<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Zilphar Matheka on Medium]]></title>
        <description><![CDATA[Stories by Zilphar Matheka on Medium]]></description>
        <link>https://medium.com/@zilpharmatheka?source=rss-b8307f1b0015------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*zVNH0uf_Zb6TUo53myw3AA.jpeg</url>
            <title>Stories by Zilphar Matheka on Medium</title>
            <link>https://medium.com/@zilpharmatheka?source=rss-b8307f1b0015------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 10:26:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@zilpharmatheka/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Understanding Stakeholder Needs Vs Requirement Gathering]]></title>
            <link>https://medium.com/@zilpharmatheka/understanding-stakeholder-needs-vs-requirement-gathering-1b0f5ef0b54f?source=rss-b8307f1b0015------2</link>
            <guid isPermaLink="false">https://medium.com/p/1b0f5ef0b54f</guid>
            <dc:creator><![CDATA[Zilphar Matheka]]></dc:creator>
            <pubDate>Sat, 12 Apr 2025 16:38:45 GMT</pubDate>
            <atom:updated>2025-09-16T16:50:44.951Z</atom:updated>
            <content:encoded><![CDATA[<p>Welcome to this blog😊.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/916/1*5HqDeGF5d0Uy0N6K7ykC3Q.png" /></figure><p>These are two somewhat confusing concepts, as sometimes they are considered to be the same thing, which they are not. Although they follow each other in the BI process or even when managing projects, they are two different things. First, you have to understand stakeholders’ needs to carry out requirement gathering.</p><p>In simple terms, <strong>understanding stakeholder needs or business needs</strong> answers the <strong>‘why’</strong> while <strong>requirement gathering</strong> answers the <strong>‘what’.</strong></p><p>Let me break it down with an example.</p><p>Let’s say you are talking to a sales manager for a real estate company, and they say they want a report on sales and customer satisfaction scores. In this case, one will tend to understand why the sales manager wants the report, the pain points, their goals, and the context behind their request. As the analyst, you ask questions like “What outcome do you hope to achieve?” Understanding stakeholder needs is more strategic and empathetic, ensuring you are solving the right problem. Just like in problem solving, where you try to identify the problem with the 5Ws, here you have to understand the needs first to ensure the final solution created fits the needs of the sales manager.</p><p>You engage with the sales manager and realize that they are trying to spot underperforming regions and low client satisfaction early on. Now you move on to answering the ‘what’, which is requirement gathering. The ‘what’ is like a technical and structured documentation of what needs to be built or delivered. In this case, one will need to ask, ‘What do they mean by underperforming?’, ‘What tools should be used?’, ‘What KPIs do you need to see in the report?’, ‘How often does it need to be updated?’</p><p>After gathering the requirements, you understand that the sales manager needs a report on sales and customer satisfaction scores using Power BI, and they want it automated and updated weekly. They also define underperforming customer scores as a satisfaction score below 70% and more than 2 unresolved complaints per week, and in sales, a conversion rate of less than 10% (these are just examples).</p><p>In this case, you decide to design a Power BI automated dashboard that updates weekly and has an alert system, for when sales and customer satisfaction scores are low. This makes it easy to track the customer satisfaction scores and sales, allowing the sales team to take action when sales are low.</p><p>**In conclusion**:</p><p><strong>You understand stakeholder needs — → Gather detailed requirements — -&gt; then design the solution.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b0f5ef0b54f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Validation as an Aspect of Data Entry]]></title>
            <link>https://medium.com/@zilpharmatheka/data-validation-as-an-aspect-of-data-entry-b82a6689e987?source=rss-b8307f1b0015------2</link>
            <guid isPermaLink="false">https://medium.com/p/b82a6689e987</guid>
            <category><![CDATA[data-validation]]></category>
            <category><![CDATA[data-entry]]></category>
            <category><![CDATA[consistency]]></category>
            <category><![CDATA[accuracy]]></category>
            <dc:creator><![CDATA[Zilphar Matheka]]></dc:creator>
            <pubDate>Tue, 29 Oct 2024 13:54:35 GMT</pubDate>
            <atom:updated>2025-08-14T20:54:08.142Z</atom:updated>
            <content:encoded><![CDATA[<p>“Get the data, we can deal with it later.”</p><p>I have heard this statement before, and this is what happens in most data collection. The process of “deal with it later” is data cleaning, which, at times when data validation was not involved in the data collection, becomes a time-consuming process.</p><p>Data validation is a process that allows control of what data is entered in the cells of an Excel worksheet or Google Sheets. It ensures that the data entered is accurate and consistent by enforcing specific criteria or rules for input values. It saves time for data entry professionals by automating validation instead of manually entering and checking each entry. This, in return, enhances data analysis and reporting by ensuring consistent data formatting and structure.</p><p>Types of data validation include:</p><ol><li>whole number validation,</li><li>decimal validation,</li><li>list validation,</li><li>date validation,</li><li>time validation,</li><li>text length validation, and</li><li>custom validation.</li></ol><p>To get to the data validation tab in Excel, go to the <strong>Data Tab</strong>, and in the <strong>Data Tools,</strong> you will find an icon like below, which, when clicked, has the <strong><em>settings, input message</em></strong>, and <strong><em>error alert</em></strong> parts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xrMEGxSxL1qxxq731b_f0Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/961/1*UF5v-CK2GW43mHrFxy_24g.png" /></figure><h4><strong><em>Data validation process</em></strong></h4><p>Have you worked with dirty data and wondered why people enter bad or incorrect data? Well, to get quality data, it is possible to let people know what is expected upfront. See, when we talk about data analysis, we say as a data analyst, you need to know what the audience of the report you are creating needs to know before creating it, and the same applies to data entry. If we use data validation and ensure there is an input message or a drop-down menu they can choose from, the data entry people will know what is expected of the data they input.</p><h4>Dropdowns in data validation</h4><p>Data validation is not always about whole numbers. It can also include supplying the information you want people to fill out through drop-downs. Dropdowns help remove the guesswork</p><blockquote>Take, for example, situations where YES and NO are the answers that could be used. For example, in an Approved column, a request can either be approved or not approved.</blockquote><p>In conclusion, data validation can be very helpful in data entry, especially in areas like online questionnaires where customers have to fill out blank parts. It is also helpful in company data collection, where an employee is to fill out who their supervisor is and their job rank for easy data collection. Generally, it makes it easier to collect data, ensuring that accurate and consistent data is the final result.</p><p><em>If you have read up to here, please leave a clap.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b82a6689e987" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Supply Chain Analysis Using Excel]]></title>
            <link>https://medium.com/@zilpharmatheka/supply-chain-analysis-using-excel-fd460ba9cf89?source=rss-b8307f1b0015------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd460ba9cf89</guid>
            <category><![CDATA[supply-chain]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Zilphar Matheka]]></dc:creator>
            <pubDate>Thu, 09 May 2024 16:03:42 GMT</pubDate>
            <atom:updated>2024-10-30T12:54:34.942Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Introduction</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/727/1*lB22ncl6Xb5E_28NWxTKWQ.png" /></figure><p>In most manufacturing organizations or even organizations/businesses that generate products, the flow of materials from raw materials to the final product and how they are distributed to reach customers are recorded. This is supply chain management. But this management is data that is recorded for future reference. A <strong><em>Data Analyst </em></strong>in the organization can use this data to uncover insights from them including the products with the most sales, which different locations performed well or worse on sales, production rates, revenue generated over a certain period, and whether it attained the given target for the period, their suppliers, and how each one of them has performed in terms of sales, etc.</p><p>In this analysis, we look at a similar dataset that has the products sold, the date, the suppliers, the shipping costs, the quantities ordered, lead times, etc. H<strong><em>ere is a link to the original dataset (before cleaning) we are using: </em></strong><a href="https://www.kaggle.com/datasets/harshsingh2209/supply-chain-analysis?rvi=1">Supply Chain Analysis (kaggle.com)</a>. <em>The dataset did not have a date column, so I had to create one.</em></p><blockquote>The dataset at hand is the supply chain dataset for a Cosmetic Organization with data recordings from January 2022 to April 10th, 2022.</blockquote><p>Below is a screenshot of the completed Report. This is the link to the completed Report which I will explain below: <a href="https://zilphar97-my.sharepoint.com/:x:/g/personal/zilphar_zilphar97_onmicrosoft_com/Ea7Hlhg_2rtFvmMiAfTSGsoBU9_067g2Awj4W_0-2zbNJA?e=psftb6&amp;nav=MTVfe0JGRDMzNDgxLTI0MkQtNDUwMC1BM0YyLUZENEZFMzU1OTdCQn0">SUPPLY CHAIN ANALYSIS (1).xlsx (sharepoint.com)</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j9r5C8hGgJJmuvQdijtZYw.png" /></figure><p>But first Data cleaning.</p><h4><strong>1. Data Cleaning</strong></h4><p>Cleaning involves taking away any impurities or dirt in something and this is the same process that is carried out when working with a dataset. Data cleaning is carried out to ensure accuracy, consistency, and data quality. Removing errors, inconsistencies, unwanted columns, and duplicates helps prevent misleading conclusions that could be generated if the data is not cleaned.</p><p>a. The first thing we do is to format the Column names to be consistent and match the data contained in them. Then change their width so the data contained in them is visible without strain (I set the column Width to 15).</p><p>b. Secondly, we change the data type of each row to match the data contained in them. This is how we set the data types: Columns with Text data type (Product Type, SKU, Customer Demographics, Shipping Carriers, Location, Supplier Name, Inspection Results, Transportation Modes, Routes). Date data type (Date). Currency data type (Price, Revenue Generated, Shipping Cost, Manufacturing Costs, Costs). Number data type (Availability, Number of Products Sold, Lead time, Order Quantities, Shipping Times, Production Volumes, Manufacturing Lead Time, Defect Rates, Month).</p><p>c. Look for duplicates and missing values (there are no duplicate values or missing values in this dataset.).</p><p>d. We set the text data in the <em>Product Type column </em>to start with a capital letter. We create a new column and use the <strong>=PROPER () </strong>function to change to the proper wording format and delete the original column.</p><p>e. Some columns in the dataset are not necessary or do not make sense so we will remove them. Remember this is dummy data for a hypothetical organization so when some columns are not required, they are removed. For real datasets that involve working with real organizations’ data, it is advised to consult first with the data source before deleting the column.</p><p>We will delete the <em>Lead Times, Manufacturing Lead Time, </em>and <em>Stock Levels </em>column. We also delete the <em>Shipping Costs </em>column because it is included in the Transportation Costs Column.</p><h4><strong>2. Data Processing</strong></h4><p>Data processing involves working with the cleaned dataset to derive insights that can drive change in the organization one is working in. In Data processing, we can use the data to get statistical values such as the average number of goods sold by each customer demographic i.e. gender, the total amount of sales made in the given period, or develop visuals like the trend of sales within the period given, etc.</p><h4><strong>a. Important Values</strong></h4><p>→ We look at the <strong>total Products Sold </strong>and the <strong>total Production Volumes</strong> within the time frame given (1/1/2022 to 1/10/2022) using the <strong>=SUM ()</strong> function. This makes sense given the Production volumes being higher than the goods sold since the organization has to have products readily available to fulfill customer orders without delays.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/530/1*nzMxqUGJyXdnwldyKjVsXA.png" /></figure><p>→ We look at the shipping time, and lead time by calculating their Average using the <strong>=AVERAGE () </strong>function. <strong>Shipping time</strong> is the time it takes when an order leaves the organization’s premises to when the order reaches the customer. The L<strong>ead Time</strong> is the time when an order is made to the time it is delivered to the customer. In our case, the lead time starts from 1 day to 30 days. This includes the <em>time taken to process the order, the manufacturing time, transportation, and delivery.</em> These Times are represented in Days. The average time makes sense with the lead time average expected to be higher than the shipping time since the shipping time is a portion of the Lead Time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/483/1*FnO7lU8Iuuy0nHMHSNimIw.png" /></figure><p>→ Production and Sales rate.<strong> Production rate</strong> is the rate at which products are manufactured in the organization and in our case, we are looking at the daily rate of production calculated using the formula <em>(</em><strong>= Total volumes produced/ total days of the period<em>)</em></strong>. <strong>Sales rate </strong>is the rate at which the produced goods are sold to customers or in this case the rate at which the goods leave the company premises for distribution to the customers. We calculate it using the formula (<strong>=Total goods Sold/total days of the period<em>).</em></strong></p><p>To calculate the total days through the period we use the <strong>=DAYS () </strong>formula to calculate the days from 1/1/2022 to 4/10/2022 (the end date of our data recording).</p><p><strong>When the production rate is higher than the Sales rate what does that mean?</strong> Production rate being high means that there is accumulated inventory which occurs when the products are produced at a faster rate than they are sold, leading to surplus stock sitting in the storage facilities. In our case, we assume the organization was building up the inventory levels for the given time period intentionally in preparation for anticipated price increases after April. While maintaining a higher production rate than the sales rate temporarily, this approach ensures continuity of product supply.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*GwWarz2oBJf-XrmtvatQcw.png" /></figure><p>→ Let us settle on certain columns in the dataset that might seem confusing at the moment: the <strong>Availability</strong>, <strong>Number of Products Sold</strong>, <strong>Order Quantities, </strong>and <strong>Shipping Times </strong>columns.</p><p><strong>Availability </strong>refers to the number of products available at the time when a customer makes an order. For example, at times a customer may make an order of <strong><em>68</em> </strong>when the available products are <strong><em>22</em></strong>, this means that the product will have to be made first for it to be shipped to the customer.</p><p><strong>Number of Products sold </strong>refers to the actual quantity of products that have been sold to customers or in this case the units that have been delivered to customers within the given period. This is a measure of the sales volume and represents the demand for the Cosmetic organization products in the market.</p><p><strong>Order Quantities</strong> refers to the quantity of products that have been ordered by customers but have not been sold or delivered yet. This represents the demand for the organization’s products by customers.</p><p><strong>Shipping Times</strong> is the Shipment frequency, or the count of times individual shipments were made to a customer.</p><p>→ Revenues, Costs, and Profits. A <strong>Gross Profit Margin (GPM) of 90.02%</strong>! A GPM of 90% is very high but what does this mean to the Cosmetic Organization? A high-profit margin suggests <strong>strong profitability.</strong> It indicates that 90% of the revenue generated by the business is retained after deducting the direct costs that are associated with producing and delivering the products. In simpler words, for every dollar of revenue generated, the business keeps 90 cents as gross profit while 10 cents is spent on the cost of products sold. This means that the cost of producing the products is very low compared to the selling price of the products.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/722/1*YadzgRq0SnLenNihWLhxEA.png" /></figure><h4><strong>b. Analysis</strong></h4><p><strong>→</strong> The Organization <strong>Executives </strong>want to know <strong><em>which product had the highest demand </em></strong>to know how they will allocate resources to match the demand for the coming high season.</p><p>To calculate the total F<strong>orecasted demand</strong> for the three products (Skincare, Haircare, and Cosmetics), we use the <strong>=SUMIF ()</strong> function to sum order quantities which indicates the demand for each product. We do the same for the <strong>Current or Actual demand</strong> but with the Number of Products sold.</p><p>This is better represented as a Percentage by using the total demand for each product dividing by the total demand and multiplying by 100. This results in Skincare products having a forecasted demand of 42% and an Actual demand of 45% for the given period.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/535/1*9LiUWSg1CSIMhPwRTZDwXw.png" /></figure><p><strong>→</strong> After knowing the demand, they want to know <strong><em>the Lead Time and whether that might affect the Forecast demand for the products.</em></strong></p><p>We calculate this using a Pivot table. But first, we create bins for the lead time (1–10, 11–20, 21–30) which we then reference using a <strong>=IFERROR (VLOOKUP ())</strong> function to insert the bins to the main table according to the value contained in the lead Time column. The <strong>=IFERROR () </strong>displays a custom message <strong>“No match” </strong>when a match is not found (all values have matches here so no errors).</p><p>This reveals that <strong>Most Demand occurs when the lead time is high (21–30). </strong>What does this mean for the company? This means that lead times might affect the demand for products as a result of customer preferences where customers tend to order more on products with larger lead times to avoid stockouts. i.e. These products take much time to manufacture, and a customer would rather buy in bulk than run out of stock and have to wait for 21 days plus (which might lead to low sales for them) to receive their next stock.</p><p><strong>→</strong>The Executives also want to know <strong>how products performed in terms of Revenue and whether the allocated Revenue KPIs were met.</strong></p><p>In this case, the Company had set the KPIs for revenue each month to increase by <strong>5% </strong>for each subsequent month with <strong><em>January being the base Month </em></strong>and the <strong><em>Projected Amount for March being the Amount where the company is supposed to be at the end of the first quarter.</em></strong> (This is like an analysis of the first quarter of the year and how the Organization performed according to the set KPIs).</p><p>We Calculate the Performance percentage to get the % at which the KPIs set were attained using the formula<strong> = ((Projected Amount — Actual Amount)/Projected Amount) * 100. </strong>The set KPIs were met by a very small percentage which calls for further analysis to determine what can be done to improve the situation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/527/1*liZDc_uO3vfvirj6qxADlQ.png" /></figure><p><strong>→</strong>Let us look at the <strong>Projected revenue for the Month of April </strong>and <strong>project how sales should be done to reach the expected target.</strong> (We get a daily sales target of approximately<strong> 602 products.). </strong>Let us break it down:</p><p>The recordings for the month of April are for the first 10 days (4/10/2022). We want to project the number of products to be sold each day and the daily revenue to be generated each day. We calculate the remaining number of days using the =DAYS () formula from 4/10/2022 to 4/30/2022 <strong>(20 Days.</strong> Remember April has 30 days). We also calculate the remaining amount that needs to be achieved for the 20 days using the <strong>= Projected Revenue for April — Current Revenue for April (</strong>$150,748.05<strong>). </strong>(The current revenue is the revenue that has been generated in the first 10 days of April). With the remaining amount and number of remaining days, we can calculate the Daily revenue required to be generated for those 20 days to reach the revenue target ($7,537.40). To get the daily sale target we use the <strong>=Daily Revenue/Revenue per product sold. </strong>Revenue per product sold is calculated by dividing the <strong>Total Revenues over the period/Total products sold</strong> ($12.53). With this, we calculate the Daily Sale Target of 602 Products. This is possible considering the highest number of products sold once to a customer is 996 products.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/559/1*rm-lIp5tUFO6xuJ3gReMSA.png" /></figure><h4><strong><em>→Additional Analysis</em></strong></h4><p>→ <strong>Transport modes</strong>. Transport Modes are means used to transport orders to Customers. Of the four transport modes (Road, Rail, Sea, and Air), Rail and Road have the highest deliveries of products sold. <strong>Rail</strong> has <strong>29.21% </strong>of total products sold delivered through it and <strong>Road </strong>has <strong>29.20% </strong>of total products transported through it. This means that most of the customers for the Cosmetic organization are in accessible locations.</p><p>→ <strong>Location sales. </strong>Most products were sold in <strong>Kolkata (28%) </strong>with <strong>Bangalore</strong> having the least sales at <strong>12%</strong> of the total products sold in the first quarter.</p><blockquote>It appears that there may be a misalignment between the planning team’s strategies and the goals of business progression. While the organization maintains a healthy profit margin generally, there seems to be a need for greater focus on strategic planning that prioritizes sustainable growth. This could involve reassessing priorities to ensure that profit-making initiatives are balanced with initiatives that drive long-term success.</blockquote><h4><strong>3. Recommendations</strong></h4><p>a. The organization is going into a high sales season after April meaning that the product forecast demand of 42% needs to be maintained and to do this, accumulated inventories are needed to ensure ready products when customers make orders.</p><p>b. Keeping a good, accumulated inventory also means making sure that the defect rates are reduced. The average defect rate of products in the organization is <strong>2.28 ~ 3. </strong>This means that for every unit of products sold approximately 3 of them are defective. This calls for the employees to identify the root cause of defects and implement corrective actions to improve production.</p><p><em>If you have read up to hereplease leave a clap.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd460ba9cf89" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Product Sales Analysis]]></title>
            <link>https://medium.com/@zilpharmatheka/product-sales-analysis-8c5f219f1da6?source=rss-b8307f1b0015------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c5f219f1da6</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[power-query]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Zilphar Matheka]]></dc:creator>
            <pubDate>Sun, 28 Jan 2024 21:06:42 GMT</pubDate>
            <atom:updated>2024-04-11T02:14:50.266Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>PRODUCT SALES ANALYSIS USING A FINANCIAL SAMPLE DATA</strong></p><p><strong>Introduction</strong></p><p>In business, the analysis of product sales data plays a strong role in understanding product dynamics, identifying trends, and making informed data-driven decisions. The dataset we will be using here has a comprehensive record of key variables related to product sales, providing a valuable resource for uncovering insights from it.</p><p>It comprises essential columns such as Segment, Country, Product, Discount Band, Units Sold, Manufacturing Price, Sale Price, Gross Sales, Discounts, Sales, Cost of Goods Sold (COGS), Profit, Date, Month Number, Month Name, and Year. Each variable holds intrinsic significance in determining the intricacies of product sales, and profitability. This analysis aims to extract meaningful insights and unveil patterns, and trends in product sales.</p><p>The audience of the report we are creating here is the <strong>Sales Team</strong> who want to know how products have been performing.</p><p>The tool used for analysis is <strong>Microsoft Power BI.</strong></p><ol><li><strong>Overview of the Dataset</strong></li></ol><p><strong><em>Introduction to the Dataset</em></strong></p><blockquote>The dataset used in this analysis is from the years 2013 and 2014. The data available for the year 2013 is from September through December and the whole of 2014 from January through December. An analysis of this dataset using Just the months alone for the two years would give an incorrect conclusion which could lead to poor decisions. The data was collected each 1st of the month for the 16 months the data is listed.</blockquote><p>The data we will be working on is a sample dataset from Microsoft, <strong>The Financial Sample Excel workbook</strong>. Here is the link to the dataset: <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/sample-financial-download"><strong>Download the Financial Sample Excel workbook for Power BI — Power BI | Microsoft Learn</strong></a></p><p>I loaded the dataset as an Excel file to the Power BI desktop using the Get data option and transformed it using power query.</p><p>Below is the overview of the dataset. It has 700 rows and 16 columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*agNUFhHqmM1cQgsb_elXNg.png" /></figure><p><strong>Data Cleaning and Processing</strong></p><p>To confirm if the data has missing values, I used the Data Preview feature (Column distribution, column quality, and column profile) in Power BI. This feature shows if a column has duplicate values and allows one to remove them if required if a column has missing values or error values, and the row count for each column and the number of empty cells in each column, etc. (It should show 700 rows and empty as 0 if there are no blank values in the column).</p><p>The dataset as provided was clean and ready for analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CPq2q9dTAX0XH2Kv8RfVAw.png" /></figure><p><strong>Project Analysis</strong></p><p>Let&#39;s break down the Units sold, manufacturing price, sales price, gross sales, discounts, sales, COGS, and profit.</p><p>The Units sold is the Number of units of a product sold on a certain date.</p><p>The manufacturing price is the production cost per unit of a certain product.</p><p>The sales price is the amount a product is sold in the market.</p><p>The gross sales are the total sales of the products. Which is calculated using <strong><em>= Units sold * Sales price</em></strong></p><p>Discounts are market deductions done on a product from its usual sale price.</p><p>Sales are calculated using <strong><em>= Gross sales — Discounts</em></strong></p><p>COGS (cost of goods sold) is the cost of producing the goods sold.</p><p>profit is =<strong><em> sales — COGS. </em></strong>A negative of this column would mean a loss and a positive means a profit.</p><ol><li><strong>Calculations</strong></li></ol><p>The total sum of units sold in the company is <strong>1.13 M </strong>units of goods.</p><p>The total sum of sales in the company is <strong>118.73 M</strong> sales.</p><p>The total cost of goods sold is <strong>101.83 M.</strong></p><p>The total profit is <strong>16.89M.</strong></p><p><strong>2. Report</strong></p><p>Below is the final report of the analysis which can be accessed through the link: <a href="https://app.powerbi.com/groups/me/dashboards/9eab9822-549b-4a4e-ab0e-9a17c43c3a10?ctid=ecea252b-90c9-4a3d-ae1b-2ed75c14a8f1&amp;pbi_source=linkShare">https://app.powerbi.com/groups/me/dashboards/9eab9822-549b-4a4e-ab0e-9a17c43c3a10?ctid=ecea252b-90c9-4a3d-ae1b-2ed75c14a8f1&amp;pbi_source=linkShare</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/857/1*GKt1pXEQyzhosUFDrRDUhg.png" /></figure><p><strong><em>3. </em>Business questions that can be solved using this analysis:</strong></p><p>a. <strong><em>Sales Performance analysis</em></strong></p><p>→Which countries have the highest sales performance and which products are highly sold there?</p><p>The United States has the highest sales of 25.03 M sales compared to the other countries with the Paseo product having the highest number of sales in the country and through the other four countries.</p><p>This could be a result of the market in the United States being more established than in other countries. Effective marketing strategies strongly affect sales and if products are marketed well and have a strong brand presence in the country, then it is more likely to be successful.</p><p>The product sales vary with each country with the Paseo product leading sales in each country. The Paseo product takes up to 30% of the total sales of all products which totals 338K units sold of the product. Other products like the VTT follow with each product taking a percentage of Units sold between 135 and 15%. The VTT product Units sold are less than half the total sold for the Paseo product.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/862/1*D3QyaN_8Rp4BruMjRC6efw.png" /></figure><p>→Which products contribute most to gross sales?</p><p>The paseo product contributes most to Gross sales leading with a Gross sale of 35.6M Gross sales. This can be placed into good marketing strategies for the product which leads to a good customer flow of the product.</p><p>The Amarilla product although has the fourth-highest Gross sales, has the third-highest profit of 2.8M of the six products. Carretera products have the lowest Gross sales and profit.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*Z8edGXkGyrtyA4L5pK61UQ.png" /></figure><p>→What are the product sales over time?</p><p><em>Using Units Sold</em></p><p>Looking at the below line graph, there is a relationship between the units sold and the profit. The two seem to move together at least for the most part: as the number of Units sold increases the profits increase. In 2013, the data available is from September through December and there has been a rise in Units sold and profit from September to October. This is a similar trend in 204 although for 2013 sales in December did not increase. As the Units sold drop after that the profits also drop.</p><p>This visual can be used to give information on the trends of sales over time but concluding why sales rise and fall can lead to incorrect decisions. The period from September to December encompasses major holidays such as Halloween, Thanksgiving, Black Friday, Christmas, and New Year’s Eve. Consumers tend to increase their spending during this festive season, buying gifts, decorations, and other holiday-related items. However, at the same time, sales decrease around November. More information is required to make these conclusions which could be based on seasonal trends such as the holiday season, and economic factors such as spending habits and fluctuations in disposable income.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*Kb20x_kPsSluPTgFCUZ2qA.png" /></figure><p>b. <strong><em>Discount Impact</em></strong></p><p>→ How does the application of discounts affect sales and profit?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*Ymytsyx1k7CX9cGkyUIFuw.png" /></figure><p>A radar chart represents the visual of the relationship between sales and profit in terms of their averages.</p><p>In the dataset, Discount is divided into four categories: None, Low, Medium, and High. Commonly, high discounts can lead to increased sales volume. Customers are often attracted to promotions and discounts and a higher discount may encourage them to make purchases. However, high discounts can potentially erode or reduce profit (the case in our dataset) as the reduction in per-unit revenue may not be offset by cost reduction.</p><p>In the absence of any discounts, both sales and profits are relatively lower. This aligns with general customer behavior, where discounts and promotions stimulate demand. In the absence of a discount, customers may perceive less value in making a purchase especially if they are not motivated by immediate needs or they can get a better deal somewhere else. As a result, when the discounts are not present the sales and profits are lower.</p><p>The key takeaway is that a moderate discount of low or medium might be more effective than no discount in terms of both sales and profits.</p><p><strong>Conclusion</strong></p><p>This analysis has helped me uncover insights from the dataset, improving my problem-solving, and critical thinking skills.</p><p>From understanding what is required of the data, transforming it, analysis to providing insights, I have been able to apply my critical thinking skills to analyze the data using Microsoft Power BI.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c5f219f1da6" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>