A Text Math & Trig Insert Function Recently Used Financial Date & Time v More Functions F2 XVfx es 20 1 2 The Lopez brothers, Angelo, Antonio, and Isalah, own the Lopez Cantina. Angelo is in charge of 3 marketing, and he is developing his sales forecast for next year. Because of his marketing efforts. he predicts a 5 percent increase in his monthly guest counts. Angelo is not aware of any anticipated menu price increases and assumes, therefore, that his weighted check average will remain stable. Hint use the ROUNDDOWN function for Guest Check Average. 7 8 9 10 Show all work to 2 decimals! Note Guest.counts should be whole numbers Using last year's sales and guest counts, estimate Angelo's weighted check average (average sales per quest) for the year. (Spreadsheet hint: Use the ROUNDDOWN function to two decimal places on the cell containing the weighted check average, coll D32, because it will be used in 15 another formula in part B.) 17 Check Average Month 10 January 20 February 21 March 2 April 29 May June 25 July 26 August 2 September 23 October 29 November 20 December I fol Sales Last Year $45 259.00 $48581.00 $50.05200 $46.072.00 $49.746,00 $48,856.00 $55 185.00 $59 162.00 $55300.00 $50.943.00 $54 097.00 551.041.00 Guest Count Last Year 4.823 5143 5029 4.983 5.163 5323 5.644 6,025 5.803 5,364 5483 5423 Weighted Check Average Total 21 2 33 B Using the weighted check average calculated in part A, determine Angelo's projected sales. as assuming a 5 percent increase in guest counts. (Spreadsheet hint: Use the ROUNDDOWN function to zero decimal places in the Guest Count Forecast column, cells C43C54. Use the SUM 17 function for the total coll C55. Otherwise, your answers will not be correct.) Guest Count Last Guest Count Weighted Check F2 Res fx 29 D 15 another formula in part B.) 16 17 Check Average 18 Month 19 January 20 February 21 March 22 April 23 May June Sales Last Yew $45,299.00 $48.681.00 $50.052.00 $46.002.00 $49.748.00 $48.866.00 $55.185.00 599 16200 $55.300.00 $50,943.00 $54,097.00 $51041.00 Guest Count Last Year 4823 5143 5,029 4983 5163 5323 5644 6,025 5,803 5364 5483 5423 25 August 27 September October November 30 December + Weighted Check Average 31 Total * B. Using the weighted check average calculated in part A. determine Angelo's projected sales, * assuming a 5 percent increase in guest counts. (Spreadsheet hint Use the ROUNDDOWN function to zero decimal places in the Guest Count Forecast column, cells C43C54. Use the SUM 2 function for the total coll C55. Otherwise, your answers will not be correct) Projected Sales Weighted Check Average $0.00 $0.00 $0.00 $0.00 $0.00 led fo 4 Month January February March 4 April 6 MM er June Muy August 5 September 51 October November 53 December 54 Total Guest Count Last Guest Count Year Forecast 4823 5143 5,079 4983 5163 5323 5.644 6,025 5.803 5.74 5.463 5423 $0.00 $0.00 $0.00 39.00 $0.00 50.00 $0.00 56 57 59 CO 01