Data Analysis

Date - all - Sum - Sales Facility Drugs Chilenje Kasangula Zambezi Total Result Ascorbic $116,950 $170,866 ### $478,662 Ibut-200 $75,448 $95,249 $32,058 $202,755 Vitamin $33,415 $116,558 $63,882 $213,855 Total Result $225,813 $382,673 ### $895,272

Objective #9: Excel Functions to Make Calculations Based on an AN Objective #10: SUMIFS for Summary Report Based on Adding, Old S Goals: 1 We want a Cross Tabulated Report showing Total Sales by Drugs and Facility. 2 We want a Cross Tabulated Report showing Average Sales by Drugs and Facility. 3 We want a Cross Tabulated Report showing the Count of Transactions by Drugs and Facility. 4 We are entering data each day and we want reporting solution to update instantly when we add ne Date Facility Drugs Sales Cross Tabulated Report for Total S 2/11/2019 Chilenje Ibut-200 $38,016 Drugs/ facility Chilenje 2/11/2019 Kasangula Vitamin $71,182 Ascorbic 116,950 2/11/2019 Zambezi Ascorbic $70,720 Ibut-200 75,448 2/11/2019 Chilenje Ascorbic $69,598 Vitamin 33,415 2/11/2019 Kasangula Ibut-200 $55,299 2/11/2019 Zambezi Vitamin $63,882 =SUMIFS($E$11:$E$27,$D$11:$D$ 2/12/2019 Chilenje Ascorbic $47,352 2/12/2019 Kasangula Ascorbic $53,105 Cross Tabulated Report for Averag 2/12/2019 Zambezi Ibut-200 $32,058 Drugs/ facility Chilenje 2/12/2019 Chilenje Vitamin $33,415 Ascorbic 2/12/2019 Kasangula Ascorbic $71,880 Ibut-200 2/13/2019 Zambezi Ascorbic $63,092 Vitamin 2/13/2019 Chilenje Ibut-200 $37,432 2/13/2019 Kasangula Vitamin $45,376 #N/A 2/13/2019 Zambezi Ascorbic $57,034 Cross Tabulated Report for Count 2/13/2019 Kasangula Ascorbic $45,881 Drugs/ facility Chilenje 2/13/2019 Kasangula Ibut-200 $39,950 Ascorbic Ibut-200 Vitamin 1/14/2019 Kasangula Ascorbic Aci 31980 1/14/2019 Zambezi Ascorbic Aci 45789 1/14/2019 Chilenje Ibut-200 X10 55299 1/14/2019 Kasangula Vitamin C 10 63882 1/14/2019 Zambezi Ascorbic Aci 47352 1/14/2019 Kasangula Ascorbic Aci 29870 1/14/2019 Kasangula Ibut-200 X10 31852

Report Based on Counting, Old School Cell References and Dynamic Ar y Report Based on Averaging, Old School Cell References and Dynami #NAME?

rrays. ic Arrays.

15000 20000 20000 15000 Revenue > 15000 and <20000 then "Good", Revenue >=20000 is "Exceptional", rest is value Revenue >= 20000 or <=15000 then "Flag"

MATCH function is a lookup function that returns the relative position of an Match w 0 (Exact Match) Only 1 in list Dups Not in List Lookup Jo Tom Sue Jo Relative Position 2 3 #N/A 2 List 1 List 1 Joe Joe Jo Tom Sioux Sioux Sioux Tom MATCH with 1 or Empty & Sorted Ascending (Approximate Match - 1st Biggest & Jump Ba Fits in Gap Finds Exact Value Smaller Than First Bigger than Last Lookup $150.00 $100.00 ($400.00) $20,000.00 Relative Position 2 2 #N/A 7 List 1 List 1 $0.00 $0.00 $100.00 100 <= x < 500 $500.00 lookup_value is the value you tell the match function to lookup lookup_array is the list that you look an item up in [match_type] tells the MATCH what sort of lookup to do: 1 or empty = aproximate match; table sorted ascending; first bigger value bumped into then jump back one returns #N/A, if bigger than last it returns last value 0 = extact match, if duplicates, it finds first one only, can't find it it show -1 = aproximate match; table sorted descending; first smaller value bumped into then jump back one position, if smaller than last it returns last value MATCH can do vertical or horizontal =MATCH(B12,$E$15:$ K$15,0)

item in a list Jo Tom Sioux Sioux Sioux Tom ack) $50.00 $100.00 $500.00 $1,000.00 $2,500.00 $5,000.00 $10,000.00 e position, if value is smaller than first item ws #N/A if value is bigger than first item returns #N/A, MATCH can do vertical or horizontal

$9,000.00 $5,000.00 $2,500.00 $1,000.00 $500.00 $100.00 $0.00 MATCH can do vertical or horizontal

Description Location Code Unit of MeQuantity Invoiced QCost Per Unit Ibut-200 X1000 LUSAKA PCS -226,000 -226,000 0.11 Vitamin C 1000mg + Zinc 10mg X20 LUSAKA PCS -147,200 -147,200 3.47 Foliben (Folic Acid) 5mg Tablets X200 LUSAKA PCS -134,280 -134,280 3.47 Regamol 500mg(Paracetamol 500mg) X1LUSAKA PCS -76,000 -76,000 0.05 Ascorb Vitamin C 100mg (Essential HealtLUSAKA PCS -71,000 -71,000 0.10 Ascorbic Acid 500mg (Crescent) X28 LUSAKA PCS -70,000 -70,000 0.18 Apectine 4mg X1000 LUSAKA PCS -58,320 -58,320 2.20 Zyncet 10mg X200 LUSAKA PCS -54,820 -54,820 2.20 Kotrim X100 LUSAKA PCS -53,508 -53,508 3.46 Nifedipine Retard-20 X100 LUSAKA PCS -53,424 -53,424 3.46 HOP-C Tablets (Chlorpheniramine MaleatLUSAKA PCS -44,000 -44,000 0.08 Ibuprofen 200mg X1000 OWN LOG. PCS -42,784 -42,784 3.46 Coldcare Flu & Cold Tablets X4 LUSAKA PCS -42,784 -42,784 3.46 Dynapar Tablets X100 LUSAKA PCS -42,600 -42,600 0.18

Ascorb Vitamin C 100mg (Essential Healthcare) X20 Description Location Code Unit of Measure Code Quantity Invoiced Quantity Cost Per Unit

Sales Performance: What is the total sales amount for the given time period? Which product has the highest sales amount? Can you identify the top 5 customers based on sales? Inventory Management: What is the remaining quantity for each product in the warehouse? Which products are running low on stock? Cost Analysis: What is the total cost amount for the products sold? What is the profit margin for each product? Customer Analysis: How many unique customers made purchases during this period? What is the average quantity of products purchased per customer? Vendor Analysis: Who are the top 3 vendors in terms of sales amount? What is the average cost per unit for products from each vendor? Quantity Analysis: What is the overall quantity sold for each unit of measure (e.g., PACK)? Can you identify any patterns or trends in the quantity sold? Product Specific: For the product "POWDER DAY BY DAY 250 GRS - ROSE X1," what is the sales amount and remaining quantity? Time Analysis: How does sales performance vary over different dates or months? Are there any seasonal trends in sales? Invoice Analysis: How many unique invoices are there, and what is the average sales amount per invoice? Unit Price Analysis: What is the average cost per unit for all products? Are there any products with unusually high or low cost per unit?