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

License

Icon for the Creative Commons Attribution 4.0 International License

Financial Management Copyright © by Dan Thompson is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.

Share This Book