Need help filling the excel sheets (Exhibit 1, Exhibit 2, Cash flows, and Pro forma.

...

CLARKSON LUMBER After rapid growth in its business during recent years, Clarkson Lumber Co. (CLC) anticipated a further substantial increase in sales. Despite good profits, the company had experienced a shortage of cash and had found it necessary to increase its borrowing from the Suburban National Bank to $399,000 In the spring of 2020. The maximum loan that Suburban National would make to any one borrower was 2020. marca $400,000, and CLC had been able to stay within this limit only by relying heavily on trade credit. In ser addition, Suburban was now asking that BRS secure the loan with its real property, Barton darkson, sole owner and president of CLC, was therefore looking elsewhere for a new barking relationship where he would be able to negotiate a larger and unsecured loan. a Clarkson had recently been introduced by a friend to Craig Doidge, an officer of a much larger bank, the Arizona National Bank. The two men had discussed the possibility that the Arizona National Bank might extend a line of credit to Bes up to a maximum amount of $750,000. Clarkson thought that a loan d a of this size would more than meet his foreseeable needs, but he was eager for the fiexibility that a line of credit of this size would provide. After this discussion, Doicge had arranged for the credit department . , of Arizona National Bank to investigate Barton Clarkson and his company. Clarkson Lumber was founded in 2011 as a partnership by Barton Clarkson and his brother-in-law, Henry Stark. In 2018, Clarkson bought out Stark's interest for $200,000 and incorporated the business. Stark had taken a note for $200,000 in 2018, to be paid off in 2019 and 2020, to give Clarkson time to arrange for the necessary financing. This loan was secured by land and buildings, carried an interest rate of f11%, and was repayable in semi-annual installments of $50,000, beginning June 30, 2019. perna CLC was located in a growing suburb of a large city in the Pacific Northwest. The company owned bed with land with easy access to a railroad, and two large storage buildings had been erected on this land. The company's operations were limited to the retail distribution of lumber products in the local area. Typical oder products included plywood, moldings, and sash and door products. Quantity discounts and credit terms of net 30 days on open account were usually offered to customers. Sales volume had been built up largely on the basis of successful price competition, made possible by careful control of operating expenses and by quantity purchases of materials at substantial discounts. Much of the moldings and sash and door products, which constituted significant items of sales, were used for repair work. About 55% of total sales were made in the six months from April through September. No sales representatives were employed, orders being taken exclusively over the telephone. Annual sales of $1,697,000 in 2017, $2,013,000 in 2018, and $2,694,000 in 2019 yielded after tax profits downturn mieht slow down sales growowerer, Were protected to some degree from 2020 as Clarkson strove to hold his borrowing within the $400,000 ceiling imposed by the Suburban National Bank , , ban a ride porc. dies are expected to because of the relatively high proportion of in the um FORS's goad of $31,000 in 2017, 534,000 in 2018, and $44,000 in 2019.: Operating statements for the years 2017- 2019 and for the three months ending March 31, 2020, are given in Exhibit 1. Barton Clarkson was an energetic 29 year old man, who worked lang hours on the job. He was helped by an assistant who, in the words af Arizana National Bank's credit analyst, "has been doing and , " can do about everything that Clarkson does in the organization. Other employees numbered 15 in early 2020, 8 of whom worked in the yard and drove trucks and 7 of whom assisted in the office and in sales. 7 . As part of its customary investigation of prospective borrowers the Arizona National Bank sent Wednes mes concer.com inquiries concerning Barton Clarkson to a number of firms that had business dealines with him. The manager drone ernstige , The conservative operation of his business appeals to us. He has not wasted his money in . disproportionate plant investment. Mis operating expenses are as low as they could possibly be. He has personal control over every feature of his business, and he possesses sound judgment and a willingness to work harder than anyone I have ever known. This, with a good personality, gives him a good turnover, I a from my personal experience in watching him work, I know that he keeps close check on his own credits from my personal experience in watching him onimnow more cose check The bank gave particular attention to the debt position and current ratio of the business. It noted the The Banana ready market for the company's products at all times and the fact that sales prospects were favorable. The analyst reparted reach $5.5 million in 2021 and may exceed this level il prices of lumber should rise substantially in the near future." On the other hand, a general economic a Projections beyond 2021 were difficult to make, but the prospects appeared good for continued growth volume of business aver the foreseeable future. me walume of ass business over the forestale tuture. The bank also noted the rapid increase in CLC's accounts and notes payable in the recent past, especially in the spring of 2020. The usual terms of purchase in the trade provided for a discount of 2% for payments made within 10 days of the invoice date. Accounts were due in 30 days at the invoice price, but suppliers ordinarily did not object if payments lazeed somewhat behind the due date. Durine the last two years, Clarkson had takeni very few purchase discounts because of the shortage of funds arising from his purchase of Stark's interest in the business and the additional investments in working capital associated with the company's sales growth. Trade credit was seriously extended in the spring of Balance sheets at December 31, 2017-2019, and March 31, 2020, are presented in Exhibit 2. The tentative discussions between Craig Doidge and Barton Carison had been about a revolving. secured 90 day note nat to exceed $750,000. The specific details of the loan had not been worked aut, but Doidge had explained that the agreement would involve the standard covenants applying to such a wear covenants loan. He cited as illustrative provisions the requirement that restrictions on actional borrowing would suscramento de recorrente des consoladora be imposed, that net working capital would have to be maintained at an agreed level, that additional capaculdade to be amanet alleres en el laboral investments in fixed assets could be made only with prior approval of the bank, and that limitations would be pasan emas would be placed on withdrawals of funds from the business by Clarkson. Interest would be set on a floating-rate basis at 2 percentage points above the prime rate the rate paid by the bank's most creditworthy customers). Daidge indicated that the initial rate to be paid would be about 11% under conditions in effect in early 2020. Both men also understood that Clarkson would sever his relationship with the Suburban National Bank if he entered into a loan agreement with the Arizona National Bank. Questions 1. From 2017 to 2019, calculate the changes in balance sheet items plus net income see "cash flows sheet. How much did each item change by? Was this a source or use of cash for the firm? 2. IS CLC financially healthy"? In the excel sheet provided, fill out the gray areas in Exhibits 1 and 2. 1. Assume that total sales in 2020 are $8.5 million, and that any interest paid in 2020 an new or existing debt is based upon the amount of debt al had at the end of 2019. Using the percent of sales method as discussed in class and assuming that all assets and spontaneous liabilities will grow at the same rate as sales, how big of a line of credit does a need from Arizona National Bank in 2020? In doing this, ignore the 1st a 2020 numbers provided. Note: The ultimate question is whether the $750,000 line of credit is enough to get CLC through the year. However, there is no one "right" answer in cases like these, just like there is no one right answer many times in life. Finance is defined by uncertainty Clarkson Lumber was required to estimate its income tax liability for the current tax year and pay four quarterly estimated tax installments during the year, CLC wus taxed at the rate of 15% on its first $50,000 of income, 25% on the next $25,000 af income, and 34% an all additional income between $75,000-100,000. Pratits in excess at $100,000 but less than 335,000 were taxed at a 29% rate. CLARKSON LUMBER Exhibit 1: Operating Expenses for Years Ending December 31, 2017-2019, and for First Quarter 2020 (thousands of dolars) CLARKSON LUMBER Ex 2: Balance Sheets at December 31, 2017-2019, and March 31, 2020 thousands of dolars) 2017 2018 1st Qur 2020 2019 2017 2018 1st Ortr 2020 2019 $1,697 $2,013 $2.594 $718 Net sales Cost of goods sold Beginning inventory Purchases 49 222 325 163 239 328 1,278 1,524 2.042 $1,461 $1,763 $2.368 239 326 418 $1,222 $1,437 $1.950 418 660 S1,078 556 $522 Cash Accounts receivable.net Inventory Current assets Property, net Total assets 58 171 239 468 155 623 41 317 418 776 180 956 31 345 568 932 186 1,118 166 782 Ending inventory Total cost of goods sold 146 233 247 Gross Profit $475 3576 S744 $196 105 Operating expenses Interest expense Net income before income taxes Provision for income taxes Net incorre 425 13 $37 6 $31 515 20 S41 7 S34 658 33 $53 9 9 $44 175 10 $11 2 $9 Notes payable, bank Nales payable, Mr. Stark Noles payable, trade Accounts payable Accrued expenses Long-term debt current portion Current liabilities Long-term debt 124 192 24 30 10 10 S263 $378 90 80 256 39 10 $538 70 157 243 38 10 $693 68 (a) In the first quarter of 2019, sales were $68.000 and net income was $7.000 Total liabilities Net worth (a) Total liabilities & net worth S353 5458 5608 270304 348 5623 $762 $956 $761 357 $1,118 (b) Operating expenses include a cash salary for Mr. Clarkson of $75,000 in 2017, $80,000 in 2018. $85,000 in 2019, and S22,500 in the 1st quarter of 2020. Mr. Clarkson also received some of the perquisites commonly taken by owners of privately held businesses (a) As a privately held company, CLC has never issued or repurchased common stock. Net worth is essentially the companies retained earnings AutoSave OFF HESU: CLC_studenthandout 2021 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share O Comments = Arial v 10 v General ativ E- Conditional Formatting Format as Table v Insert v & Delete v u Y 4 Paste BI U v ar Ar $ %) Ideas Sensitivity Cell Styles v Format v Sort & Filter Find & Select L32 fix D E F G H J K L 1 2 3 4 5 6 A B C CLARKSON LUMBER Exhibit 1: Operating Expenses for Years Ending December 31, 2017-2019, and for First Quarter 2020 (thousands of dollars) Percent of sales 2017 2018 1st Otr 2020 2019 2017 2018 2019 Avg $718 418 660 $1,078 556 $522 $196 8 Net sales $1,697 $2,013 $2,694 9 Cost of goods sold: 10 Beginning inventory 183 239 326 11 Purchases 1,278 1,524 2,042 12 $1,461 $1,763 $2,368 13 Ending inventory 239 326 418 14 Total cost of goods sold $1,222 $1,437 $1,950 15 16 Gross Profit $475 5578 $744 17 18 Operating expenses 425 515 658 19 Interest expense 13 20 33 20 Net income before income taxes $37 $41 $53 21 Provision for income taxes 6 7 9 22 Net income $31 $34 $44 23 24 (a) In the first quarter of 2019, sales were $698,000 and net income was $7,000. 25 26 (b) Operating expenses include a cash salary for Mr. Clarkson of $75,000 in 2017, 27 $85,000 in 2018, $95,000 in 2019, and $22,000 in the 1st quarter of 2020. 28 Mr. Clarkson also received some of the perquisites commonly taken by owners 29 of privately held businesses. 30 31 32 33 34 175 10 S11 2 a Exhibit 1 Exhibit 2 Cash flows Pro forma + E E + 118% AutoSave OFF HESU: CLC_studenthandout 2021 Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share O Comments Arial v 10 v == General ats B Conditional Formatting Format as Table v Cell Styles v Insert v x Delete v EAR-Ou 4 Paste B I U ar Ar $ % ) Ideas Sensitivity Sort & Filter Format v Find & Select 040 fix E F G H H I J K L M M N O 1st Ortr 2020 Percent of salas 2017 2018 2019 Avg 3.4% 2.4% 1.5% 2.5% 31 345 556 932 186 1,118 247 157 243 36 10 $693 68 A B C D 1 CLARKSON LUMBER 2 Ex. 2: Balance Sheets at December 31, 2017-2019, 3 4 5 2017 2018 2019 6 7 Cash 58 49 41 B Accounts recevable, net 171 222 317 9 Inventory 239 325 418 10 Current assets 466 596 776 11 Property, net 155 166 180 12 Total assets 623 762 956 13 14 Notas payable, bank 146 233 15 Notes payable, Mr. Stark 105 16 Notes payable, trade 17 Accounts payable 124 192 256 18 Aoorued expenses 24 30 39 19 Long-term debt, current portion 10 10 10 20 Qument liabilties $263 $378 S539 90 21 Long-term debt 80 70 22 23 Total abilities 3353 $456 S600 24 Net worth 270 304 349 25 Tatal liabilities & net worth 3623 $762 S958 26 27 (a) As a privately held company, CLC has never issued or repurchased comman stock . 28 Net worth is essentially the companies retained earnings. 29 30 31 Ratios 2017 2018 2019 32 Current rulia 33 Current liabiltie/assets 34 Net worth/assets 35 Return on sale 36 Ratum on assets 37 Raturn on equily 38 Collection period (days) 39 Days in Inventory 40 Days in Payables 41 Sales growth 42 Sustainable growth rate 43 44 45 Exhibit 1 Exhibit 2 Cash flows Pro forma + $781 357 $1,118 $ E EL 95% AutoSave OFF JES 5 = CLC_studenthandout 2021 Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share O Comments Arial v 10 ' == ats B General Conditional Formatting {"2900 4 Format as Table v Insert v & Delete v Format v Paste BI U ar Ar $ % Ideas Sensitivity 12 Cell Styles v Sort & Filter Find & Select N32 X fix fx D E F G H 1 J K L M N Source or Use 1st Ortr 2020 Changes 2017-2018 Changes 2018-2019 Total Change 2017-2019 2019 41 317 418 776 180 956 31 345 556 932 186 1,118 233 247 A B C 1 CLARKSON LUMBER 2 Ex. 2: Balance Sheets at December 31, 2017-2019, 3 4 5 2017 2018 6 7 Cash 58 49 8 Accounts receivable, net 171 222 9 Inventory 239 325 10 Current assets 468 596 11 Property, net 155 166 12 Total assets 623 762 13 14 Notes payable, bank 146 15 Notes payable, Mr. Stark 105 16 Notes payable, trade 17 Accounts payable 124 192 18 Accrued expenses 24 30 19 Long-term debt, current portion 10 10 20 Current labilis $283 $378 21 Long-term debt 90 80 22 Note payable, Mr. Stark 23 Total liabilties $353 $458 24 Net worth 270 304 25 Total liabilities & net worth $623 $762 26 27 28 29 30 31 32 33 34 35 256 39 10 $538 70 157 243 36 10 $893 68 $808 348 $956 $761 357 $1,118 Exhibit 1 Exhibit 2 Cash flows Pro forma + + 114% AutoSave OFF HESU CLC_studenthandout 2021 Q Home Insert Draw Page Layout Formulas e Share O Comments Geneva v 10 v Data Review View Tell me = ats General , $ % Conditional Formatting Format as Table Insert v & Delete v Y 4 Tv Paste B I U U Ideas Sensitivity Cell Styles Find & Select Format v Sort & Filter H29 x D E G HI | K M 0 P 1 2 A C CLARKSON LUMBER Pred income strumenter 200 that of dollars! 2019 Assumption 6 Nel salos 2684 Cost of the 8 irro 9 Purces 10 $2.368 11 Ending worry S418 12 Totalcort of goods word SLI 13 Green Profi 5741 14 Orgape SHES 15 Operating Port 596 16 Inforest expense 3 17 For me 18 Existing fachada ! 19 Net ircame where 982 20 Provision for income taxes 99 21 Not income $24 22 23 24 25 CLARKSON LUMBER 26 Projected balance sheet for December 31, 2020 thousands of dollars) 27 PR 2112 29 AM 30 C Ciel 41 31 Acoounts receivablere 317 32 Irway 415 33 Current A 728 34 Pro 10 35 Toata 956 36 37 Liabilities 38 Acours 256 39 AM 30 40 Lourdes, current prin 10 41 Bari no papallo plug 233 42 Current Lic 635 43 Long-term 20 Tus Lisaties BOB 45 Nel wat 346 Total Liabilities plus not worth 980 47 48 (ALERE - Lisbi plus net worth 49 50 2011 Exhibit 1 Exhibit 2 Cash flows Pro forma + W 81% CLARKSON LUMBER After rapid growth in its business during recent years, Clarkson Lumber Co. (CLC) anticipated a further substantial increase in sales. Despite good profits, the company had experienced a shortage of cash and had found it necessary to increase its borrowing from the Suburban National Bank to $399,000 In the spring of 2020. The maximum loan that Suburban National would make to any one borrower was 2020. marca $400,000, and CLC had been able to stay within this limit only by relying heavily on trade credit. In ser addition, Suburban was now asking that BRS secure the loan with its real property, Barton darkson, sole owner and president of CLC, was therefore looking elsewhere for a new barking relationship where he would be able to negotiate a larger and unsecured loan. a Clarkson had recently been introduced by a friend to Craig Doidge, an officer of a much larger bank, the Arizona National Bank. The two men had discussed the possibility that the Arizona National Bank might extend a line of credit to Bes up to a maximum amount of $750,000. Clarkson thought that a loan d a of this size would more than meet his foreseeable needs, but he was eager for the fiexibility that a line of credit of this size would provide. After this discussion, Doicge had arranged for the credit department . , of Arizona National Bank to investigate Barton Clarkson and his company. Clarkson Lumber was founded in 2011 as a partnership by Barton Clarkson and his brother-in-law, Henry Stark. In 2018, Clarkson bought out Stark's interest for $200,000 and incorporated the business. Stark had taken a note for $200,000 in 2018, to be paid off in 2019 and 2020, to give Clarkson time to arrange for the necessary financing. This loan was secured by land and buildings, carried an interest rate of f11%, and was repayable in semi-annual installments of $50,000, beginning June 30, 2019. perna CLC was located in a growing suburb of a large city in the Pacific Northwest. The company owned bed with land with easy access to a railroad, and two large storage buildings had been erected on this land. The company's operations were limited to the retail distribution of lumber products in the local area. Typical oder products included plywood, moldings, and sash and door products. Quantity discounts and credit terms of net 30 days on open account were usually offered to customers. Sales volume had been built up largely on the basis of successful price competition, made possible by careful control of operating expenses and by quantity purchases of materials at substantial discounts. Much of the moldings and sash and door products, which constituted significant items of sales, were used for repair work. About 55% of total sales were made in the six months from April through September. No sales representatives were employed, orders being taken exclusively over the telephone. Annual sales of $1,697,000 in 2017, $2,013,000 in 2018, and $2,694,000 in 2019 yielded after tax profits downturn mieht slow down sales growowerer, Were protected to some degree from 2020 as Clarkson strove to hold his borrowing within the $400,000 ceiling imposed by the Suburban National Bank , , ban a ride porc. dies are expected to because of the relatively high proportion of in the um FORS's goad of $31,000 in 2017, 534,000 in 2018, and $44,000 in 2019.: Operating statements for the years 2017- 2019 and for the three months ending March 31, 2020, are given in Exhibit 1. Barton Clarkson was an energetic 29 year old man, who worked lang hours on the job. He was helped by an assistant who, in the words af Arizana National Bank's credit analyst, "has been doing and , " can do about everything that Clarkson does in the organization. Other employees numbered 15 in early 2020, 8 of whom worked in the yard and drove trucks and 7 of whom assisted in the office and in sales. 7 . As part of its customary investigation of prospective borrowers the Arizona National Bank sent Wednes mes concer.com inquiries concerning Barton Clarkson to a number of firms that had business dealines with him. The manager drone ernstige , The conservative operation of his business appeals to us. He has not wasted his money in . disproportionate plant investment. Mis operating expenses are as low as they could possibly be. He has personal control over every feature of his business, and he possesses sound judgment and a willingness to work harder than anyone I have ever known. This, with a good personality, gives him a good turnover, I a from my personal experience in watching him work, I know that he keeps close check on his own credits from my personal experience in watching him onimnow more cose check The bank gave particular attention to the debt position and current ratio of the business. It noted the The Banana ready market for the company's products at all times and the fact that sales prospects were favorable. The analyst reparted reach $5.5 million in 2021 and may exceed this level il prices of lumber should rise substantially in the near future." On the other hand, a general economic a Projections beyond 2021 were difficult to make, but the prospects appeared good for continued growth volume of business aver the foreseeable future. me walume of ass business over the forestale tuture. The bank also noted the rapid increase in CLC's accounts and notes payable in the recent past, especially in the spring of 2020. The usual terms of purchase in the trade provided for a discount of 2% for payments made within 10 days of the invoice date. Accounts were due in 30 days at the invoice price, but suppliers ordinarily did not object if payments lazeed somewhat behind the due date. Durine the last two years, Clarkson had takeni very few purchase discounts because of the shortage of funds arising from his purchase of Stark's interest in the business and the additional investments in working capital associated with the company's sales growth. Trade credit was seriously extended in the spring of Balance sheets at December 31, 2017-2019, and March 31, 2020, are presented in Exhibit 2. The tentative discussions between Craig Doidge and Barton Carison had been about a revolving. secured 90 day note nat to exceed $750,000. The specific details of the loan had not been worked aut, but Doidge had explained that the agreement would involve the standard covenants applying to such a wear covenants loan. He cited as illustrative provisions the requirement that restrictions on actional borrowing would suscramento de recorrente des consoladora be imposed, that net working capital would have to be maintained at an agreed level, that additional capaculdade to be amanet alleres en el laboral investments in fixed assets could be made only with prior approval of the bank, and that limitations would be pasan emas would be placed on withdrawals of funds from the business by Clarkson. Interest would be set on a floating-rate basis at 2 percentage points above the prime rate the rate paid by the bank's most creditworthy customers). Daidge indicated that the initial rate to be paid would be about 11% under conditions in effect in early 2020. Both men also understood that Clarkson would sever his relationship with the Suburban National Bank if he entered into a loan agreement with the Arizona National Bank. Questions 1. From 2017 to 2019, calculate the changes in balance sheet items plus net income see "cash flows sheet. How much did each item change by? Was this a source or use of cash for the firm? 2. IS CLC financially healthy"? In the excel sheet provided, fill out the gray areas in Exhibits 1 and 2. 1. Assume that total sales in 2020 are $8.5 million, and that any interest paid in 2020 an new or existing debt is based upon the amount of debt al had at the end of 2019. Using the percent of sales method as discussed in class and assuming that all assets and spontaneous liabilities will grow at the same rate as sales, how big of a line of credit does a need from Arizona National Bank in 2020? In doing this, ignore the 1st a 2020 numbers provided. Note: The ultimate question is whether the $750,000 line of credit is enough to get CLC through the year. However, there is no one "right" answer in cases like these, just like there is no one right answer many times in life. Finance is defined by uncertainty Clarkson Lumber was required to estimate its income tax liability for the current tax year and pay four quarterly estimated tax installments during the year, CLC wus taxed at the rate of 15% on its first $50,000 of income, 25% on the next $25,000 af income, and 34% an all additional income between $75,000-100,000. Pratits in excess at $100,000 but less than 335,000 were taxed at a 29% rate. CLARKSON LUMBER Exhibit 1: Operating Expenses for Years Ending December 31, 2017-2019, and for First Quarter 2020 (thousands of dolars) CLARKSON LUMBER Ex 2: Balance Sheets at December 31, 2017-2019, and March 31, 2020 thousands of dolars) 2017 2018 1st Qur 2020 2019 2017 2018 1st Ortr 2020 2019 $1,697 $2,013 $2.594 $718 Net sales Cost of goods sold Beginning inventory Purchases 49 222 325 163 239 328 1,278 1,524 2.042 $1,461 $1,763 $2.368 239 326 418 $1,222 $1,437 $1.950 418 660 S1,078 556 $522 Cash Accounts receivable.net Inventory Current assets Property, net Total assets 58 171 239 468 155 623 41 317 418 776 180 956 31 345 568 932 186 1,118 166 782 Ending inventory Total cost of goods sold 146 233 247 Gross Profit $475 3576 S744 $196 105 Operating expenses Interest expense Net income before income taxes Provision for income taxes Net incorre 425 13 $37 6 $31 515 20 S41 7 S34 658 33 $53 9 9 $44 175 10 $11 2 $9 Notes payable, bank Nales payable, Mr. Stark Noles payable, trade Accounts payable Accrued expenses Long-term debt current portion Current liabilities Long-term debt 124 192 24 30 10 10 S263 $378 90 80 256 39 10 $538 70 157 243 38 10 $693 68 (a) In the first quarter of 2019, sales were $68.000 and net income was $7.000 Total liabilities Net worth (a) Total liabilities & net worth S353 5458 5608 270304 348 5623 $762 $956 $761 357 $1,118 (b) Operating expenses include a cash salary for Mr. Clarkson of $75,000 in 2017, $80,000 in 2018. $85,000 in 2019, and S22,500 in the 1st quarter of 2020. Mr. Clarkson also received some of the perquisites commonly taken by owners of privately held businesses (a) As a privately held company, CLC has never issued or repurchased common stock. Net worth is essentially the companies retained earnings AutoSave OFF HESU: CLC_studenthandout 2021 Q Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share O Comments = Arial v 10 v General ativ E- Conditional Formatting Format as Table v Insert v & Delete v u Y 4 Paste BI U v ar Ar $ %) Ideas Sensitivity Cell Styles v Format v Sort & Filter Find & Select L32 fix D E F G H J K L 1 2 3 4 5 6 A B C CLARKSON LUMBER Exhibit 1: Operating Expenses for Years Ending December 31, 2017-2019, and for First Quarter 2020 (thousands of dollars) Percent of sales 2017 2018 1st Otr 2020 2019 2017 2018 2019 Avg $718 418 660 $1,078 556 $522 $196 8 Net sales $1,697 $2,013 $2,694 9 Cost of goods sold: 10 Beginning inventory 183 239 326 11 Purchases 1,278 1,524 2,042 12 $1,461 $1,763 $2,368 13 Ending inventory 239 326 418 14 Total cost of goods sold $1,222 $1,437 $1,950 15 16 Gross Profit $475 5578 $744 17 18 Operating expenses 425 515 658 19 Interest expense 13 20 33 20 Net income before income taxes $37 $41 $53 21 Provision for income taxes 6 7 9 22 Net income $31 $34 $44 23 24 (a) In the first quarter of 2019, sales were $698,000 and net income was $7,000. 25 26 (b) Operating expenses include a cash salary for Mr. Clarkson of $75,000 in 2017, 27 $85,000 in 2018, $95,000 in 2019, and $22,000 in the 1st quarter of 2020. 28 Mr. Clarkson also received some of the perquisites commonly taken by owners 29 of privately held businesses. 30 31 32 33 34 175 10 S11 2 a Exhibit 1 Exhibit 2 Cash flows Pro forma + E E + 118% AutoSave OFF HESU: CLC_studenthandout 2021 Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share O Comments Arial v 10 v == General ats B Conditional Formatting Format as Table v Cell Styles v Insert v x Delete v EAR-Ou 4 Paste B I U ar Ar $ % ) Ideas Sensitivity Sort & Filter Format v Find & Select 040 fix E F G H H I J K L M M N O 1st Ortr 2020 Percent of salas 2017 2018 2019 Avg 3.4% 2.4% 1.5% 2.5% 31 345 556 932 186 1,118 247 157 243 36 10 $693 68 A B C D 1 CLARKSON LUMBER 2 Ex. 2: Balance Sheets at December 31, 2017-2019, 3 4 5 2017 2018 2019 6 7 Cash 58 49 41 B Accounts recevable, net 171 222 317 9 Inventory 239 325 418 10 Current assets 466 596 776 11 Property, net 155 166 180 12 Total assets 623 762 956 13 14 Notas payable, bank 146 233 15 Notes payable, Mr. Stark 105 16 Notes payable, trade 17 Accounts payable 124 192 256 18 Aoorued expenses 24 30 39 19 Long-term debt, current portion 10 10 10 20 Qument liabilties $263 $378 S539 90 21 Long-term debt 80 70 22 23 Total abilities 3353 $456 S600 24 Net worth 270 304 349 25 Tatal liabilities & net worth 3623 $762 S958 26 27 (a) As a privately held company, CLC has never issued or repurchased comman stock . 28 Net worth is essentially the companies retained earnings. 29 30 31 Ratios 2017 2018 2019 32 Current rulia 33 Current liabiltie/assets 34 Net worth/assets 35 Return on sale 36 Ratum on assets 37 Raturn on equily 38 Collection period (days) 39 Days in Inventory 40 Days in Payables 41 Sales growth 42 Sustainable growth rate 43 44 45 Exhibit 1 Exhibit 2 Cash flows Pro forma + $781 357 $1,118 $ E EL 95% AutoSave OFF JES 5 = CLC_studenthandout 2021 Home Insert Draw Page Layout Formulas Data Review View Tell me Le Share O Comments Arial v 10 ' == ats B General Conditional Formatting {"2900 4 Format as Table v Insert v & Delete v Format v Paste BI U ar Ar $ % Ideas Sensitivity 12 Cell Styles v Sort & Filter Find & Select N32 X fix fx D E F G H 1 J K L M N Source or Use 1st Ortr 2020 Changes 2017-2018 Changes 2018-2019 Total Change 2017-2019 2019 41 317 418 776 180 956 31 345 556 932 186 1,118 233 247 A B C 1 CLARKSON LUMBER 2 Ex. 2: Balance Sheets at December 31, 2017-2019, 3 4 5 2017 2018 6 7 Cash 58 49 8 Accounts receivable, net 171 222 9 Inventory 239 325 10 Current assets 468 596 11 Property, net 155 166 12 Total assets 623 762 13 14 Notes payable, bank 146 15 Notes payable, Mr. Stark 105 16 Notes payable, trade 17 Accounts payable 124 192 18 Accrued expenses 24 30 19 Long-term debt, current portion 10 10 20 Current labilis $283 $378 21 Long-term debt 90 80 22 Note payable, Mr. Stark 23 Total liabilties $353 $458 24 Net worth 270 304 25 Total liabilities & net worth $623 $762 26 27 28 29 30 31 32 33 34 35 256 39 10 $538 70 157 243 36 10 $893 68 $808 348 $956 $761 357 $1,118 Exhibit 1 Exhibit 2 Cash flows Pro forma + + 114% AutoSave OFF HESU CLC_studenthandout 2021 Q Home Insert Draw Page Layout Formulas e Share O Comments Geneva v 10 v Data Review View Tell me = ats General , $ % Conditional Formatting Format as Table Insert v & Delete v Y 4 Tv Paste B I U U Ideas Sensitivity Cell Styles Find & Select Format v Sort & Filter H29 x D E G HI | K M 0 P 1 2 A C CLARKSON LUMBER Pred income strumenter 200 that of dollars! 2019 Assumption 6 Nel salos 2684 Cost of the 8 irro 9 Purces 10 $2.368 11 Ending worry S418 12 Totalcort of goods word SLI 13 Green Profi 5741 14 Orgape SHES 15 Operating Port 596 16 Inforest expense 3 17 For me 18 Existing fachada ! 19 Net ircame where 982 20 Provision for income taxes 99 21 Not income $24 22 23 24 25 CLARKSON LUMBER 26 Projected balance sheet for December 31, 2020 thousands of dollars) 27 PR 2112 29 AM 30 C Ciel 41 31 Acoounts receivablere 317 32 Irway 415 33 Current A 728 34 Pro 10 35 Toata 956 36 37 Liabilities 38 Acours 256 39 AM 30 40 Lourdes, current prin 10 41 Bari no papallo plug 233 42 Current Lic 635 43 Long-term 20 Tus Lisaties BOB 45 Nel wat 346 Total Liabilities plus not worth 980 47 48 (ALERE - Lisbi plus net worth 49 50 2011 Exhibit 1 Exhibit 2 Cash flows Pro forma + W 81%