show full work please! the excel document is the last picture
Paste Java Julce Caf - Sales Forecast Prepared by: Jane Doe; Section 02 Open % of January February March April May June Total Total A1 Food Sales Sandwiches $83,000 $83,500 $84,000 $84,500 $85,000 $85,500 $505,500 46.86% Soups $38,000 $39,900 $41,895 $43,990 $46,189 $48,499 $258,473 23.96% Desserts $44,000 $47,080 $50,376 $53,902 $57,675 $61,712 $314,745 29.18% 2 Prepared Total Food Sales $165,000 $170,480 $176,271 $182,392 $188,864 $195,711 $1,078,717 100.00% 3 4 Food Sales Beverage Sales 5 Sandwiche Coffee $68,000 $70,040 $72,141 $74,305 $76,535 $78,831 $439,852 47.65% 6 Soups Tea $30,000 $30,000 $30,000 7 Desserts $30,000 $30,000 $30,000 $180,000 19.50% Beer/Wine $24,000 $22,800 $21,660 $20,577 $19,548 $18,571 8 Total Food $127,156 13.77% Julce $10,000 $11,000 $12,100 $13,310 $14,641 $16,105 $77,156 9 8.36% Soft Drinks $14,000 $15,000 $16,000 $17,000 $18,000 10 Beverage Sa $19,000 $99,000 10.72% Total Beverage Sales $146,000 $148,840 $151,901 $155,192 $158,724 $162,506 11 Coffee $923,164 100.00% 12 Tea Intredient Costs 13 Beer Wine Food Cost $54.450 $56,258 14 Juice $58,169 $60,189 $62,325 $64,585 $355,977 Beverage Costs $24.820 $25,303 $25,823 $26.383 15 Soft Drinks $26,983 $27,626 $156,938 Net Revenue 16 Total Beverage $231,730 $237,759 $244,179 $251,012 $258,280 $266,007 $1,488,967 17 18 Ingredient Cose 19 Food Cost Food Cost 20 Beverage Costs 33% 21 Net Revenue Beverage Cost% 17% In this assignment, you will create a coffee shop sales forecast for a six-month period. You write formulas and format cells per instructions below. 1. Open SalesForecastAssignment.xlsx from the files folder in Canvas and go to the Sales Forecast sheet. Page 1 2. Merge and Center the cell range A1:H1. Make the text bold with 16-point font size. Make sure the height of Row 1 is tall enough to clearly show this title. 3. Merge cells A2:02. Put in your name and section number. Make the text bold with 12-point font size. Left-align (not center) text in this merged cell. 4. From B3, use AutoFill to put February... June in cells C3 to G3. 5. Horizontally center the cells A4 to A21. 6. Enter 30% in cell B25; enter 15% in cell B26. 7. Enter formulas in cells C5:07 and C11:C15 to calculate the sales values for February as follows: C5: Sales of Sandwiches sales will increase by $500 from previous month. Ce: Sales of Soups will increase 5% from previous month. 07: Sales of Desserts will increase 7% from previous month. C11: Sales of Coffee will increase by 3% from previous month. C12: Sales of Tea will not change from previous month. C13: Sales of Beer Wine will decrease by 5% from previous month. C14: Sales of Juices will increase by 10% from previous month. NNNNN 2 3 3 12. Use AutoFill to copy the formulas from cells C5:07 to D5:G7, and from cells C11:C15 to D11:G15. 13. Use the AutoSUM shortcut (1.0. ALT and = at the same time for Windows; Shift + Command + T for Mac) to enter a formula in B8 to show the total of cell range B5:37. Use AutoFill to copy the formula from cell B8 to cells CS:G8. 14. Use AutoSUM shortcut (i.6. ALT and = at the same time for Windows; Shift + Command + T for Mac) to enter a formula in B16 to show the total of cell range B11:B15. Use AutoFill to copy the formula from cell B16 to cells C16:16. 15. Food costs is to be calculated as a percentage of Total Food Sales for each month. The percentage is as specified in cell B25. Enter a formula in cell B19 to show Food Costs. Use cells B8 (Total Food Sales in January) and B25 (Food Cost %) in your formula. 16. Use AutoFill to copy the formula from cell B19 to the cell range C19:G19 for the other months. Make sure you use an appropriate reference for cell B25 such that it shows up in all the formulas. Page 12 17. Beverage costs is to be calculated as a percentage of Total Beverage Sales for each month. The percentage is as specified in cell B26. Enter a formula in cell B20 to show Beverage Costs. Use cells B16 (Total Beverage Sales in January) and B26 (Beverage Cost %) in your formula. 18. Use AutoFill to copy the formula from cell B20 to the cell range C20:G20 for the other months. Make sure you use an appropriate reference for cell B26 such that it shows up in all the formulas. 19. Enter a formula in B21 to calculate the Net Revenue for January: add Total Food Sales (B8) and Total Beverage Sales (B16); then subtract Food Costs (B19) and Beverage Costs (B20). Use AutoFill to copy the formula into cell range C21:G21 for the other months. 20. Use AutoSUM shortcut (1.0. ALT and = at the same time for Windows; Shift + Command + T for Mac) to insert formulas into cell ranges H5:H8, H11:H16, and H19:H21. These are total sales values and costs across all the months. 21. Format the cell range B5:H21 as Currency with no decimal places. Make the text bold. 22. In B25, replace 30% with 33%. In B26, replace 15% with 17%. Make sure numbers in cell ranges B19:H19, and B20:H20 are re-calculated automatically. 23. In cell 15 write the formula =H5/H8. This is the percentage of sandwich sales as compared to the total food sales. 24. Use AutoFill to copy the formula from cell 15 to cells 16:18. Do you get #DIV/0! error? Do you know why? Now fix the formula in cell 15 by changing H8 to either H$8 (i.e. mixed reference) or $H$8 (i.e. absolute reference). Use AutoFill to copy the formula from cell 15 to cells 16:18. 25. In cell 111 write the formula =H11/H$16 (le, mixed reference to H16) or =H11/$H$16 (i.e. absolute reference to H16). This is the percentage of coffee sales as compared to the total beverage sales. Use AutoFill to copy the formula from cell 111 to cells 112:116. 26. Save the file you have been working on to your desktop, using a combination of your own name and "SalesForecastAssignment" (e.g. "Jane Doe-SalesForecastAssignment.xlsx") as the file name. Submit this file to Canvas. A- V O Home Insert Draw Page Layout Formulas Data Review View Tell me Share X % Conditional Formatting E Format as Table Paste Font Alignment Number Cells Editing Analyze Cell Styles Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left of... A20 X fx Beverage Costs G Java Juice Caf-Sales Forecast 2 Prepared by: Your name; e.e. Jane Doe Section 1.0.01 or 02 Ye D E Ianuary Total of Total 83000 38000 44000 68000 30000 24000 10000 14000 Food Sales 5 Sandwiches 6 Soups 7 Desserts 8 Total Food Sales 9 10 Bevere Sales 11 Coffee 12 Tea 13 Beer Wine 14 15 Soft Drink 26 Total Beverage Sales 17 18 ingredient costs 19 Food Cost 20 Beverage Costs 21 Net Revenue 22 23 24 25 Food Cost 26 Beverage Cost 27 28 29 30 31 32 33 34 35 36 37 30% 15% 13 Paste Java Julce Caf - Sales Forecast Prepared by: Jane Doe; Section 02 Open % of January February March April May June Total Total A1 Food Sales Sandwiches $83,000 $83,500 $84,000 $84,500 $85,000 $85,500 $505,500 46.86% Soups $38,000 $39,900 $41,895 $43,990 $46,189 $48,499 $258,473 23.96% Desserts $44,000 $47,080 $50,376 $53,902 $57,675 $61,712 $314,745 29.18% 2 Prepared Total Food Sales $165,000 $170,480 $176,271 $182,392 $188,864 $195,711 $1,078,717 100.00% 3 4 Food Sales Beverage Sales 5 Sandwiche Coffee $68,000 $70,040 $72,141 $74,305 $76,535 $78,831 $439,852 47.65% 6 Soups Tea $30,000 $30,000 $30,000 7 Desserts $30,000 $30,000 $30,000 $180,000 19.50% Beer/Wine $24,000 $22,800 $21,660 $20,577 $19,548 $18,571 8 Total Food $127,156 13.77% Julce $10,000 $11,000 $12,100 $13,310 $14,641 $16,105 $77,156 9 8.36% Soft Drinks $14,000 $15,000 $16,000 $17,000 $18,000 10 Beverage Sa $19,000 $99,000 10.72% Total Beverage Sales $146,000 $148,840 $151,901 $155,192 $158,724 $162,506 11 Coffee $923,164 100.00% 12 Tea Intredient Costs 13 Beer Wine Food Cost $54.450 $56,258 14 Juice $58,169 $60,189 $62,325 $64,585 $355,977 Beverage Costs $24.820 $25,303 $25,823 $26.383 15 Soft Drinks $26,983 $27,626 $156,938 Net Revenue 16 Total Beverage $231,730 $237,759 $244,179 $251,012 $258,280 $266,007 $1,488,967 17 18 Ingredient Cose 19 Food Cost Food Cost 20 Beverage Costs 33% 21 Net Revenue Beverage Cost% 17% In this assignment, you will create a coffee shop sales forecast for a six-month period. You write formulas and format cells per instructions below. 1. Open SalesForecastAssignment.xlsx from the files folder in Canvas and go to the Sales Forecast sheet. Page 1 2. Merge and Center the cell range A1:H1. Make the text bold with 16-point font size. Make sure the height of Row 1 is tall enough to clearly show this title. 3. Merge cells A2:02. Put in your name and section number. Make the text bold with 12-point font size. Left-align (not center) text in this merged cell. 4. From B3, use AutoFill to put February... June in cells C3 to G3. 5. Horizontally center the cells A4 to A21. 6. Enter 30% in cell B25; enter 15% in cell B26. 7. Enter formulas in cells C5:07 and C11:C15 to calculate the sales values for February as follows: C5: Sales of Sandwiches sales will increase by $500 from previous month. Ce: Sales of Soups will increase 5% from previous month. 07: Sales of Desserts will increase 7% from previous month. C11: Sales of Coffee will increase by 3% from previous month. C12: Sales of Tea will not change from previous month. C13: Sales of Beer Wine will decrease by 5% from previous month. C14: Sales of Juices will increase by 10% from previous month. NNNNN 2 3 3 12. Use AutoFill to copy the formulas from cells C5:07 to D5:G7, and from cells C11:C15 to D11:G15. 13. Use the AutoSUM shortcut (1.0. ALT and = at the same time for Windows; Shift + Command + T for Mac) to enter a formula in B8 to show the total of cell range B5:37. Use AutoFill to copy the formula from cell B8 to cells CS:G8. 14. Use AutoSUM shortcut (i.6. ALT and = at the same time for Windows; Shift + Command + T for Mac) to enter a formula in B16 to show the total of cell range B11:B15. Use AutoFill to copy the formula from cell B16 to cells C16:16. 15. Food costs is to be calculated as a percentage of Total Food Sales for each month. The percentage is as specified in cell B25. Enter a formula in cell B19 to show Food Costs. Use cells B8 (Total Food Sales in January) and B25 (Food Cost %) in your formula. 16. Use AutoFill to copy the formula from cell B19 to the cell range C19:G19 for the other months. Make sure you use an appropriate reference for cell B25 such that it shows up in all the formulas. Page 12 17. Beverage costs is to be calculated as a percentage of Total Beverage Sales for each month. The percentage is as specified in cell B26. Enter a formula in cell B20 to show Beverage Costs. Use cells B16 (Total Beverage Sales in January) and B26 (Beverage Cost %) in your formula. 18. Use AutoFill to copy the formula from cell B20 to the cell range C20:G20 for the other months. Make sure you use an appropriate reference for cell B26 such that it shows up in all the formulas. 19. Enter a formula in B21 to calculate the Net Revenue for January: add Total Food Sales (B8) and Total Beverage Sales (B16); then subtract Food Costs (B19) and Beverage Costs (B20). Use AutoFill to copy the formula into cell range C21:G21 for the other months. 20. Use AutoSUM shortcut (1.0. ALT and = at the same time for Windows; Shift + Command + T for Mac) to insert formulas into cell ranges H5:H8, H11:H16, and H19:H21. These are total sales values and costs across all the months. 21. Format the cell range B5:H21 as Currency with no decimal places. Make the text bold. 22. In B25, replace 30% with 33%. In B26, replace 15% with 17%. Make sure numbers in cell ranges B19:H19, and B20:H20 are re-calculated automatically. 23. In cell 15 write the formula =H5/H8. This is the percentage of sandwich sales as compared to the total food sales. 24. Use AutoFill to copy the formula from cell 15 to cells 16:18. Do you get #DIV/0! error? Do you know why? Now fix the formula in cell 15 by changing H8 to either H$8 (i.e. mixed reference) or $H$8 (i.e. absolute reference). Use AutoFill to copy the formula from cell 15 to cells 16:18. 25. In cell 111 write the formula =H11/H$16 (le, mixed reference to H16) or =H11/$H$16 (i.e. absolute reference to H16). This is the percentage of coffee sales as compared to the total beverage sales. Use AutoFill to copy the formula from cell 111 to cells 112:116. 26. Save the file you have been working on to your desktop, using a combination of your own name and "SalesForecastAssignment" (e.g. "Jane Doe-SalesForecastAssignment.xlsx") as the file name. Submit this file to Canvas. A- V O Home Insert Draw Page Layout Formulas Data Review View Tell me Share X % Conditional Formatting E Format as Table Paste Font Alignment Number Cells Editing Analyze Cell Styles Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left of... A20 X fx Beverage Costs G Java Juice Caf-Sales Forecast 2 Prepared by: Your name; e.e. Jane Doe Section 1.0.01 or 02 Ye D E Ianuary Total of Total 83000 38000 44000 68000 30000 24000 10000 14000 Food Sales 5 Sandwiches 6 Soups 7 Desserts 8 Total Food Sales 9 10 Bevere Sales 11 Coffee 12 Tea 13 Beer Wine 14 15 Soft Drink 26 Total Beverage Sales 17 18 ingredient costs 19 Food Cost 20 Beverage Costs 21 Net Revenue 22 23 24 25 Food Cost 26 Beverage Cost 27 28 29 30 31 32 33 34 35 36 37 30% 15% 13
