allocated to commission or the cost of the kiosk rentals. In addition, you need to create a column chart that shows the breakdown of expenses for each month in the period.
The requirements document for the Kaitlyn’s Ice Cream Shoppe Six-Month Financial Projection worksheet is shown in Figure 3 – 2. It includes the needs, source of data, summary of calculations, and chart requirements.
Needs • A woksheet that shows Kaitlyn’s Ice Cream Shoppe projected monthly sales revenue, 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 figures (see Table 3–1). Remaining numbers in the worksheet are based on formulas. Calculations The following calculations are needed for each month: • Cost of Goods Sold = Revenue * (1 − Margin) • Gross Margin = Revenue − Cost of Goods Sold • Bonus expense = Predetermined bonus amount if Revenue exceeds the Revenue for Bonus, otherwise Bonus = 0 • Commission expense = Commission percentage × Revenue • Site 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 all 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 figure.
LAB1
Problem:
Eight-Year Financial Projection
Your supervisor in the finance department at August Online Technology has asked you to
create a worksheet for the flagship product that will project the annual gross margin, total expenses,
operating income, income taxes, and net income for the next eight years based on the assumptions
in Table 3–9.
Units Sold in Prior Year
235,411
Unit Cost
$150.00
Annual Sales Growth
3.25%
Annual Price Increase
3.00%
Margin
29.90%