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
Amount Increase (decrease)
Percent increase (decrease)
Total liabilities and owner's equity© BrainMass Inc. brainmass.com October 25, 2018, 2:11 am ad1c9bdddf
The problem set deals with issues in accounting:balance sheet analysis,trade discount estimation, etc.
Accounting horizontal and vertical income statements
COOKIE & COFFEE CREATIONS INC.
Assets 2008 2007
Cash $ 34,324 $13,050
Accounts receivable 3,250 2,710
Inventory 7,897 7,450
Prepaid expenses 6,300 6,050
Equipment 96,500 75,500
Accumulated depreciation (25,200) (9,100)
Total assets $123,071 $95,660
Liabilities and Stockholders' Equity
Accounts payable $ 3,650 $ 2,450
Income taxes payable 10,251 11,200
Dividends payable 28,000 25,000
Salaries payable 2,250 1,280
Interest payable 188 0
Note payable?current portion 3,000 0
Note payable?long-term portion 4,500 0
Preferred stock, no par, $6 cumulative?3,000 and 2,500 shares
issued, respectively 15,000 12,500
Common stock, $1 par?23,180 shares issued 23,180 23,180
Additional paid in capital?Treasury stock 250 250
Retained earnings 32,802 19,800
Total liabilities and stockholders' equity $123,071 $95,660
COOKIE & COFFEE CREATIONS INC.
Year Ended October 31
Sales $485,625 $462,500
Cost of goods sold 222,694 208,125
Gross profit 262,931 254,375
Depreciation expense 17,850 9,100
Salaries and wages expense 147,979 146,350
Other operating expenses 43,186 42,925
Total operating expenses 209,015 198,375
Income from operations 53,916 56,000
Interest expense 413 0
Loss on sale of computer equipment 2,250 0
Total other expenses 2,663 0
Income before income tax 51,253 56,000
Income tax expense 10,251 11,200
Net income $ 41,002 $ 44,800
Natalie and Curtis are thinking about borrowing an additional $20,000 to buy more kitchen
equipment. The loan would be repaid over a 4-year period. The terms of the loan provide for
equal semi-annual payments of $2,500 on May 1 and November 1 of each year, plus interest of 5% on the outstanding balance.
(a) Calculate the following ratios for 2007 and 2008.
1. Current ratio
2. Debt to total assets
3. Gross profit rate
4. Profit margin
5. Return on assets (Total assets at November 1, 2006, were $33,180.)
6. Return on common stockholders' equity (Total common stockholder's equity at November
1, 2006, was $23,180.)
7. Payout ratio
(b) Prepare a horizontal analysis of the income statement for Cookie & Coffee Creations Inc.
using 2007 as a base year.
(c) Prepare a vertical analysis of the income statement for Cookie & Coffee Creations Inc. for
2008 and 2007.
(d) Comment on your findings from parts (a) to (c).
(e) What impact would borrowing an additional $15,000 to buy more equipment have on each
of the ratios in (a) above, assuming that no changes are expected on the income statement
and balance sheet? Comment on your findings.
(f) What would justify a decision by Cookie & Coffee Creations Inc. to buy the additional
equipment? What alternatives are there instead of bank financing?