; Forecast Sales - PDF
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Forecast Sales - PDF

VIEWS: 200 PAGES: 4

Forecast Sales document sample

More Info
  • pg 1
									Sales Forecast
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. As you work on the assignment, refer to this
sample.

                                          Java Juice Sales Forecast

Prepared by: Your Name


                        January     February       March          April        May          June          Total         %
      Food Sales
      Sandwiches         $42,000      $42,500      $43,000       $43,500      $44,000      $44,500      $259,500       47.0%
     Soup/Salads           19000        20710        22574         24606        26820        29234        142943       25.9%
       Desserts            22000        23100        24255         25468        26741        28078        149642       27.1%
Total Food Sales         $83,000      $86,310      $89,829       $93,573      $97,561     $101,812      $552,085       100%


    Beverage Sales
        Coffee           $34,000      $35,020      $36,071       $37,153      $38,267      $39,415      $219,926       46.8%
          Tea              15000        15000        15000         15000        15000        15000         90000       19.2%
       Beer/Wine           12000        11640        11291         10952        10624        10305         66811       14.2%
         Juice              5000         5750          6613         7604          8745       10057         43769        9.3%
      Soft Drinks           8000         8080          8160         8240          8320         8400        49200       10.5%
    Total Beverage
         Sales           $74,000      $75,490      $77,134       $78,949      $80,956      $83,177      $469,706       100%




    Ingredient Costs

      Food Costs         $24,900      $25,893      $26,949       $28,072      $29,268      $30,544      $165,626

    Beverage Costs       $11,100      $11,324      $11,570       $11,842      $12,143      $12,477       $70,456




     Net Revenue       $121,000      $124,584     $128,444      $132,608     $137,105     $141,969      $785,710




     Ratios
Food Cost as a %
    of Sales                 30%
Beverage Cost as
  a % of Sales               15%




Adopted with permission from "Business Productivity Tools", Second Edition, published by Kendall Hunt. The author is Mike 
Splane. ISBN # 978‐0‐7575‐5879‐5 
 
Directions:
1. Click and drag to double the width of column A.
2. Merge cells A1:I1 (I-One), and then A2:B2. (Hint: Home tab -> Alignment group -> Merge
and Center icon.)
3. Type Java Juice Sales Forecast into cell A1. Format the text as bold with 16 font
size.
4. Enter Prepared by: and your name in cell A2.
5. Enter the names of months in cells B3:G3, starting with January. Enter Total in cell
H3, and % in cell I3. Center these cell contents and apply a red font.
6. Add Text to cells A4:A26 as shown above. Center the cell contents. Format these
cells as wrap-text. (Hint: Home tab -> Alignment group -> Wrap-text icon.)
7. For the Food Sales, Beverage Sales, Ingredient Costs, and Ratios cells, change the
font color to blue and add underlining.
8. For the Sandwiches, Soups/Salads, Desserts, Coffee, Tea, Beer/Wine, Juices, and
Soft Drinks cells, change the font color to light green.
9. For the 3 food categories and the 5 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.
10. 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 by 9% each month.
   • Row 7: Sales of Desserts will increase by 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.
11. Enter formulas in cells B8, C8, B16, and C16 using the SUM() function to show
totals of the numbers above these cells. (Hint: Home tab -> Editing group -> AutoSum
icon ‘Σ’.)
12. Format cell range B6:H15 as General numbers with no decimal place.
13. Format cell ranges B5:H5, B8:H8, and B16:H22 as Currency with no decimal
place.
14. Format cells B25 and B26 as Percentages with no decimal place.




Adopted with permission from "Business Productivity Tools", Second Edition, published by Kendall Hunt. The author is Mike 
Splane. ISBN # 978‐0‐7575‐5879‐5 
 
