ASCC Biz Cafe Excel Operations Forecasting & Online Advertising Cost Options Worksheet

Biz Café Excel Operations AssignmentThis assignment is a forecasting exercise using your Biz Café Data. Successful competition of thisassignment will prepare you for the Biz Café Excel Capstone assignment.There are a number of ways to build your spreadsheet to accomplish forecasting models. I believe themodel we are using simplifies some of the concepts and calculations. The importance of preparing yourspreadsheet so that you can run “What If” scenarios is critical to being able to make informed decisions.I am not asking you to perform any “What If” scenarios in this exercise but will in the capstone. Thisassignment ensures that you can use your data and create a forecasting model that is capable of seeingquick results when you make operational changes.Your task is to forecast your net profit based on projected cup sales for four months using baseline dataand forecasting a .5% weekly increase in sales across all three cup sizes. You will need to use absolutecell referencing as appropriate.The baseline data will be the information from your most recent decision period. You will need to gointo your café account to gather the information. You can also obtain your cups sales via thecomparative results or in your café account.Action:• Login to Biz Café and Choose a recent decisiono Obtain information needed to complete assignment.• Download your decision period data as an alternative method to find your cups sales.Start FileInstructions:1. Enter your name in cell A22. 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 B4:B6, B19:B21, B25, and B26:B29.c. For Cells B33 and B34 insert a list to include the cost of advertising options and chooseand option.i. Online Advertisement1. $02. $1400ii. Radio Spots1. $02. $5603. $14004. $28003. In Cells C4:C6 (Week 1) determine cups sales for all three cups sizes with a projected increase insales of .5% increase each week. Since cups are sold in whole numbers use the ROUNDUPfunction to ensure the you have a whole number result. To earn full credit, you need to beproperly referencing the growth values in Cells B16:B18.4. Copy cell C4:C6 using the fill handle or manual copy/paste across to Week 16.a. Right Justify Cup Sales data5. In the Baseline Column determinea. Revenuei. Include all three cup sizesb. COGSi. Include all three cups sizesc. Gross Profitd. Salary Expensei. Include Managers and Servers paye. Payroll Taxesi. Based on B10f. Rent/Utilities Expensei. Include weekly rent and utilitiesg. Advertisement/Promo Expensesi. Include online and radio spotsh. Net Profit6. Copy the results of B7:B14 across to Week 16.7. Total values for C4:R4 and each row until C14:R14.8. Save the file and submit it to Canvas for grading. Make sure you are using formulas/functions todetermine the calculations.Example Solution File