Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
For each year, your spreadsheet should show net income after taxes, cash on hand at the end of the year,
bank debt owed at the end of the year, and return on sales for the year. Format the net income, cash, and
debt cells as currency with zero decimals. Format the return on sales cells as a percentage with one decimal
place. These values are computed elsewhere in the spreadsheet and should be echoed here.
You should calculate intermediate results to use in the income and cash flow statements that follow.
Calculations, as shown in Figure 7-4, may be based on expected year-end 2011 values. When called for, use
absolute referencing properly. You must compute values by cell formula; use hard-coded numbers in formulas
only when instructed. Cell formulas should not reference a cell that contains a value of
2014 is expected to stay the same; in
other words, the proportion of small collars to all collars will stay the same, the proportion of large collars will
stay the same, and so on. Thus, prices and costs calculated here are averages for the company
Big Dog makes collars for dogs of all sizes. The product mix in 2012
s product mix
of sizes sold.
An explanation of each item in this section follows the figure.
The average selling price for collars of all sizes was $20 in 2011. The mix of sizes
sold is expected to stay the same in the future. The average selling price is expected to increase
3% each year. In other words, the 2012 value will be 3% greater than the 2011 value, the 2013
value will be 3% greater than the 2012 value, and so on. Format cells as currency with two
Big Dog sold a total of 20,000 collars in 2011. The mix of sizes sold is expected to
stay the same in the future. The number of collars sold in a year is expected to increase 3% each
year. Format these cells as a number with no decimal places.
Oil cost change factor
This value is the percentage change in the average price of a barrel of oil
from one year to the next. Note that the price was $80 in 2011; this number can be hard-coded
into the 2012 formula. Format this value as a percentage with one decimal place.
Raw material cost change factor
This factor is half of the year
s oil cost change factor. Format
this factor as a percentage with one decimal place.
This value is the expected average cost of collar snaps per year. The cost is based on
the prior year
s cost, and is increased or decreased by the raw material cost change factor. For
example, if the snap cost is $0.50 in one year and the raw material cost change factor is 10% the
next year, the expected snap cost in the next year would be $.50
this value as currency with two decimal places.
This value is the expected average cost of collar straps per year. The cost
computation is the same as the snap cost calculation. Format the value as currency with two decimal
Number of manufacturing employees
A manufacturing employee assembles 5,000 collars per
year (20 per day
250 work days). To determine how many manufacturing employees you
need, divide the expected production by 5,000 using the Roundup function. For example, if cell
M20 holds the number 102, the formula =Roundup(M20/5,0) would return the value 21 with no