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 >

Search JabSto ::

Custom Search