need help with the case

Unless you need to edit it's safer to stay in Protected View. Enable Editing B 1 Retail Business F 2 H M N 3 ABC Women's Retail Store is a local Edmonton based business run by two sisters. 4. They are located in a small strip mall on the southside of Edmonton. 5 They have 4 staff - 3 Sales Staff & 1 Admin/Accounting/Marketing Staff. 6 This document includes their financial information for 2018, 2019, 2020. (Daily Sales Log, Customer Database, and Expense Log) 7 They would like you to analyze their financials for the last 3 years and budget this year 2021. 8 On a separate Excel Tab (of in an MS Word Document) Answer ALL Questions on the Case QA & Case QB tabs. (You must include this spreadsheet with ALL your calculations to get marathi 9_Support Videos are located on the Support Videos Tab. 12 Product List 13 Women's Shir 14 Women's Pants 15 Women's Dresses 16 Men's Shirts 17 Men's Pants Sales Price Cost $30.00 S 20.00 S 50.00 15.00 S75.00 $ 20.00 S 25.00 $ 12.00 $ 40.00 S 10.00 18 19 20 21 22 Expenses 23 Monthly Store Rent ######## per month 24 4 Staff Salariess / mont ######## (40 hours a week X $15/hour X 4 staff) 25 26 27 28 Business Overview Case QA Questions Support Videos Case Q5 Inc ... Formulas Data VIEW - Excel Review View Developer Esther O Bakare Help Power Pivot EO Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing Share Commen A 1 G H M BCD Q B Case Questions: 8 Either in a separate excel tab (or MS Word Document) create a report format layout answering the folloving questions. (Must provide the Excel Spreadsheet with ALL Calculations shown). Embedde IGNORE INVENTORY ISSUES - EVERYTHING BOUGHT IS SOLD IN THE SAME MONTH. 2 3 N P S T U V W 4 Points 5 6 132 10 7 8 6 9 10 Calculations Required: Show All your work in Excel - Answers provided with no Excel Calculations will get a Zero) 1. Complete the Case QB Income Statements Tab 2. Identify the variable, fixed costs, direct costs, and indirect costs. Using a Vlookups, filters and pivot tables calculate the following - show your rort: 3. a) Who is the customer who purchased the most in 2018? 2019? 2020? b) Who is the customer who purchased the least in 2018 2019 2020? 4. What products does the store sell? What is the price of each? What is the cost of each? What is the gross margin and gross margin ratio of each? 5. Which product line contributed the most to the profit in 2018 2019 20202 6. a) Which month was the stores biggest sales month? b) Which month was the stores lowest sales month? 7. Which year was the stores biggest sales year? Use an Excel Graph to show the yearly comparison 8. Rank the product lines from the best seller to the least? 20 6 11 4 12 13 7 5 14 15 16 17 15 18 9 Analysis Section: 9. After reviewing the data calculated above, identify and explain 5 actions would you suggest to increase profits? 10. What are 3 trends impacting the retail industry? And how could this store deal with them? 11. a) As the store manager, how would you suggest to pay your sales staff? b) How would you suggest to pay your office/admin staff? c) and explain why? BONUS: 12. If you were required to allocate the expenses across the various product lines (in Case QBinoome Statements) how would you do this? On what basis? 6 5 225 19 20 21 22 23 24 25 26 Your assignment MUST have at least: a. TVO PIVOT TABLES b. One EXCEL GRAPH c. One Filtered List d. TVO VLOOKUPS (Hint for one of them: Customers Name & Customer Number) 27 28 29 30 131 33 34 35 36 37 Business Overview Case QA Questions Support Videos Case QB Inc D 6496 X R T V W 2 HA oring questions. (Hest provide the Excel Spreadsheet with ALL Calculations shor). Embedded MS Excel Tables, Graphs and Pirot Tables are acouraged. AB AC AD HE AF AG AH AT AU Ell get a Zero) 8 2019? 2020? Tgin ratio of each? staff? )and explain why? ould you do this? On what basis? fons Support Videos Case OB Inc .. 1 12:10 PM E For the Year Ending 2018 H 1 3 Total 5 Vomen's Shirt Vomen's Pantstomen's Dresse Men's Shirts Men's Pants 6 Sales COGS 8 Gross Margin 9 Gross Margin Ratio 10 Expenses 11 12 2. 3. 15 5. 18 17 Total Expenses 18 19 Net Profit (Loss): 20 Profit Margin Total ABC Women's Retail Store 22 Income Statement For the Year Ending 2019 26 27 Vomen's Shirt Women's Pantsomen's Dresse Men's Shirts Men's Pants 28 Sales 29 COGS 30 Gross Margin Gross Margin Ratio 32. Expenses 16 $ 22 85 36 $15. 38 6 32 Total Ezpenses Net Profit (Loss): 42 Profit Margin se Di.. Case QB Income Statements Case QA Questions Support Videos B -5 16 ABC Yone's Retail Store hecond State cat For the Year Ending 2020 27 Total 13 Yout's Shirt Yone's Pants Pont's Dresses Men's Shirts Men's Pants 50 Sales 51 COGS 52 Gross Margis 53 Gross Hargis Ratio 54 Eupeasts 55 1. 2. 56 57 58 60 6 61 Total Expenses 62 63 Het Profit (Loss): 64. Profit Margie 65 66 68 69 ABC Yone's Retail Store BUDGETED (FORECASTED) lacone Stateneat for the Year Ending 2021 12 Vosen's Shirte one's Pants one's Dresses Men's Shirts Men's Pants Sales COGS Gross Margin Gross Margin Ratio Expese 1. 15 3 80 5 6 83 Total Expenses Het Profit (Loss): 8 Profit margin 31 Case OA Questions Support Videos case QB Income Statements Quantity - 3 5 1 1 2. 1 3 5 1 1 Date T Customer ERS 1-Dec-20 CN3826 2-Dec-20 KP9372 3-Dec-20 0W3927 4-Dec-20 JC3927 5-Dec-20 JC4716 6-Dec-20 043658 7-Dec-20 BH0483 8-Dec-20 CB4385 9-Dec-20 RK6372 5376 10-Dec-20 SR9486 11-Dec-20 AR3027 12-Dec-20 HK2563 13-Dec-20 JK5103 14-Dec-20 AW1948 15-Dec-20 CW3492 16-Dec-20 DW6291 17-Dec-20 DR8737 18-Dec-20 HA9273 19-Dec-20 MR8271 20-Dec-20 KK 3647 21-Dec-20 BR9993 22-Dec-20 CM6225 5441 23-Dec-20 SL 2837 24-Dec-20 LC8372 25-Dee-20 TZ6464 26-Dec-20 WF9785 5461 27-Dec-20 FM6382 5466 28-Dec-20 JE3521 5471 29-Dec-20 A7326 5476 30-Dec-20 LK4242 5481 31-Dec-20 MJ7654 - Product Men's Pants Men's Pants Men's Pants Men s Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants Men's Pants 1 3 1 2 1 3 5 6 1 1 3 3 4 5482 5483 5484 5485 Daily Sales Log Customer Database Expenses Log 3 5 2 12 CNDCTETIN JD3425 Jan. Das JS5553 Jahr. Smith CK210 Carolking Jonny Black AS7658 Amit Singh JM3394 Jonny Moh AS4634 Anna Suidors 3 DR1899 Darlene Roi 11 MN5102 Mohammad Maso 11 OH3419 Omar Harsemi CH1029 Candace Henderson 119 JK4293 Joolkluson . TH8564 TrollHondorran 15 TJ2212 Tammy Jackson 16 SM0397 Suo McGay 17 DH2342 Dave Hinton 1 RK7105 Ronald Knuts 13 CHBO26 Carl Norr 20 KP972 Karoy Panar 21 QW2927 Quinton Wright JOB927 Jerrica Cameron 2.1 JC-4716 Jerrica Coolidge 2 0A3658 Omar Aksam BH0483 Bob Hops 23 CB4385 Carol Burnetto FEBBR2 Ralph Elion 2. SR9ASE Susanna Rompel 20 ARB027 Albort Rompel HK2563 Hilda Klarron JK5103 Jake Klarson E AW1948 Aynor Wall CW3492 Colleen Wall DW62991 Davo Woutherby 15 DR8737 DowRoom HA9373 Harald Alfred MR8271 Marilyn Barra KKB647 Kurt Katal BR9993 Bryan Ray CM6225 Chelos Morro Soon Lee LOT2 Leroy Chong LED Tanya zenare WF9785 Willou Frank ES FM0.382 Freddie Mercury 16 JE3521 John Elton 147326 Trone Almond Lina Kudre M7658 Michael Jackran Mohammad AH Cara Le 12 Daily Sales Log Customer Database Expen 1 S 23 35 RE t-Jan 18 ABC Property Managemse Stors Rental Manthy 1-Fok-18 ARG Property Management Store Fontal Months 1-Mar-18 ABC Property Managemer Store Rental Monthly 1-Apr-18 ABG Property Managemer Stars Rental Monthly 1-May-18 ABC Property Managemer Stars Bontal Monthly 1- Jun 18 ABC Property Managemer Stor Rental Monthly 1-Jul-18 ABC Property Manymor Star Rental Monthly ***** 1-Aug-18 ABC Property Managemer Store Rental Monthly 1-Sep-18 ARG Property Managemr Store Rental Monthly 1-01-18 ABC Property Managemer Stor Pontal Monthly 1-Nov-18 ABC Property Managemer Stars PortalMonthly 1-Doe-18 ABC Property Managemer Stars Rontal Monthly 1-Jan-19 ABC Property Manaqomer Store Rental Monthly 1-Feb-19 ABC Property Managomer Store Rental Monthly 7-Mar-19 ABC Property Mangomer Stars Bontal Monthly 1-Apr-19 ABC Property Managemer Store Rental Monthly 1-Mar-19 ABC Property Managemer Stors Rental Monthly - Jun 19 ABC Property Managemer Store Pontal Monthly 1-Jul 19 ABG Property Managemer Stor Rental Monthly t-Aug 19 ABG Property Managemor Stors Rontal Monthly 1-Sep-19 ABC Property Managemer Store Pontal Monthly 1-Oct-19 ABG Property Managemor Stora Rontal Monthly 1-Nov-19 ABC Property Managemer Store Rental Monthly 1-Dec-19 ABC Proporty Managemar Store Rental Monthly 1-Jan-20 ABC Property Managemer Store Rental Monthly 1-Fok-20 ABC Property Managemer Store Bontal Monthly 1-Mar-20 ABO Property Manamer Store Rental Monthly tapr? ABC Property Managemer Store Rental Monthly 1-May-20 ABC Property Managemer Stor Rental Monthly hlon 20 ABG Property Managemor Store Rental Monthly 1-Jul-20 ABC Property Managmar Stor Pontal Manthly 4-Aug-20 ABC Property Manaqomor Storo Rontal Manthly t-Sop-20 ABC Property Managemer Store Rental Monthly 1-Oct-20 ABC Property Managemor Stors Rental Monthly 1-Nov-20 ABC Property Manaqomer Store Rental Monthly 1-Dec-20 ABC Property Managemer Store Rental Monthly wakaripur Staff Staff Salaris(4) Manthh DER Various Staff Staff Solaris Monthly Mordvarious Staff Staff Salarior Monthly 1-Apr-18 Variou Staff Staff Salarior (a) Monthly 1-May-18 ribu Staff Staff Salaris (A Monthly 1. Jun 1% Various Staff Stafi Salarior () Monthly Jul 1% Variour Staff Staff Solaris () Monthly 1-Aug-1% Variour Staff Staff salaris (4) Monthly 1-50p-1% Variour Staff Staffsalariwe Manthin f 1% Varipur Staff Staff salarier Month Hay-1% Variour Staff Staff Solaris (41 Mann wariaur Staff Staff salariwan Mante 1 19 Various Staff Staff salario a Monte 1 Feb 19 Variou Staff Szaff salario (di Mant 1-Mar-19 Variaw Staff Staffsalarier Monday 1-Apr 14 Variou Shaft affalaris Month 1-May 19 Variaw Staff Staff Solaris de Month Customer Database 12 11 Daily Sales log Expenses SN **** 55 SU ER Staff Salariva Monthly Staff Salaris Monthly Staff Salaris Monthly Staff Salarind Monthly Staff Salaris Monthly Staff salaris Mantha Staff Salariorld Monthly Staff Salaris Monthly Staff Salaris Mon., Staff Solaris Monthly Staff Salarios Monthly Staff Salaring Monthly Staff Salaris Monthly Staff Salaris Monthly Staff Salarint Monthly Staff Salarin Monthly Statisalariat Monthly Staff Salaris 14 Monthly Staff salaria 14 Monthly Staff Salarin|4) Monthly Utilities 3 SE FI Utilitis Utilitin t-Marin Karibur Staff tlun N taur Start Jul-19 Varinu Staff IN Uribur Staff DESNON Varipur Staff 1-04-19 Variaw Staff Now Yariour Staff -Doc-19 Variour Staff Jan zu Lariow Stalt 1-Fub-20 Variour Sean E-Mar Marinur Staff -Apr-20 Variour Staff -May-20 Variou Staff 1-Jun 20 Yariow Staff 1-Jul-20 Variour Staff 1-Aug-20 Variow Staff -Sep-2 Wariour Staff 1-04-20 Liow Staff 1-Nov-20 Variour Staff -D Variou Staff Jan 18 EPCOR 1-Fob EPCOR Nard EPOOR terpret EPCOR May. EF COR Jun-OPCOR 18 EPGOR Drug-18 EPSOR 1Sep18 EPCOR 1-03-13 EPCOR 1-Nov EPCOR 1-Dec 18 EPCOR Plan EPCOR FebEPCOR MS EROOR Apr 19 EPGOR May EPCOR -dur-19 EPCOR 1- Jul 9 EPSOR -Aug-19 EPGOR 4-Sop:19 EPCOR 1-02-19 EPCOR Hau-19 EPCOR Dog EPCOR Jan EPCOR -Feb-20 EPCOR 1-Mar-2o EPCOR Lapr.26 EPCOF: 1-May-20 EPCOF 1- Jyr-26 EPCOF. 1-Jul-2 EPCOR 1-Aug-20 EPCOR 1-Sep-20 EPCOR -Oct-26 EPCOR Utilitie Elitis Utilitis Utilities filitia VEilities litor Lilities Utilitis Utilities VEilities Lilities Utilities Peilitier Utilities Utilities Utilitie Utilities Utilities Utilitier 11 12 12 Daily Sales Log Customer Database Expense 21 ### # 1-- VEFLOR Utilitis NorDEFIDA -Due-20 EFCOR Ban is Telur Turhan. Bill Nuur Than Bill INT-NWur Tiphone Bill -Apr-Wur I kohane Bill E-May-1 Eur Tuhan. Bill 1- To chan. BHN Jul-18 Talu Tel rhon. Bill Avg. Telur Il phone Bill Sopstur Telephone Bill estur Tlophane Bill Nar-18 Talur Telephane Bill Das is Tolur Tolophone Bill lu Tolophon, Bill IN Tour Telephone Bill Narlour Telephone Bill Apr 19 Tale Lolophone Bill May 19 Tolu Telephone Bill unsalu Tokophone Bill Tarhan Bill Raya 19 Talu Telephone Bill Sep 19 Tolu Telephone Bill Telephone Bill Telephone Bill 0 Talu Telephon Bill on 20 Tolu Telephone Bill ko Telur Telephone Bill Mar-20 Tlur Telephone Bill Apr-20 or Telephon Bill Telephan, Bill Juno Tour Telephone Bill l-20 Tower Telephano Bill 1-Aug-20 Telur Telephone Bill -Sep-20 Telur Tolophone Bill Oct-2 Blu Tolophon, Bill Hoy 2 Tour Tolophone Bill -02-zu Telu Telephone Bill 1.-1% z Markoting Company Adverturing 1-F0=1 #12 Marketing Company Hverturing 1-749-14 WZ 14arketing Company Hverting 1-hyr 1% 12 r1arketing Company Adverting 1-14ay-14 HZ Marketing Company Advertuing 1- Jur. 18 WYZ Marketing Company Advertering 1-Jul-1% XYZ Markoting Company Advertiring 1-Aug-12 214arketing Campang Advertuing 1-50p-1% 2 Marketing Gampang Advertering 1-04k-1% XYZ Marketing Company Adversuing 1-toy-18 WYZ Marketing Company Advertoring 1-Dec-16 MYZ Marketing Company Admerking 1-Jan-19 WYZ Marketing Compani Adverkering 1-Feb 19 XYZ Marketing Company Advertin 1-Mar-19 XYZ Marketing Company Adworturing EFTE SEO 922 17 Daily Sales log Customer Database Expenses 30 $$$$$ 33333 3333 3 #3 ***** *** ***** ***** ***** ***** ***** ***** ***** 1-Nar 1992 Marketing Company Advantirina 1-Apr-1 NY Marketing Company Advertiring 1-May-19 SYzMarketing Company Advertising 31-Jun-9 SY&Marketing Company Advertiring -Jul-NNY Marketing Comeny deartiring 1-Aug-1 NY Marketing Company Advertiring 1-Sep-1 NY Marketing Company Advertiring 1-2-1 NY Marketing Company Advertving 1-Nov-N NY Marketing Company 1-Bu-NE Marketing Company Advertiring - lan 20 SYMarketing Company Advertising 1-Feb-20 SYMarketing Company Advertiring 1-Mar-20 SYZ Marketing Company Advertising 1-Apr-20 WY? Marketing Camping Adverturing 4-May-20 SYNarketing Company Aduertuing und Y Marketing Company Advorturing elu YYZ Marketing Company Advertuing A NYO Narketing Company Advertiring Sop SY2 Narkotin Company Advertiring t-02 NY Marketing Company t-New X2 Narkoting Company Advertiring 3-0 - YZ Marketing Company Adverting son-18 AiClothing Supplior Bwin Women's Shirts Feb-18 AClothing Supplier Bwin Women's Shirts Mar-18 AiClothing Supplier Buin Women's Shirts Paprt Alclothing Supplier Bwin Women's Shirts MEN AGlothing Supplior Buin Women's Shirts un Clothing Supplier Bwin Women's Shirts V A1Glothing Supplier Burin Women's Shirt Pavg-18 AiClothing Supplier Burin Women's Shirts 1-Sep-18 AlGlathing Supplier Burin Women's Shirts -Oct-13 Al Clothing Supplior Bwin Women's Shiru 1-Nov-18 A1 Clothing Supplior Bwin Women's Shirts -D2-13 AiClothing Supplier Burin Women's Shirts -Jan-19 A1Clothing Supplier Burin Women's Shirts H-Fob-19 AiClothing Supplier Bwin Women's Shirt 1-Mar-19 Alclothing Supplier Burin Womens Shirt 1-Apr-19 AlClothing Supplier Bwin Women's Shirts 1-May19 AlClothing Supplier Burin Women's Shirts -Jun-14 AiClothing Supplier Burin Women's Shirt -Jul-19 A1 Glothing Supplier Bwin Women's Shirt 1-Aug-19 AlClothing Supplier Burin Women's Shiro 1-Sop-19 AlClothing Supplier Burin Women's Shirter 1-04-19 A1 Clothing Supplier Burin Womens Shire 1-Nov-19 A1Clothing Supplier Burin Women Shirts 1-Dec-19 A1 Clothing Supplier Bwin Women's Shirt t-Jan 20 AiClothing Supplier Burin Women Shirt 1-Feb-20 Al Clothing Supplier Bwin Women Shirer 1-Mar-20 AlClothing Supplier Bwin. Womentshire 1-Apr-20 AlClothing Supplier Burin Womentshire 1-May-20 AlClothing Supplier Burin Wamenrshire 1-Jun-20 A1Clothing Supplier Bwin Womenrshire 1-Jul-20 A1Clothing Supplier Burin Womens shirt 1-Aug-20 AlClothing Supplier Burin Womendshire 12 30 12 2017 22 20 Daily Sales Log customer Database Expenses Lo A 10 352 350 : : . 1-Jul-19 AlClothing Supplier Burn Men's Pants 1-Aug-19ADlathim Supplier Burin Men's Pants 1-Sop 10 ATGothing Supplier Burin Men's Pants 1-04-19 AlClothing Supplier Burin Mon'. Pank 1-Nov-19 AlClothing Supplier Buin Mont Fans 1-Dec-19 AlClothing Supplier Burin Man's Pants 1 Jan 20 AlClothing Suplir un Mon Farr 1-Fob-20 AlClothing Supplier Burin Mon. Panter 1-Mar-20 AlClothing Supplier Bwin Man Ban 1-Apr-20 AlClothing Supplier Burin Man: Pants 1-May-20 AlClothing Supplior Buin Mon. Fins 1-Jun-20 AiClothing Supplier Burin Mon's Pants 1-Jul-20 A1Clothing Supplier Bwin Mont Pants 1-Aug-20 AlClothing Supplier Burin Man's Pant 1-Sop-20 AlClothing Supplier Burin Men's Ranta 1-Oct-20 AlClothing Supplior Bwin Mon: Pants 1-Nov-20 AlClothin Supplier Bwin Monsant 1-Doc-20 AlClothing Supplier Bwin Mont Pants 1-Jan-1 Staplos OfficSupplier 1-Feb-18 Stapler offie Supplies 1-Mar-18 Staplo Offi, Supplies 1-Apr-18 Staplar Off Supplier t-Mart Staplor Office Supplin Jan 13 Slaaplo Offisa Suppli 1- Jul-10 Staples Office Supplis 09-18 Stapler Office Supplies -Sep-Stapler Difice Supplier 1-02-18 Stapler Offis Supplies 1-Nap Staphor Office Supplies -Destapler Ofis Supplies 1-Jan-19 Stapler Office Supplier 1-Feb-19 Stapler Ofiss Suppe Mar-19 Stapler Office Supplies - App 19 Staples Office Supplier 1-May-19 Stapler OFFE Supplier - Jun 19 Stapler Office Supply 1 Jul 19 Stapler Office Supplier 1-Aug-19 Stapler Office SuppIN 112 1-80p-14 Staplos Difier Supplin Offise Supplis 4-Hop-19 Staplor Office Supplin 1-Dec-14 Stapler Office Supplier 1-Jan-20 Skalor OFFICS Surcler 17 1-ok-20 karla Office Supplier 1-Mar-20 Charles Office Supplier 1-Apr-20 Saaplar OFFICES 1-14 Y Z kapler Office Supplier 1- Jarzh Stapler OF SUP 1-Jul-ze Stapler OFFS OFFIKS 1-5 -20 Skapla OG Super 1-02-20 Smart OFFSET 1-May-20 darle OFFICE 1D20 Charles OF EPIS MINIMAS WMMMMM. www ... IM BIL 10 12 22 1 1113 1 COM Customer Database 2 HT ***** *** 18+# +++++ 20 1-Aug-20 Al Clothing Supplier Bwin Women's Shir 7-Sep-20 AlGlathing Supplier Burin Women's Shirts RO