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.

Calculations Section

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

“

NA.

”

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.

FIGURE 7-4

Calculations section

Selling price

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

decimal places.

—

Units sold

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.

Snap cost

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

’

(1

0.10)

$0.55. Format

þ

¼

this value as currency with two decimal places.

Strap cost

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

places.

—

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

decimal places.