Microsoft Office Tutorials and References
In Depth Information
Introduction
The requirements document for the FroYoToGo Six-Month Financial
Projection worksheet is shown in Figure 3 – 2. It includes the needs, source of data,
summary of calculations, and chart requirements.
Worksheet Title
FroYoToGo Six-Month Financial Projection
Needs
A worksheet that shows FroYoToGo’s projected monthly sales, cost of goods
sold, gross margin, expenses, and operating income for a six-month period.
A clustered column chart that shows the expected contribution of each
expense category to total expenses.
Source of Data
Data supplied by the business owner includes projections of the monthly sales
and expenses based on prior year gures (see Table 3 – 1 on page EX 133).
Remaining numbers in the worksheet are based on formulas.
Calculations
The following calculations are needed for each month:
Cost of Goods Sold = Revenue Revenue Margin
Gross Margin = Revenue Cost of Goods Sold
Bonus Expense = \$5,000.00 if Revenue exceeds the Revenue for Bonus,
otherwise Bonus = 0
Commission expense = Commission percentage Revenue
Kiosk Rental expense = Kiosk Rental Percentage Revenue
Marketing expense = Marketing percentage Revenue
Equipment Repair and Maintenance expense = Equipment Repair and
Maintenance percentage Revenue
Total expenses = sum of Expenses
Operating Income = Gross Margin Total expenses
Chart Requirements
Show sparkline charts for revenue and each of the items noted in the
calculations area above.
Show a clustered column chart that shows the contributions of each month’s
expense categories to the total monthly expense gure.
BTW
Touch Screen
Differences
The office and Windows
interfaces may vary if you are
using a touch screen. For this
reason, you might notice that
the function or appearance
slightly from this chapter’s
presentation.
Figure 3 – 2
Using a sketch of the worksheet can help you visualize its design. The sketch
of the worksheet consists of titles, column and row headings, location of data values,
calculations, and a rough idea of the desired formatting (Figure 3 – 3a). The sketch of
the clustered column chart shows the expected contribution of each month’s operating
income to the six-month operating income (Figure 3 – 3b). The assumptions about
income and expenses will be entered at the bottom of the worksheet (Figure 3 – 3a).
The projected monthly sales revenue will be entered in row 4 of the worksheet. The
projected monthly sales revenue and the assumptions shown in Table 3 – 1 will be used
to calculate the remaining numbers in the worksheet.
BTW
BTWs
For a complete list of the
BTWs found in the margins
of this topic, visit the BTW
resource on the Student
Companion Site located on
www.cengagebrain.com. For