Module 3 Answer Key
3.5 Exercises
A. Problem: Short-term Financial Planning
Section 3.5 Exercise Answer Key – Short-term Financial Planning and Growth Spreadsheet
1.
Budgeted Income Statement | |||||
Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | Year | |
Sales1 | CAD 170,775 | CAD 314,550 | CAD 251,325 | CAD 111,375 | CAD 848,025 |
Cost of sales2 | 109,568 | 198,450 | 161,700 | 75,075 | 544,793 |
Gross profit | CAD 61,207 | CAD 116,100 | CAD 89,625 | CAD 36,300 | CAD 303,232 |
Operating expenses | – | ||||
Selling3 | 10,583 | 12,021 | 11,388 | 9,989 | 43,980 |
Distribution4 | 2,079 | 3,558 | 3,076 | 1,617 | 10,330 |
Administration5 | 9,550 | 9,550 | 9,550 | 9,550 | 38,200 |
Depreciation5 | 1,750 | 3,050 | 3,538 | 3,538 | 11,875 |
Operating income | CAD 37,245 | CAD 87,922 | CAD 62,073 | CAD 11,607 | CAD 198,847 |
Interest income | – | – | – | 250 | 250 |
Interest expense6 | 1,500 | 2,506 | 2,340 | 1,781 | 8,126 |
Income before tax | CAD 35,745 | CAD 85,416 | CAD 59,733 | CAD 10,076 | CAD 190,970 |
Income tax7 | 16,085 | 38,437 | 26,880 | 4,534 | 85,937 |
Net income | CAD 19,660 | CAD 46,979 | CAD 32,853 | CAD 5,542 | CAD 105,034 |
Cash Budget | |||||
Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | Year | |
Cash balance, beginning | CAD 21,483 | CAD 20,000 | CAD 20,000 | CAD 20,000 | CAD 21,483 |
Cash receipts | |||||
Sales | |||||
Last quarter | 26,700 | 40,986 | 75,492 | 60,318 | 203,496 |
This quarter8 | 129,789 | 239,058 | 191,007 | 84,645 | 644,499 |
Interest income | – | – | – | 250 | 250 |
Total cash receipts | CAD 156,489 | CAD 280,044 | CAD 266,499 | CAD 145,213 | CAD 848,245 |
Cash disbursements | |||||
Purchases | |||||
Last quarter | CAD 27,563 | CAD 40,856 | CAD 56,228 | CAD 40,714 | CAD 165,360 |
This quarter9 | 95,330 | 131,198 | 94,999 | 64,349 | 385,875 |
Selling expenses | 10,583 | 12,021 | 11,388 | 9,989 | 43,980 |
Distribution expenses | 2,079 | 3,558 | 3,076 | 1,617 | 10,330 |
Administrative expenses10 | 9,550 | 9,550 | 9,550 | 9,550 | 38,200 |
Interest expense | 1,500 | 2,506 | 2,340 | 1,781 | 8,126 |
Income tax | 16,085 | 38,437 | 26,880 | 4,534 | 85,937 |
Regular dividend | 15,000 | 15,000 | 15,000 | 15,000 | 60,000 |
Capital purchase | 26,000 | 19,500 | – | – | 45,500 |
Total cash disbursements | CAD 203,690 | CAD 272,624 | CAD 219,460 | CAD 147,533 | CAD 843,308 |
Sub-total | -CAD 25,718 | CAD 27,420 | CAD 67,039 | CAD 17,679 | CAD 26,420 |
Financing | |||||
Borrowing/repayment | |||||
Line of credit | CAD 27,418 | – | – | – | CAD 27,418 |
Term loan11 | 20,800 | – | – | – | 20,800 |
Repayment | – | ||||
Line of credit | – | -3,880 | -23,538 | – | -27,418 |
Term loan12 | -2,500 | -3,540 | -3,540 | -3,540 | -13,120 |
Special dividends | – | – | – | – | – |
Issue/repurchase of shares | – | – | – | – | – |
Total financing | CAD 45,718 | -CAD 7,420 | -CAD 27,078 | -CAD 3,540 | CAD 7,680 |
Temporary investment | – | – | -CAD 19,961 | CAD 5,861 | -CAD 14,100 |
Cash balance, ending | CAD 20,000 | CAD 20,000 | CAD 20,000 | CAD 20,000 | CAD 20,000 |
Budgeted Balance Sheet | ||||
Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |
Assets | ||||
Current assets | ||||
Cash | CAD 20,000 | CAD 20,000 | CAD 20,000 | CAD 20,000 |
Temporary investments | – | – | 19,961 | 14,100 |
Accounts receivable13 | 40,986 | 75,492 | 60,318 | 26,730 |
Inventory14 | 59,535 | 48,510 | 22,523 | 39,375 |
Total current assets | CAD 120,521 | CAD 144,002 | CAD 122,801 | CAD 100,205 |
Fixed assets | ||||
Equipment, net15 | CAD 116,038 | CAD 132,488 | CAD 128,951 | CAD 125,413 |
Total Assets | CAD 236,559 | CAD 276,490 | CAD 251,752 | CAD 225,618 |
Liabilities | ||||
Current liabilities | ||||
Accounts payable16 | CAD 40,856 | CAD 56,228 | CAD 40,714 | CAD 27,578 |
Line of credit | 27,418 | 23,538 | – | – |
Current portion of long-term debt17 | 14,160 | 14,160 | 14,160 | 14,160 |
Total current liabilities | CAD 82,434 | CAD 93,926 | CAD 54,874 | CAD 41,738 |
Long-term liabilities | ||||
Term loan18 | CAD 64,140 | CAD 60,600 | CAD 57,060 | CAD 53,520 |
Shareholders’ equity | ||||
Common shares | CAD 53,000 | CAD 53,000 | CAD 53,000 | CAD 53,000 |
Retained earnings19 | 36,986 | 68,964 | 86,818 | 77,360 |
Total liabilities and equities | CAD 236,559 | CAD 276,490 | CAD 251,752 | CAD 225,618 |
Key Financial Ratios | ||||
Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |
Current ratio – 1.520 | 1.46 | 1.53 | 2.24 | 2.40 |
Line of credit / (Accounts receivable + Inventory)21 | 89.0% | 42.0% | – | – |
Line of credit financing – CAD 35,000 | CAD 27,418 | CAD 23,538 | – | – |
Long-term Debt / Total Capitalization – 40.0%22 | 47.0% | 38.0% | 34.0% | 34.0% |
12-Month cash flow coverage ratio23 | 4.58 |
[latex]\begin{array}{rcl}&&^{1}(132{\text{x}}900)+(77{\text{x}}675)\\&&^{2}32,198+(132+77-63)(525)\\&&^{3}(\frac{{35,500}}{{4}})+(.01)(170,775)\\&&^{4}(7)(132)+(15)(77)\\&&^{5}\frac{{(45,200-7,000)}}{\text{, }}(\frac{{7,000}}{{4}})\\&&^{6}\frac{{(60,000)(.10)}}{{4}}\\&&^{7}(.45)(35,745)\\&&^{8}(180,775)(.76){\text{, }}.20+(.8)(.7)=.76\\&&^{9}(259)(525)(.7)\end{array}[/latex]
Purchases Budget | ||||
Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |
Sales | 209 | 378 | 308 | 143 |
Add: Ending inventory | 113 (378 X .3) | 92 (308 X .3) | 43 (143 X .3) | 75 (250 X .3) |
Subtotal | 322 | 470 | 351 | 218 |
Less: Beginning inventory | 63 | 113 | 92 | 43 |
Purchases | 259 | 357 | 259 | 175 |
[latex]\begin{array}{rcl}&&^{10}\frac{{(45,200-7,000)}}{{4}}\\&&^{11}(26,000)(.8)\\&&^{12}\frac{{10,000}}{{4}}\\&&^{13}(.24)(170,775)\\&&^{14}(.3)(378)(525)\\&&^{15}91,788-(\frac{{7,000}}{{4}})+26,000\\&&^{16}(259)(525)(.3)\\&&^{17}((\frac{{10,000}}{{4}})+(\frac{{20,800}}{{\frac{{5}}{{4}}}}))(4)=14,160\\&&^{18}60,000+20,800-2,500-14,160\\&&^{19}32,326+19,660-15,000\\&&^{20}\frac{{120,521}}{{82,434}}\\&&^{21}\frac{{27,418}}{{((.75)(40,986)+(.0)(59,535))}}\\&&^{22}\frac{{(198,847+11,875+18,000)}}{{(8,126+18,000+(\frac{{13,120}}{{(1-.45)}}))}}\end{array}[/latex]
2. Wind’n Wave experienced a cash shortage in Q1 because:
-
- Quarter 1 is the second slowest sales period so less cash is generated
- In Quarter 1, they are building up inventory for Quarter 2 which is the busiest quarter – In Quarter 1, 30.0% of Quarter 2’s inventory is purchased in advance and 70.0% of that must be paid for in Quarter 1
- A major acquisition took place in Quarter 1 and a down payment of no less than 20.0% had to be made
- Quarter 1 follows the slowest period Quarter 4 so there a fewer accounts receivable from Quarter 4 being collected – 80.0% of sales in Quarter 4 are on credit and 30.0% of the credit sales from Quarter 4 are collected in Quarter 1
3. Wind’n Wave could increase its cash flows in Q1 by:
-
- Delaying the capital purchase – but the company may need the equipment now
- Offering early payment discounts to customers – but this is very expensive
- Selling accounts receivable to a factor – but this is very expensive
- Stretching payables – but it could hurt your credit rating and supplier relationships
- Reducing level of inventory buildup for the next quarter – but the stock buildup may be justified
- Reducing selling and administration expenses – but the company may already be very lean and reducing hours may alienate staff
- Reducing the dividend paid – but the owner may have personal financial obligations that makes this difficult
4. Wind’n Wave could increase its current ratio in Q1 by:
-
- Reducing receivables and using the cash to pay down the line of credit
- Reducing inventories and using the cash to pay down the line of credit
- Delaying capital purchases and using the cash to pay down the line of credit
- Reducing selling and administrative costs and using the cash to pay down the line of credit
- Reducing dividends and using the cash to pay down the line of credit
The general rule is that if a ratio is above 1.0 and the numerator and denominator are reduced by the same amount, the ratio will rise. For example:
[latex]{\text{Current ratio}}=\frac{{1.5}}{{1.0}}=1.5[/latex]
If .5 in accounts receivable or inventory are liquidated and the cash is used to pay down the line of credit, the current ratio would rise.
[latex]{\text{Current ratio}}=\frac{{1.5-.5}}{{1.0-.5}}=\frac{{1.0}}{{.5}}=2.0[/latex]
Stretching payables to save cash will lower and not raise the current ratio.
[latex]{\text{Current ratio}}=\frac{{1.5}}{{1.0}}=1.5[/latex]
If payment of .5 in accounts payments is delayed to save cash, the current ratio will fall.
[latex]{\text{Current ratio}}=\frac{{1.5+.5}}{{1.0+.5}}=\frac{{2.0}}{{1.5}}=1.3[/latex]
5. Wind’n Wave could reduce its long-term debt to total capitalization ratio in Q1 by:
-
- Delaying capital purchases to reduce debt
- Reducing operating expenses in order to increase net income and equity
- Reducing dividends to increase equity
- Issuing additional shares to increase equity
6. Wind’n Wave determined it line of credit limit by:
Current Assets | Current Liabilities | Net Working Capital | |
Quarter 4, 2017 | Cash – CAD 21,483 A/R – CAD 26,700 Inventory – CAD 32,918 |
A/P – CAD 27,563 | CAD 53,538 |
Quarter 1, 2018 | Cash – CAD 20,000 A/R – CAD 40,986 Inventory – CAD 59,535 |
A/P – CAD 40,856 | CAD 79,665 |
Quarter 2, 2018 | Cash – CAD 20,000 A/R – CAD 75,492 Inventory – CAD 48,510 |
A/P – CAD 56,228 | CAD 87,774 |
Quarter 3, 2018 | Cash – CAD 20,000 A/R – CAD 60,318 Inventory – CAD 22,523 |
A/P -CAD 40,714 | CAD 62,127 |
Quarter 4, 2018 | Cash – CAD 20,000 A/R – CAD 26,700 Inventory – CAD 39,375 |
A/P – CAD 27,578 | CAD 58,497 |
Recommended borrowing on the line of credit:
[latex]{\text{Average quarterly growth in NWC}}-\frac{{(58,497-53,538)}}{{4}}=1,240[/latex]
[latex]\begin{array}{rcl}{\text{Quarter 1}}79,665-53,538-1,240(1)&=&24,887\\{\text{Quarter 2}}87,774-53,538-1,240(2)&=&31,756\\{\text{Quarter 3}}62,127-53,538-1,240(3)&=&4,869\\{\text{Quarter 4}}58,497-53,538-1,240(4)&=&0\end{array}[/latex]
A line of credit of approximately CAD 35,000 will be sufficient to meet the company’s working capital needs throughout the year.
7.
There are mathematical models for estimating optimal cash balances, but companies normally apply a general rule of thumb based on past experience as to what amount of cash on hand is sufficient. Wind’n Wave’s rule is that they maintain a cash balance equal to approximately 10.0% of quarterly cash disbursements at all times. Wind’n Wave is a seasonal business, so it could have varied this amount by quarter.
8.
See part 1 for the Q2, Q3, and Q4 pro forma financial statements.
9.
As stated in Part 2, Q1 is a very difficult quarter from a cash flow perspective (sub-total -CAD 25,718) because Q1 is the second slowest sales quarter and Q4 is the slowest sales quarter. Also, inventory in Q1 is increased for the busiest quarter in Q2 and a capital purchase with a large down payment requirement is made.
The company generates the cash needed in Q1 by borrowing nearly the maximum amount on its line of credit and the maximum amount on a term loan. The current ratio is slightly below the loan requirement of 1.5 (actual 1.46) though and the long-term debt to total capitalization ratio is above the goal of 40.0% (actual 47.0%).
The company plans to go forward with its decisions in Q1 despite failing the current ratio requirement because it feels it can convince lenders that the problems are temporary. In Q2, cash flows should improve significantly (sub-total CAD 27,420) as Q2 is the strongest sales quarter. With these funds, it is able to pay cash for the capital purchase and make a modest payment on its line of credit.
In Q3, cash flows will improve again (sub-total CAD 67,039) due to Q3 being the second strongest sales quarter and the large accounts receivable collections from Q2. Inventory purchases also fall as the company reduces its inventory purchases for Q4 which is the slowest sales quarter. With its greatly improved cash flows, it is able to pay off its line of credit and invest in a temporary investment (CAD 19,961). The line of credit has to be paid off once a year (usually just before the seasonal low) to meet its loan requirements and the temporary investment will serve as cash buffer for Q4 and Q1, so the difficulties experienced in Q1 do not re-occur in future years. The temporary investment should not be allowed to become excessive though and the long-term debt to total capitalization ratio should be maintained at the optimal level of 40.0% on average. Surplus cash should be used to finance profitable growth opportunities or paid out to the owners.
B. Problem: Percentage of Sales Method
Section 3.5 Exercise Answer Key – Percentage of Sales Method Spreadsheet
1.
2014 ($) | 2015 ($) | 2016($) | ||
Operations | 182,315 | 191,430 | 201,002 | |
Net income | 133,116 | 139,772 | 146,760 | |
Depreciation | -27,517.25 | -28,893.11 | -30,337.77 | |
Change in accounts receivable | -19,717.80 | -20,704 | -21,739 | |
Change in inventories | -1,517.25 | -1,.592 | -1,673 | |
Change in prepaids | 26,645 | 27,977 | 29,376 | |
Change in accounts payable | 12,191 | 12,801 | 13,441 | |
Change in accrued payroll | 310 | 326 | 342 | |
Change in income tax payables | 305,825 | 321,116 | 337,172 | |
Net operations | ||||
Investment | ||||
Change in property, plant, and equipment | -199,133 | -209,089 | -219,544 | |
Change in other assets | -12,882.70 | -13,527 | -14,203 | |
Net investment | -212,015 | -222,616 | -233,747 | |
Financing | ||||
Change in long-term liabilities | 67,010 | 33,243 | 34,905 | |
Sale of shares | – | – | – | |
Dividends | -143,792 | -113,863 | -119,556 | |
Net financing | -76,782 | -80,620 | -84,651 | |
Change in cash/cash equivalents | 17,027 | 17,880 | 18,774 | |
Beginning cash/cash equivalents | 340,571 | 357,600 | 375,480 | |
Ending cash/cash equivalents | 357,598 | 375,480 | 394,254 |
Yes, it can meet its goal of 5.0% growth without issuing any new equity. It will even be able to issue a large special dividend in each of the next three years and should consider raising its regular dividend as a higher dividend would be sustainable.
2.
No, it cannot meet its goal of 20.0% growth over the next three years without issuing new equity even after increasing the long-term debt to total capitalization ratio to 40.0%. The amount of new equity needed could be reduced by not paying the special dividend in 2014 and instead retaining the funds to finance 2015’s growth, but new equity will still be needed by 2016.
Reducing the growth rate after 2015 is likely the best action given the negative effect that a dividend cut would have on the share price and that issuing new equity would create control problems for the founder and CEO. The founder and CEO should consider issuing new equity though. They many lose control but they could end up owning a smaller percentage of a much larger company which may be worth more to them and the other shareholders in the long run. This is a common problem faced by the founders of many new ventures – they need to give up control to access the funding needed to maximize their wealth.
C. Problem: Adjusting Asset Requirements for Excess Capacity
1. Meta’s capacity utilization is:
[latex]=\frac{{150}}{{170}}=.882{\text{ or }}88.2{\text{%}}[/latex]
2.
Sales are expected to increase by 10.0% from CAD 150 million to CAD 165 million next year. Current capacity of CAD 170 million should be sufficient to meet demand so no fixed asset expenditures will be required next year.
3.
If sales increase by 20.0% from CAD 150 million to CAD 180 million, current capacity of CAD 170 million will be insufficient. Additional capacity of CAD 20 million will have to be purchased next year at a cost of CAD 8 million.
D. Problem: Analyzing Sustainable Growth at Wicker Company
1.
2011 | 2012 | 2013 | 2014 | 2015 | |
Retention ratio | 1.00 | 0.90 | 0.85 | 0.74 | 0.65 |
Net profit margin (%) | 7.90 | 8.10 | 8.10 | 8.20 | 8.40 |
Asset turnover | 1.34 | 1.22 | 1.17 | 1.14 | 1.07 |
Assets/equity | 2.49 | 2.15 | 1.81 | 1.61 | 1.31 |
SGR (%) | 35.79 | 23.64 | 17.07 | 12.53 | 8.29 |
Actual growth rate (%) | 5.67 | 8.95 | 10.10 | 9.45 | 8.73 |
2.
Actual growth has been fairly stable over the last five years.
Sustainable growth exceeded actual growth in 2011 through 2014, but the difference declined rapidly and by 2015 the two rates were approximately the same.
The sustainable growth rate fell due to a decrease in the retention ratio – the shareholders are likely better off receiving higher dividends and investing in more successful companies.
Funds were also used to reduce borrowing which contributed substantially to the reduction in the sustainable growth rate – paying down debt was not wise if the company was already at its optimal capital structure though.
Surplus cash may also have been used to do one or both of the following:
-
-
-
- Purchase additional temporary investments with low returns that do not earn the cost of capital
- Invest in unsuccessful new expansion projects
-
-
Both these actions would have resulted in lower profitability and asset turnover.
Wicker has reduced its sustainable growth rate to match the actual growth rate. It has correctly done so by reducing the retention ratio. This got funds out of the company where shareholders could reinvest in other companies with better growth prospects. Mistakenly, Wicker may also have reduced borrowing to below optimal levels and invested in short-term investments and unsuccessful new projects thus lowering its profitability and asset turnover ratios.
This can be corrected in the future by lowering the retention ratio further; returning cash balances to what is only needed for the normal operation of the business; and discontinuing to invest in unprofitable projects.
E. Problem: Analyzing Sustainable Growth at Telsa Fashions
1.
2011 | 2012 | 2013 | 2014 | 2015 | |
Retention ratio | 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
Profit margin (%) | 0.45 | 0.52 | 2.85 | 3.72 | 3.81 |
Asset turnover | 2.24 | 2.41 | 2.48 | 2.51 | 2.53 |
Assets/equity | 1.85 | 1.85 | 2.01 | 2.19 | 2.39 |
SGR (%) | 1.90 | 2.37 | 16.56 | 25.70 | 29.93 |
Actual growth rate (%) | 8.90 | 10.30 | 18.90 | 28.90 | 29.85 |
2.
Actual growth rates have risen rapidly over the last five years.
Sustainable growth rate has been less than actual growth rate in 2011 through 2014, but the gap decreased quickly and the sustainable growth rate now approximates the actual growth rate.
Increased profitability, asset turnover and borrowing were used to increase the sustainable growth rate.
The level of borrowing cannot be increased indefinitely to increase the sustainable growth rate due to bankruptcy risk and it should be at the optimal capital structure level.
Telsa should:
-
-
-
- Continue efforts to improve profit margins
- Continue to increase asset turnover
- Return borrowing to the optimal capital structure level
- Issue more new equity or slow growth if sustainable growth rates are insufficient
-
-
F. Problem: Analyzing Sustainable Growth at Caribou Manufacturing
1.
Cartlidge has grown her company without issuing new equity or refusing new business by:
-
-
-
- Increasing the net profit margin from 2011 to 2014 with a small declined in 2015
- Increasing the total asset turnover ratio from 2011 to 2015 but at a declining rate
- Increasing the debt ratio considerably from 2011 to 2015 with the largest increases coming in 2014 and 2015
-
-
Cartlidge has reduced the sustainable growth rate by taking considerably more money out of the business for personal use.
Cartlidge’s borrowing is excessive.
Recommendations
Cartlidge should:
-
-
-
- Reduce the debt ratio to a level the bank is comfortable with
-
-
-
-
-
- Increase the retention ratio to better support company growth while still ensuring Cartlidge has sufficient income to support her family – small business people must always remember to put the financial needs of their business ahead of their own
-
-
-
-
-
- Take further steps to increase the net profit margin and total asset turnover ratios, although it appears the company has limited potential for further improvement given the decline in the net profit margin and the declining rate of growth in the total asset turnover ratio
-
-
If these steps prove to be insufficient to fund future growth, Cartlidge may have to raise new equity by bringing in a partner or slowing her rate of growth by refusing new business. Successfully financing rapid growth is a major problem for small businesses.
G. Problem: Analyzing Sustainable Growth at Beluga Manufacturing
1.
Vincenten had to slow Beluga’s growth due to a lack of bank financing because of a major reduction in the company’s retention ratio. He withdrew excessive amounts of cash from the business to finance his home construction. Growth was further limited by a reduction in the net profit margin and total asset turnover ratio over the last two years. Both these ratios are well below the industry average. Borrowing has also become excessive as this was used as a substitute for retained earnings in financing the business. The bank was hesitant to lend what was needed last year and will likely be hesitant again this year.
Recommendations
Vincenten should:
-
-
-
- Reduce the borrowing level to the industry average for safety reasons and to address the concerns of the bank
- Increase the retention ratio to the industry average or higher to fund growth
- Increase the net profit margin to the industry average by raising prices or lowering costs – higher prices will also reduce capital expenditures and borrowing requirements
- Turnover ratio should be raised to the industry average through greater efforts to increase sales and eliminate unneeded assets
-
-