Data to use for OPS Forecasting Assignment Small Cups Sold 2300 Medium Cups Sold 4100 Large Cups Sold 1956 Small Cup Price $4.25 Medium Cup Price $5.25 Large Cup Price $6.25 COGS Small will be calculated when you add Coffee Cost per Pound COGS Medium COGS Large Coffee Cost Per Pound $7.00 Management 3 Servers 31 Management Pay $900 per week Sever Pay (10 hours per server) $10.50 Online Advertisement $1400 Radio $2800 1. Enter your name and UID in cell B4 and B6 2. Choose the most recent decision period week as your baseline for sales, prices, and costs. a. You are to use your own data except in cells where the data is already provided. b. Add your baseline data to cells B10:B12,B27:B29,B34, and B36:B39. c. For Cells B44 and B45 insert a list to include the cost of advertising options and choose and option. i. Online Advertisement 1. $0 2. $1400 ii. Radio Spots 1. $0 2. $560 3. $1400 4. $2800 3. In Cells C10:C12 (Week 1) determine cups sales for all three cups sizes with a projected increase in sales of .5% increase each week. Since cups are sold in whole numbers use the ROUNDUP function to ensure the you have a whole number result. To earn full credit, you need to be properly referencing the growth values in Cells B23:B25. 4. Copy cell C10:12 using the fill handle or manual copy/paste across to Week 16. a. Right Justify Cup Sales data 5. In the Baseline Column determine a. Revenue i. Include all three cup sizes b. COGS i. Include all three cups sizes c. Gross Profit d. Salary Expense i. Include Managers and Servers pay e. Payroll Taxes i. Based on B16 f. Rent/Utilities Expense i. Include weekly rent and utilities g. Advertisement/Promo Expenses i. Include online and radio spots h. Net Profit 6. Copy the results of B13:B20 across to Week 16 . 7. Total values for C10:R10 and each row until C20:R20. 8. Save the file and submit it to Canvas for grading. Make sure you are using formulas/functions to determine the calculations. Example Solution File Aa Cald Operabons Lntei Ausiche tht (Gerecartind) Data to use for OPS Forecasting Assignment Small Cups Sold 2300 Medium Cups Sold 4100 Large Cups Sold 1956 Small Cup Price $4.25 Medium Cup Price $5.25 Large Cup Price $6.25 COGS Small will be calculated when you add Coffee Cost per Pound COGS Medium COGS Large Coffee Cost Per Pound $7.00 Management 3 Servers 31 Management Pay $900 per week Sever Pay (10 hours per server) $10.50 Online Advertisement $1400 Radio $2800 1. Enter your name and UID in cell B4 and B6 2. Choose the most recent decision period week as your baseline for sales, prices, and costs. a. You are to use your own data except in cells where the data is already provided. b. Add your baseline data to cells B10:B12,B27:B29,B34, and B36:B39. c. For Cells B44 and B45 insert a list to include the cost of advertising options and choose and option. i. Online Advertisement 1. $0 2. $1400 ii. Radio Spots 1. $0 2. $560 3. $1400 4. $2800 3. In Cells C10:C12 (Week 1) determine cups sales for all three cups sizes with a projected increase in sales of .5% increase each week. Since cups are sold in whole numbers use the ROUNDUP function to ensure the you have a whole number result. To earn full credit, you need to be properly referencing the growth values in Cells B23:B25. 4. Copy cell C10:12 using the fill handle or manual copy/paste across to Week 16. a. Right Justify Cup Sales data 5. In the Baseline Column determine a. Revenue i. Include all three cup sizes b. COGS i. Include all three cups sizes c. Gross Profit d. Salary Expense i. Include Managers and Servers pay e. Payroll Taxes i. Based on B16 f. Rent/Utilities Expense i. Include weekly rent and utilities g. Advertisement/Promo Expenses i. Include online and radio spots h. Net Profit 6. Copy the results of B13:B20 across to Week 16 . 7. Total values for C10:R10 and each row until C20:R20. 8. Save the file and submit it to Canvas for grading. Make sure you are using formulas/functions to determine the calculations. Example Solution File Aa Cald Operabons Lntei Ausiche tht (Gerecartind)