Joe's Lawn Service has asked you to create various financial spreadsheets and a written memo to help him understand his finances for his business.
Create an Excel spreadsheet with formulae to demonstrate the following examples. Be sure to clearly label each example by using a different spreadsheet tab:
· Example 1 (Trade Discount, refer to Chapter 8): Using Excel formulae, calculate the discount of a mower that lists for $1,217 with the trade discount series of 15/4/1. Display formulae for each discount series as well as the final price of the item after all discounts have been applied.
· Example 2 (Depreciation, refer to Chapter 16): Create a depreciation schedule, using the sum-of-the-years'-digits method, for a mower that costs $1,217 with a salvage value of $150 with an expected life of 7 years
· Example 3 (Financial Statement, refer to Chapter 20): Prepare the following horizontal analysis of a balance sheet into Excel. Complete the information on total assets, total liabilities and owner's equity, increase and percent increase by using formulae in Excel. Be sure to format the percent increase (decrease) column to a decimal with one decimal place.
After creating the spreadsheet with the three examples above, write a memo that addresses the following points for Joe's questions and his files:
Explain what a trade discount is and why would Joe offer these.
Explain what is depreciation and why it is more applicable for Joe to use sum-of-the-years'-digits method versus the straight-line method.
Explain to Joe why he would want to prepare a horizontal analysis, such as what does the percent increase (decrease) column tell him about his assets and liabilities from years 2008 to 2009, and give suggestions on what he should do based on this analysis.
Joe's Lawn Service
Balance Sheet
2009
2008
Amount Increase (decrease)
Percent increase (decrease)
Assets
Cash
$10,814
$8,403
Accounts receivable
385
278
Inventory
857
1,098
Equipment
1,285
1,192
Total assets
Liabilities
Accounts payable
$9,597
$8,711
Wages payable
587
1,118
Total liabilities
Owner's equity
3,157
1,142
Total liabilities and owner's equity