Microsoft Office Tutorials and References

In Depth Information

**Cases and Places**

Apply your creative thinking and problem solving skills to design and implement a solution.

•
E
ASIER
••
M
ORE
D
IFFICULT

•
1: Break–Even Analysis

You can calculate the number of units you must sell to break even (break–even point) if you know the

ﬁ xed expenses, the price per unit, and the expense (cost) per unit. You have been hired by Fairview

Clothing to create a data table that analyzes the break–even point for prices between $8.00 and $14.25

in increments of $0.25. The following formula determines the break–even point:

Break–Even Point = Fixed Expenses / (Price per Unit – Expense per Unit)

Assume Fixed Expenses = $800,000; Price per Unit = $8.50; and Expense per Unit = $4.10. Enter the

data and formula into a worksheet and then create the data table. Use the Price per Unit as the input

cell and the break–even value as the result. For a price per unit of $10.50, the data table should show a

break–even point of 125,000 units. Protect the worksheet.

•
2: Salvage Value of an Asset

Jack Hollinsworth, owner of Hollinsworth Bakery, recently purchased a new commercial–sized oven

for his business. Jack wants a worksheet that uses the ﬁ nancial function SLN to show the oven’s

straight–line depreciation and a formula to determine the annual rate of depreciation. Straight–line

depreciation is based on an asset’s initial cost, how long it can be used (called useful life), and the price

at which it eventually can be sold (called salvage value). Jack has supplied the following information:

Cost = $124,857; Salvage = $30,000; Life = 8 years; and Annual Rate of Depreciation = SLN / Cost

Jack is not sure what selling price the oven will bring in 8 years. Create a data table that shows

straight–line depreciation and annual rate of depreciation for salvage from $25,000 to $35,000 in $500

increments. Use Excel Help to learn more about the SLN function. Protect the worksheet.

•
3: Saving for College

Your friends’ dream for their one–year–old son is that one day he will attend their alma mater, Tesla

University. For the next 15 years, they plan to make monthly payment deposits to a 529 College

Savings plan at a local bank. The account pays 4.5% annual interest, compounded monthly. Create

a worksheet for your friends that uses a ﬁ nancial function to show the future value (FV) of their

investment and a formula to determine the percentage of the college’s tuition saved. They have

supplied the following information:

Out of State Annual Tuition = $40,000; Rate (per month) = 4. 5% / 12; Nper (number of

monthly payments) = 15 * 12; Pmt (payment per period) = $375; and percentage of Tuition Saved

= FV / Tuition for four years

Your friends are not sure how much they will be able to save each month. Create a data table that

shows the future value and percentage of tuition saved for monthly payments from $250 to $850, in

$50 increments. Protect the worksheet.

Continued >