Bus. 91L Excel Assignment
Sales Spreadsheet
(20 points possible)

For this assignment, you will create a Coffee Shop Sales Forecast for a six month period.

Before you begin creating a worksheet, it is a good idea to create a drawing or rough outline of how you want it to look. Refer to this sample. My numbers are rounded so they won’t exactly match yours.

Office 2007 instructions are in Red. Instructions specific to older versions are in Blue.

Directions:

1. Type “Java Juice Sales Forecast”, into cell B1. Format the heading as bold and use a size 16 font. Use the Merge and Center icon to merge cells B1 to I1 (Eye One).

Office 2007 – Look in the Home Tab, Alignment Group

2. Enter dates in cells B3 to G3: “Jan-00” to “Jun-00”. (Hint: don’t type in the words, use number keys: 1-1-2000 or 1/1/2000.) Format the cells to show the Month and Year as shown in the sample. (Hint: use the Numbers Tab in the Format dialog box) In cell H3 type the word Totals. In I3 type the % sign. Format cells B3 to I3 as centered, bold, with a red font. 

Office 2007 - To format numbers, look on the Home Tab, Number Group. There is a checkbox next to the word Number. Click on it.

3. Add Text to cells A1 to A26, as shown in the sample. Include your name in cell A2.

4. For the Food Sales, Beverage Sales, Ingredient Costs, and Ratios cells, use a blue font and apply an underline format.

5. For the Sandwich, Soups/Salads, Desserts, Coffee, Tea, Beer/Wine, Juices, and Soft Drinks cells use a green font.

6. For the three food categories and the five beverage categories, enter sales values for January in column B. You can either use the numbers provided in the sample or make up your own. When you enter these numbers try to be realistic. Remember that our coffee shop is open 6 days a week, 16 hours a day, which equals about 420 hours per month.

7. Create formulas to obtain the sales values for February. Enter these formulas in Column C.

• Row 5: Sales of Sandwiches sales will increase by $500 each month.
• Row 6: Sales of Soups/Salads will increase 9% each month.
• Row 7: Sales of Desserts will increase 5% each month.

• Row 11: Sales of Coffee will increase by 3% each month.
• Row 12 Sales of Tea will not change. Use a formula, not a number.
• Row 13 Sales of Beer/Wine will decrease by 3% each month
• Row 14 Sales of Juices will increase by 15% each month.
• Row 15 Sales of Soft Drinks will increase by $80 each month.

8. Format the cells in row 5 and row 11 as Currency with no decimal places. (Hint: the currency icon on the toolbar is mislabeled. It is actually accounting format. You will have to use the number tab in the format dialog box to find the currency format.)

Office 2007 – they changed the icon’s name to Accounting style.

9. Format the cells in the other sales categories as “general” numbers with no decimal places.

10. Use the summation icon ‘Σ’ to enter formulas in cells B8, C8, B16 and C16 The formulas will refer to the set of numbers directly above these cells. Format the cells as currency with no decimal places. 

Office 2007 - Look in the Home Tab, Editing Group to find the AutoSum icon.

11. Format the cells B25 and B26, as percentages with no decimal places.

Office 2007 - To format numbers, look on the Home Tab, Number Group. There is a checkbox next to the word Number. Click on it.

12. Enter a number between 20% and 30% in cell B25. (Food Ingredient Cost Ratio)

13. Enter a number between 10% and 20% in cell B26. (Beverage Ingredient Cost Ratio)

14. Enter a formula for “Food Costs” in cell B19. (Hint: =Total Food Sales * the Food Cost Ratio in cell B25.) Use an absolute cell reference referring to cell B25 for the Food Cost Ratio. Format cell B19 as currency with no decimal places. Copy the formula to cell C19. Check to make sure the cell reference still refers to cell B25. (Hint: Use dollar signs in your formula.)

Office 2007 - To format numbers, look on the Home Tab, Number Group. There is a checkbox next to the word Number. Click on it.

15. Enter a formula for “Beverage Costs” in cell B20.” (Hint: =Total Beverage Sales * the Beverage Cost Ratio.) Use an absolute cell reference referring to cell B26 for the Beverage Cost Ratio. Format cell B20 as currency with no decimal places. Copy the formula to cell C20. Check to make sure the cell reference still refers to cell B26. (Hint: Use dollar signs in your formula)

16. Enter a formula in Cell B22 to calculate the Net Revenue. This will include Food Sales, + Beverage Sales, - Food Cost, - Beverage Cost. Format it as Currency, with no decimal places. Copy the formula into Cell C22.

17. You should now have finished entering formulas in Column C, rows 5 through 22. Copy the formulas into columns D through G.

18. Using the summation icon ‘Σ’, insert =SUM formulas into column H showing the total for each row (5 through 20) that contains data.

Office 2007 - Look in the Home Tab, Editing Group to find the AutoSum icon.

19. Format the cells in column I (EYE) as percents and add a light yellow background.

20. In cell I5 write a formula, using cell references, to show the sandwich sales as a percentage of total food sales. (Hint: Divide Total Sandwich Sales by Total Food Sales.) Include 2 dollar signs in the divisor cell reference. Copy the formula in cell I5 into cells I6, and I7. Change the number of decimal places to 1.

21. In cell I11 write a formula, using cell references, to show the coffee sales as a percentage of total beverage sales. (Hint: Divide Total Coffee Sales by Total Beverage Sales.) Include two dollar signs in the divisor cell reference. Copy the formula in cell I11 into cells I12, I13, I14, and I15.

22. Insert =SUM formulas into cells I8 and I16 to show the total of the percentages. Format the cells to show no decimal places.

23. You can use the data validation feature to provide messages to guide spreadsheet users. Click on Cell A3. On the Menu Bar, select Data, then Validation. Office 2007 - To reach this dialog box, look on the Data Tab, Data Tools Group, Data Validation. Click on the Input Message tab. In the title box type: “Warning!” In the input message box, type: “Leave this cell blank.” Click OK.

Did your message appear? Click on another cell. Did the message go away? Click back on A3.

24. Add lines (Hint: use the borders icon) to your Forecast as shown on the sample.

Office 2007 - The Borders icon is in the Home Tab, Font Group.

25. Turn the gridlines off. This option is found on the Tools Menu, Options Submenu, View tab.

Office 2007 – Look in the Page layout Tab, Sheet Options Group.

26. Select the entire worksheet and increase the height of the rows to 14. (Hint: From the Menu Bar, select Format, Row, Height and enter 14 and click OK. Office 2007 – Look in the Home Tab, Cells Group, Format Category.) Click on cell A1. Click and drag to reduce the height of rows 9, 17 and 21 to 9.00. Increase the height of row 1 to 21.00.

27. Change the name of the worksheet tab to Sales. (Hint: right-click on the tab.)

Office 2007 – Right click on the tab or look in the Home Tab, Cells Group, Format Category.

28. Create a copy of the Sales spreadsheet. (Hint: right-click on the tab.) Office 2007 – Right click on the tab or look in the Home Tab, Cells Group, Format Category. Rename the tab on the copy to Formulas. In the Formulas worksheet, switch to the view that shows the formulas in the cells (Control and ~).

29. Print both the Sales worksheet and the Formulas worksheet (Hint: use the landscape setting instead of portrait.) Office 2007 – Look in the Page Layout Tab, Page SetUp Group, Orientation Category.

30. Save the file. Office 2007 – Click on the Office Button.