Calculate the projected, or pro forma, Interest Expense for 2004

based on Iteration #4 of the balance sheet. Use the same

assumptions given in the page titled Comment on the Pro Forma

Income Statement for 2004, except that now the required new

bank loan amount is $559.1, not $465

Cartwright Lumber Company Basic Information It's Spring 2004 Company founded in 1994 Partnership by Cartwright & Stark Brothers-in-law Stark sold in 2001 for $105 o Paid off in 2002, in part with a $70 loan, PMT = $7/yr over 10 years Good sales growth and profits, but short on cash Bank debt of $247; max = $250 per client Why do banks impose such limits? New bank willing to provide up to $465 Not all of this amount is \"fresh\" money Cartwright must sever ties with its current bank; i.e., must pay off $247 Is this amount enough to finance Cartwright's growth in 2004? Pro Forma Financial Statements Assumptions for Projecting the 2004 Financial Ratios We need to make assumptions regarding some, but not all, of Cartwright's ratios for 2004, as follows: The company will have 7 days of sales in cash (DSC) Many analysts believe this should be the \"bare bones\" minimum DSC for most well-managed companies The average collection period (ACP) will equal 40 days The average payment period (APP) will equal 10 days Cartwright will pay all invoices in 10 days, rather than in 30 or more days, to obtain the 2% discount for early payment offered by its suppliers o This new early payment policy applies for the last 3 quarters of 2004 Days of sales in inventory (DSI) will equal 70 days In the U.S., the average DSI for non-agricultural firms is around 68 days Fixed asset turnover (FATO) will equal 18 Consistent with the upward trend for this ratio in Cartwright's recent past Further Assumptions and Comments on the Financial Ratios for 2004 We also need to assume the percentage of Sales for several income statement accounts for 2004, as follows: Cost of Goods Sold = 72% Operating Expense - Salary = 21% Salary, Cartwright = 3% Notice that we have only assumed certain ratios for 2004, as these are the only ones we will need to make our projections The ratios that we have not assumed will be calculated AFTER our projections are complete Comments on the Assumed Common-Size Income Statement Accounts for 2004 Notice that we did not assume the interest expense, taxes as a percentage of sales, nor the tax rate for 2004 Interest expense is only indirectly related to sales, but it is directly tied to the firm's debt, through the general relationship Interest = Interest Rate Debt Taxes are very strongly related to earnings before taxes (EBT), and only weakly related to sales The tax schedule in the U.S. is \"progressive,\" so the average tax rate may change substantially when the EBT changes o \"Progressive\" means that the marginal tax rate increases as net income increases Given the above, it may be perilous to project net income based on an historical percentage For most serious projections, it is best to let net income \"float\" rather than to assume it is a certain percentage of sales Pro Forma Income Statement for 2004 Based on the assumed values for the ratios and percentage accounts, we can now project most of Cartwright's pro forma income statement for 2004 For example, Gross Profit is projected to be 28% of Net Sales (NS) plus the 2% Sales Discount (SD) for early payment of the purchases over the remaining 3 quarters of 2004. The detailed calculations are 0.28 $3,600 + 0.02 ($2,671.1 - $600) = $1,008 + $40.2 = $1,048.2 The resulting projections are shown in the next page Pro Forma Income Statement for 2004 2004 2004 Q1 Pro-Forma 718 3,600.0 From case 40.2 2%, 3 quarters 2001 2002 2003 1697 2013 2694 183 1,278 239 1,222 239 1,524 326 1,437 326 2,042 418 1,950 418 660 556 522 418.0 2,671.1 By difference 497.1 DSI = 70 days 2,592.0 72% of sales Gross Profit 475 576 744 196 1,048.2 28% of NS + SD Operating Expense - Salary Salary, Cartwright 350 75 430 85 563 95 153 22 756.0 21% of sales 108.0 3% of sales EBIT Interest Expense 50 13 61 20 86 33 21 10 184.2 50.9 r x Debt EBT Provision for Income Taxes 37 6 41 7 53 9 11 2 Net Income 31 34 44 9 133.3 34.2 T x EBT 99.2 Net Sales Sales Discounts Beginning Inventory + Purchases - Ending Inventory Cost of Goods Sold Comments on the Pro Forma Income Statement for 2004 The Interest expense projection of $50.9 includes the 11% p.a. long-term debt associated with Mr. Stark's departure from the firm, as well as the new 10.5% p.a. bank debt of $465 that Cartwright is requesting. To simplify the calculations, it also assumes the following: The average long-term debt, including its current portion, is $52 in the remaining 9 months of the year (54 + 50)/2 = $52 Cartwright takes all of the $465 new bank loan on April 1, and the loan remains at that level through the rest of 2004 It is a useful exercise to verify the $50.9 interest expense on your own The Provision for Income Taxes projection of $34.2 is obtained by applying the tax schedule in the case (footnote 1) to the EBT Income Tax Calculation for 2004 Earnings before taxes Tax Schedule $133,313 (from pro-forma IS for 2004) From To Marginal Rate Tax $0 $50,000 15% $7,500 $50,000 $75,000 25% $6,250 35% $20,410 $75,000 TOTAL $34,160 Average Tax Rate 25.6% Pro Forma Balance Sheet for 2004 Based on the assumed values for the ratios, as well as on the net income value just obtained from the pro forma income statement, we can now obtain the first iteration of Cartwright's pro forma balance sheet for 2004 For example, the Cash account is assumed to be 7 days of sales at the end of 2004, or $69.04 7 ($3,600/365) = $69.04 o We'll round it to $69.0 for presentation purposes The balance sheet projections are shown in the next page Pro Forma Balance Sheet for 2004 (Iteration #1) ASSETS Cash Accounts receivable, net Inventory Current Assets Property, net Total Assets LIABILITIES Notes payable, bank Notes payable, Stark Notes payable, trade Accounts payable Accrued expenses L-T debt, current Current Liabilities Long-term debt Total Liabilities Net worth Total Liabilities & NW Difference 2004 2004 Q1 Pro-Forma 31 69.0 DSC = 7 days 345 394.5 ACP = 40 days 556 497.1 DSI = 70 days 932 960.7 162 200.0 FATO = 18x 1,094 1,160.7 2001 2002 2003 58 171 239 468 126 594 48 222 326 596 140 736 41 317 418 776 157 933 146 233 247 465.0 256 39 7 535 50 585 348 933 157 243 36 7 690 47 737 357 1,094 73.2 36.0 7.0 581.2 43.0 624.2 447.2 1,071.3 105 124 24 7 260 64 324 270 594 192 30 7 375 57 432 304 736 89.3 APP = 10 days Assume no change Pay down $7K/yr NW '03 + NI '04 Comments on the Pro Forma Balance Sheet for 2004 The balance sheet does not balance!! There is a difference of $89.3 between Total Assets and Total Liabilities This discrepancy simply means that the new bank loan of $465 is insufficient to finance the projected growth of the company in 2004, based on our assumptions This begs the question: What is the loan amount that Cartwright Lumber really needs? To answer it, we will use Notes payable, bank as the \"plug\" account; that is, as the account that forces the balance sheet to balance. In other words, instead of assuming a value for that account, e.g., $465, we'll let it \"float\" Note: which account is used as the \"plug\" may change depending on the situation. Because our goal is to find the loan amount that is actually needed, Notes payable, bank is the natural choice for the \"plug\" account in this case Iterating the Pro-Forma Financial Statements for 2004 As we just saw, the projected balance sheet does not balance so, of course, we must fix this problem. To do so, it is natural to add the $89.3 difference between Total Assets and Total Liabilities to the new $465 bank loan that we used in the initial projection (iteration #1). Thus, the \"plug\" account will equal $554.3 in the revised projection, Iteration #2 Notice, though, that changing the value for Notes payable, bank in the balance sheet will also change the Interest expense in the income statement. This, in turn, will change net income, which will change Net worth as well. Finally, a change in Net worth will modify the required \"plug\" amount. This \"vicious circle\" means that we will have to make several iterations before the balance sheet does, in fact, balance Just be patient... or use Goal Seek in Excel. In this case, we'll patiently iterate manually, as it is valuable to understand the mechanics of this process The new balance sheet projections are shown in the next page Pro Forma Balance Sheet for 2004 (Iteration #2) ASSETS Cash Accounts receivable, net Inventory Current Assets Property, net Total Assets LIABILITIES Notes payable, bank Notes payable, Stark Notes payable, trade Accounts payable Accrued expenses L-T debt, current Current Liabilities Long-term debt Total Liabilities Net worth Total Liabilities & NW Difference 2004 2004 Q1 Pro-Forma 31 69.0 DSC = 7 days 345 394.5 ACP = 40 days 556 497.1 DSI = 70 days 932 960.7 162 200.0 FATO = 18x 1,094 1,160.7 2001 2002 2003 58 171 239 468 126 594 48 222 326 596 140 736 41 317 418 776 157 933 146 233 247 256 39 7 535 50 585 348 933 157 243 36 7 690 47 737 357 1,094 554.3 Plug account 105 124 24 7 260 64 324 270 594 192 30 7 375 57 432 304 736 73.2 36.0 7.0 670.5 43.0 713.5 442.6 1,156.1 4.6 APP = 10 days Assume no change Pay down $7K/yr NW '03 + NI '04 Comments on Iteration #2 of the Pro Forma Financial Statements for 2004 The balance sheet is still unbalanced! However, we have made significant progress, as the difference between Total Assets and Total Liabilities is now only $4.6. We will add this amount to the $554.3 that we just tried; that is, the plug account for Iteration #3 will be $531.4 554.3 + 4.6 = 558.9 We must continue to project the pro forma financial statements in this iterative manner until the balance sheet balances, to the desired degree of accuracy For our purposes, a one-decimal accuracy suffices The necessary iterations are shown in the next two slides Pro Forma Balance Sheet for 2004 (Iteration #3) ASSETS Cash Accounts receivable, net Inventory Current Assets Property, net Total Assets LIABILITIES Notes payable, bank Notes payable, Stark Notes payable, trade Accounts payable Accrued expenses L-T debt, current Current Liabilities Long-term debt Total Liabilities Net worth Total Liabilities & NW Difference 2004 2004 Q1 Pro-Forma 31 69.0 DSC = 7 days 345 394.5 ACP = 40 days 556 497.1 DSI = 70 days 932 960.7 162 200.0 FATO = 18x 1,094 1,160.7 2001 2002 2003 58 171 239 468 126 594 48 222 326 596 140 736 41 317 418 776 157 933 146 233 247 256 39 7 535 50 585 348 933 157 243 36 7 690 47 737 357 1,094 558.9 Plug account 105 124 24 7 260 64 324 270 594 192 30 7 375 57 432 304 736 73.2 36.0 7.0 675.1 43.0 718.1 442.3 1,160.4 0.2 APP = 10 days Assume no change Pay down $7K/yr NW '03 + NI '04 Pro Forma Balance Sheet for 2004 (Iteration #4) ASSETS Cash Accounts receivable, net Inventory Current Assets Property, net Total Assets LIABILITIES Notes payable, bank Notes payable, Stark Notes payable, trade Accounts payable Accrued expenses L-T debt, current Current Liabilities Long-term debt Total Liabilities Net worth Total Liabilities & NW Difference 2004 2004 Q1 Pro-Forma 31 69.0 DSC = 7 days 345 394.5 ACP = 40 days 556 497.1 DSI = 70 days 932 960.7 162 200.0 FATO = 18x 1,094 1,160.7 2001 2002 2003 58 171 239 468 126 594 48 222 326 596 140 736 41 317 418 776 157 933 146 233 247 256 39 7 535 50 585 348 933 157 243 36 7 690 47 737 357 1,094 559.1 Plug account 105 124 24 7 260 64 324 270 594 192 30 7 375 57 432 304 736 73.2 36.0 7.0 675.3 43.0 718.3 442.3 1,160.6 0.0 APP = 10 days Assume no change Pay down $7K/yr NW '03 + NI '04 Comments on Iteration #4 of the Pro Forma Financial Statements for 2004 The balance sheet balances to one-decimal accuracy after the fourth iteration This is typical; it often suffices to iterate just a few times We conclude that the amount of funds needed to finance the company's growth over the rest of the year is $559.1, not the requested amount of $465 However, given Cartwright Lumber's recent history and its projected growth and profitability, the company should have no problem obtaining this increased amount from the bank Note: The assets and liabilities in the pro forma balance sheet still differ by 0.1 due to the combination of rounding and the loose one-decimal accuracy we used. Of course, in an actual balance sheet there can be no difference, down to the penny! Exercise 5 Calculate the projected, or pro forma, Interest Expense for 2004 based on Iteration #4 of the balance sheet. Use the same assumptions given in the page titled Comment on the Pro Forma Income Statement for 2004, except that now the required new bank loan amount is $559.1, not $465 I suggest that you confirm the logic of your calculations by also applying it to replicate the Interest Expense of $50.9 that resulted when the new bank loan was assumed to be $465 I told you it was useful to verify this calculation! When you finish the calculations, you may take the quiz