JudgeRainPorcupine33
Year 1 Year 2 Year 3 Notes…
Year 1 Year 2 Year 3 Notes
Sales revenue 550000 Units are sold for $10 each
Units manufactured 55000 65000 60000
Supply cost 96250 113750 $1.75 / unit variable cost
Shipping costs 82500 97500 $1.50 / unit
Personnel cost
Machine operators 80,000 120,000 80,000
Administrative personnel 72,000 72,000 72,000
Manufacturing manager 55,000 55,000 55,000
Distribution / marketing 45,000 45,000 45,000
Administrative expenses 30,000 30,000 30,000
Marketing 25,000 35,000 25,000
Facilities 60,000 60,000 60,000 $5,000 / month in rent/utilities
Overview
For this exercise, you will be creating a data set based on three years of revenue and expenses. These three years of
revenue and expenses are provided (on sheet 2) as a starting point. You will have two projections based on the
trend data and explain how you arrived at your calculation.
For the purposes of this assignment we are dealing with a manufacturing organization, and the data provided is based
on specific unit numbers manufactured and sold. Keep in mind, as you prepare your budget, that changes in units
manufactured would potentially impact (or be impacted by) personnel costs, shipping costs and supply costs.
Marketing is also a consideration you’ll want to be mindful of, from an associated cost perspective.
For the sake of this example, the ‘administrative expenses’ category would include non-personnel and non-facility
related expenses. Facilities expenses would include rent and utilities. You are welcome to interpret these
expense categories in your own way.
The data provided are also based on 100% of units manufactured being sold. There is no carry-over of inventory from
prior years.
Instructions
Fill your data into the cells below based on estimations of the data sets you’re providing. This should be
data based on the trends you have identified in a simple analysis of the data set on sheet 2. For this
assignment, there is no auto-totaling functionality built into the spreadsheet so you’ll need to create your own
formulas based on the data provided. This will provide you with an opportunity to further explore the impact of
per-unit cost on both the revenue and expense side.
Any cells highlighted in orange are cells to which you need to add data.
In addition to the data you are including in your projections, please answer the questions regarding your
calcuations and how you arrived at the particular profit/loss numbers.
Projection 1 Projection 2
(Revenue)
Sales Revenue
(Expenses)
Cost of sales
Units sold
Manufacturing cost/unit
Shipping cost/unit
Marketing
Technology
General and administrative
Other operating expenses
Total Revenue
Total Expenses
(net gain/loss will auto-total) $0 $0
Questions:
1. In your two models above, how did you arrive at these projections? What trend(s) did you observe in the data?
2. Please explain your adjustments, if any, to projected expenses for your proposed budgets. Expand on why you
made these changes, if applicable. If they did not change, please explain why.
3. This is a simplified data set, assuming no increases in personnel cost based on raises. What would the budget impact be
if all personnel were provided with raises of 2%? Would this significantly impact your revenue projections?
