ISM 4547 – Spring 2024 Module 3 Project Assignment Your boss, Tom Dataman, just visited your work area and delivered to you what he described as “a very important project”. Tom is Director of Analytics for A-to-Z Supermarkets, a large food chain, with many stores. Tom and the Analytics Department have been under pressure to continue to provide additional insights from the “big data” captured during the grocery checkout process. He wants you to do a prototype project using Excel examining the soft drink sales from one store over a 4-week period. If this analysis provides benefit in the form of actionable information, the Merchandising department will want similar studies in the future from other stores. He also told you that the output of your analysis should be in professional form because it will probably be presented to the entire executive leadership team. Here are some other notes from your conversation with Dataman.   You are being provided with an Excel download of information by the IT department. Soft drink marketing is oftentimes used as a loss leader to promote more store traffic. You will notice that when heavy markdowns (specials) on soft drinks are run, sometimes the gross margin on a transaction is negative. Each transaction for the month provides key financial information from the project – here are some terms/definitions: o o o o o o o o  Our Cost (per Unit) – amount that we purchased the product for each unit (our inventory cost) List Amount (per Unit) – our normal price charged (to the customer) Markdown Amount (per Unit) – if the item was purchased on special, the discount amount is provided. Net Amount (per Unit) – final price for sale of the item after discount Total Sale – the Net Amount (per Unit) X the Number of Units Purchased (see UNITS) Dataman suggested you consider adding new fields for your analysis to the worksheet: Gross Margin = Units X (Net Amt – Our Cost), GM% (Gross Margin/Total Sale) Mfgr Group Name for Product (see worksheet 3 below – use a Lookup function to populate the field) Dataman has asked you to include the following worksheets in your workbook which you will submit. Note that the sales data will be file number 7 (the last workbook) on your tabs at the bottom: o 1 – Answer Sheet – see Questions to be answered on this tab. o 2 – Brand and Size Analysis – a Pivot Table showing total sales and total gross margin for each Brand/Size. This report is a summary. Use the conditional formatting (as visualization tool) to highlight what you think are the lowest profitability (Margin) products. 1 ISM 4547 – Spring 2024 Module 3 Project Assignment o 3 – Analysis by Mfg Group. Use one of the LOOKUP functions to add convert the Mfg names into the data so it will appear on your report. These lookup codes could be added to Tab 6. Group code 1 = “Coca-Cola”, Group code 2 = “Keurig Dr Pepper”, and Group code 3 = “PepsiCo”. Management is interested in summary Total Sales and Gross Profit % for each of the 3 groups (a very short analysis). o 4 – Cashier Production – Provide an analysis showing the top 10 Cashiers in descending order by the number of Units scanned throughout the month. Use Tab 6 information (below) to add the Cashier’s Name which you will insert in the original data or your analysis via use of a LOOKUP. o 5 – Use a GOAL SEEK function, on total 2-liter Diet Coke sales. Build a simple model for 2-liter Coke on in this worksheet as shown below: Actual Sale Scenario Units Sold XX,XXX XX,XXX Average Price $X.XXX $X.XXX Revenue $XX,XXX $XX,XXX Costs $XX,XXX $XX,XXX Margin $XX,XXX $XX,XXX For Scenario 2 assume marketing ran a 20% off promotions. Create a Goal Seek for the sale scenario to determine “What would Unit Sales have to be to increase total margin $ by 5% on this product”? Hint: your Goal Seek will assume a lower price and compute a new Units required to increase Margin by 5%. o 6 – Other Data – This tab provides the names of all the Cashier’s working at this location. Names Table (no changes required). Add Cashier Names to the sales file by using the LOOKUP function. (This tab is information and in not graded). o 7 – Soft Drink Sales (original spreadsheet which you have enhanced with some additional data). Use conditional formatting to highlight any gross margin amounts < 0. When you have completed tabs 1-5, then go back to the Answer Sheet (Tab 1) to answer question