Microsoft Office Tutorials and References
In Depth Information
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.
FroYoToGo Six-Month Financial Projection
• 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.
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
• 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.
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
of your touch screen differs
slightly from this chapter’s
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.
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
detailed instructions about
accessing available resources,
contact your instructor for
information about accessing
the required les.