Dudley Huckle repairs a variety of keyboard instruments from his shop in Belleville, ON. He started out on his own two years ago after buying the business of his mentor, Ludwig Mozart. The business employs 6 piano repair techs, plus Dudley. Currently, they tune and fix pianos in eastern Ontario, but Dudley believes the business can grow if he hires some new employees and opens a new office closer to Toronto. His services are sold to the major music shops in the area and, although he occasionally tunes pianos for individuals, he wants to focus on his B2B business.
Dudley’s accountant has just finished the Current Year (actual) financial statements, so Dudley now has two full years of actual results to compare with each other. Because he knows that it is important to plan and budget for the future, Dudley has also prepared a Plan for next year. This incorporates Dudley’s idea to hire new staff and open a new office. He wants to increase his return on equity, so he is planning to finance this expansion by simply getting another loan from his bank.
Approximately 75% of Dudley’s sales are on credit. 25% are cash sales. 65% of Dudley’s purchases are on credit
At the beginning of the year, Dudley rented much of his equipment and tools. He has since bought out some of Mozart’s assets at auction, and plans to buy more equipment since he feels that renting is really expensive.
Required:
1. Using Excel formulas, complete the missing information, ie the coloured cells, in the attached worksheet (totals, Income Taxes,
% differences between years and plans, etc).
2. Perform a horizontal analysis comparing the Actual amounts for the current year to last year’s results, and also to the Planned amounts for next year. Discuss any items you think are unusual, unrealistic, good, bad, or otherwise interesting
3. Calculate the financial ratios indicated in the coloured cells. Comment on the company’s performance in the areas of
profitabillity, efficiency, liquidity, and leverage. What do you think of Dudley’s plan to expand, and how he intends to finance it?
Marks will be awarded as noted in the yellow cells.
Save your completed Excel file, ensuring your name is included in the file name. Submit the completed Excel file via Blackboard before the end of the day Wednesday March 9.
Hints:
1)
Last year was the first year of operations, so some of last year’s ratios require a slightly different calculation. These have been provided for you.
2)
Cash Flow From Operations is one of the required calculations.
To calculate this number, take Net Income and add back the Depreciation expense, to arrive at Net Income before Depreciation (“NIBD”). Then calculate any increase or decrease in Working Capital from the beginning to the end of the period. (Working Capital for this purpose is Receivables plus Inventory, minus Current Liabilities). An increase in Working Capital should be deducted from NIBD, and a decrease in Working Capital should be added to NIBD, in order to arrive at Cash Flow From Operations.
Worksheet
Student Name Student Number
Dudley Huckle repairs a variety of keyboard instruments from his shop in Belleville, ON. He started out on his own two years ago after buying the business of his mentor, Ludwig Mozart. The business employs 6 piano repair techs, plus Dudley. Currently, they tune and fix pianos in eastern Ontario, but Dudley believes the business can grow if he hires some new employees and opens a new office closer to Toronto. The Actual results from his first two years in operation are shown below, plus Dudley’s plan for next year, which includes an expansion of the business.
Financial
Compare your Actual results to last year’s Actual results, and
Compare this year’s actual results to Next Year’s Plan
As at: Actual results: Next year % Change: GRADE
Date This year Last year Increase or (Decrease) % Plan Next year Plan vs This Yr Actual
Balance sheet
Cash $ 28,138 $ 48,375 $ 73,217
Receivables 127,500 42,000 102,610
Inventory 53,125 43,000 40,625
Total current assets
Long-term Assets
Property, Plant, Equip 370,625 215,625 547,500
Other assets 25,000 18,750 26,250
Total assets
Current liabilities $ 130,201 $ 49,000 $ 180,049
Long term liabilities 265,625 118,750 394,687
Equity 208,562 200,000 215,466
Total liabilities and equity
Income statement
Sales Revenue $ 906,250 $ 859,375 $ 1,208,000
Cost of goods sold 171,875 153,125 237,500
Gross profit (0.5 marks) 0.5 marks 0.5
Operating Expenses:
Salaries 352,375 346,875 469,833
Rent/occupancy 89,375 84,225 115,000
Equipment Rental 18,100 34,375 9,520
Marketing and Sales 42,375 40,625 75,250
Travel 43,125 34,375 61,000
Office expenses 87,500 84,375 98,250
Other operating expenses 15,610 21,875 18,400
Depreciation 62,500 37,500 93,250
Operating Income (EBIT)
Interest/financing fees 13,342 3,750 21,875
Income Before Tax (EBT) (0.5 marks) 0.5 marks 0.5
Income Taxes (=15% of positive EBT)
Net income (0.5 marks) 0.5 marks 0.5
Cash Flow from Operations (0.5 marks) 0.5 marks 0.5
Financial ratios (3 marks) This year Last year Potential Questions Next Year Plan
Gross margin – Are your products properly priced to cover your operating costs?
Operating Margin – Does the business make an adequate return before financing costs and taxes? 1 mark 1
Net margin – Is this an adequate & sustainable amount of profitability?
Return on Equity 15.53% – Are your shareholders receiving an adequate return on their investment in the company?
Return on Assets 8.45% – Are the assets generating a reasonable level of sales? 1 mark 1
Average Collection Period (in days) 11.9 – Are your collecting your revenues in a timely manner?
Average Days in Inventory 51.2 – Is the turnover too high or low?
Current Ratio – Does your business have enough liquidity to cover your short term liabilities?
Quick ratio – Does your business have enough cash to meet any short term liabilities that may be due soon? 1 mark 1
Debt to Equity Ratio – Does the business use debt responsibly to enhance shareholder wealth or does it borrow excessively?
Financial Comment on the results that you see above
Commentary What actions should management prioritize? (5 marks)
Enter your comments after Horizontal analysis here: 1 mark 1
Enter your analysis of profitability ratios here: 1 mark 1
Enter your analysis of efficiency ratios here: 1 mark 1
Enter your analysis of liquidity ratios here: 1 mark 1
Enter your analysis of leverage ratios here: 1 mark 1