Industry Cost Ratios: Prime costs are combination of the Cost of Goods Sold and Payroll. Total
prime costs should run from 60 to 69% of sales. The closer to 60%, the better.
In more detail, the percentages look like:
       Food Cost                      27 to 33%
       Wine Cost                             27 to 46%
       Liquor Cost                           14 to 31%
       Beer Cost                             10 to 25%
       Other Beverage Costs                  10 to 25%
       Payroll                               25 to 35%
15. Enter a value between 20% and 30% in cell B25. (i.e. Food Ingredient Cost Ratio)
16. Enter a value between 10% and 20% in cell B26. (i.e. Beverage Ingredient Cost Ratio)
17. Enter a formula in Cell B19 to calculate Food Costs. (Hint: =Total Food Sales * the
Food Cost Ratio in cell B25.) Use an absolute cell reference, or a mixed cell reference,
referring to cell B25.
18. Enter a formula in Cell B20 to calculate Beverage Costs. (Hint: =Total Beverage Sales
* the Beverage Cost Ratio.) Use an absolute cell reference, or a mixed cell reference,
referring to cell B26.
19. Enter a formula in Cell B22 to calculate the Net Revenue. (Hint: = Food Sales +
Beverage Sales - Food Costs - Beverage Costs.) Copy the formula into cell C22.
20. You should now have finished entering formulas in the range C5:C22. Copy the
formulas the range D5:G22 row by row.
21. If decimal places are showing in any of the numbers, use the reduce-decimals icon
(Hint: Home tab->Numbers group) to eliminate them. Pennies are never shown in a
forecast.
22. Enter formulas in cell ranges H5:H8 and H11:H16 using the SUM() function to show
total for each row. (Hint: Home tab -> Editing group -> AutoSum icon ‘Σ’.)
23. Format cell ranges I5:I8 and I11:I16 as Percentages with one decimal place.
Apply a yellow fill color to the cells.
24. Enter a formula in cell I5 to convert sandwich sales into a percentage of total food
sales. (Hint: Total Sandwich Sales ÷Total Food Sales.) Use an absolute cell reference,
or a mixed cell reference, referring to cell H8. Copy the formula in cell I5 into cells I6
and I7.
25. Enter a formula in cell I11 to convert coffee sales into a percentage of total
beverage sales. (Hint:Total Coffee Sales ÷ Total Beverage Sales.) Use an absolute cell
reference, or a mixed cell reference, referring to cell H16. Copy the formula in cell I11
into cells I12, I13, I14, and I15.
26. Enter formulas in cells I8 and I16 using the SUM() function to show totals of the
percentages above these cells. (Hint: Total must be 100%) Format these cells to show no
decimal places.
27. Use Data Validation in cell A3. (Hint: Click on cell A3. Data tab -> Data Tools group ->
Data Validation.) In the Data Validation dialog box, select Input Message tab. In the title



Adopted with permission from "Business Productivity Tools", Second Edition, published by Kendall Hunt. The author is Mike 
Splane. ISBN # 978‐0‐7575‐5879‐5 
 
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?
28. Add lines (Hint: Home tab -> Font group -> Borders icon) to worksheet as shown in the
sample above.
29. Turn gridlines off. (Hint: Page Layout tab ->Sheet Options group -> uncheck View and
Print.)
30. Change the name of the worksheet to Sales. (Hint: Right-click on the worksheet tab,
or Home tab -> Cells group -> Format category -> Rename Sheet.)
31. Create a copy of the Sales worksheet. (Hint: Right-click on the worksheet tab, or Home
tab -> Cells group -> Format category -> Move or Copy Sheet.)
32. Rename the tab on the copy to Formulas. (Hint: Ctrl + ~, or Formula Tab -> Formula
Editing group -> show Formulas icon.)
33. Change page layout orientation to Landscape. (Hint: Page Layout tab -> Page Set-Up
group ->Orientation category -> Landscape.)
34. Save the file using your own name as the file name.

 




Adopted with permission from "Business Productivity Tools", Second Edition, published by Kendall Hunt. The author is Mike 
Splane. ISBN # 978‐0‐7575‐5879‐5 
 

								
To top
;