For the Clarion Case, how do we handle working capital and R&D for the debt and equity option to have zero cash flows for the first 3 years? year 1,2 & 3.( see attached)

We would also appreciate some critique on our calculation on the attached sheet.

How do we find the IRR for jerry and Iris in both debt and equity scenario?

Max debt (With Bank Debt) Maximum Debt Option (Research and Development Costs Spread over 3 yrs) ('000) Excess Cash 100,000 100 Only $100k can be used - balance c New England Pension Mortgage 800,000 800 80% of Land & Building Cost at 12% Borrow Against Accounts Receivable 240,000 240 Can only borrow what Borrow Against Inventory 80,000 80 Sale & Leaseback of Equipment 200,000 200 Sold at $300k. Max possible ($200k Bank Loan 220,000 220 40,000 40 Jerry & Iris (Equity) Rebel Ventures (Equity) - TOTAL 0 1,680,000 1680 1 2 3 4 1,100 1,280 1,600 5,600 - COGS 330 384 480 1,680 - SG&A 0 Sales 108 120 129 500 - Executive Salaries 40 40 40 200 - R&D - 500 500 - - Depreciation - - - - - Interest on Mortgage 96 95.3 94.5 93.6 - Interest (Accts Rec.,Inv. & Loan) 86 80 74 67 100 100 100 100 - Real Estate Maintenance 40 41 42 44 - Real Estate Property Tax 25 25 25 25 116 2,890 - Lease Payment Taxable Income 275 - Corporate Income Tax (50%) - (105) 58 1,445 275 (190) 58 1,445 + Depreciation - - - - - Principal Repayment (Mortgage + Note) 37 44 51 58 Net Income Page 1 85 Max debt (With Bank Debt) - Investment (NWC) 281 - Investment (Equipment) Cash Flow From Operations 201 236 617 - - - - (43) (435) (229) 770 (43) (435) (229) 770 Terminal Value Cash Flow From Operations Clarion Value at exit $94,897,840 Using FV of Rebel investment @60%IRR FV of Jerry and Iris Investment IRR for Jerry and Iris IRR for clarion FV=PV(1+r)^7 1,027*(1+0.6)^7 $27,568,321.33 $27,568,321.33120 $67,329,519 ??? 193% Amount Received from Rebel as part Equity Year Equity from Rebel V Total amount to be obtained from rebel V 0 320 1 43 Page 2 2 435 3 229 4 Max debt (With Bank Debt) nly $100k can be used - balance can be used as working capital 0% of Land & Building Cost at 12% mortgage an only borrow what old at $300k. Max possible ($200k) due to depreciation recapture (50% tax on capital appreciation) Note: Jerry and Iris were able to raise $1.68M from debt. He needs about $320k in yr0 from the VC tocomplete the purchase 5 6 7 9,600 17,600 27,600 2,880 5,280 8,280 600 700 800 300 400 500 - - - - - - 92.6 91.4 90.2 58 48 48 - - 45 46 48 25 25 25 5,499 11,010 17,809 2,750 5,505 8,904 2,750 5,505 8,904 100 - - - 68 11 12 Page 3 Max debt (With Bank Debt) #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 89,044 #NAME? #NAME? #NAME? $1,027,000 5 6 7 Page 4 Max debt (With Bank Debt) king capital Note Payment on recapture (50% tax on capital appreciation) Real Estate rom debt. He needs about $320k in yr0 from the VC tocomplete the purchase Assumptions Mortgage 25 Yrs Per schedule A 800,000 30% of sales 12% Constant Payment of $102k R&D cost spread over 3yrs No equip, plant, building to depreciate 5yr lease and equipment revert to clarion Page 5 Max debt (With Bank Debt) NWC is obtained using the average ratio of the WC to sales for the all equity play (11.01%) in addition to the $100k cash from the company and the $60 from executive salary +Amortizatio Total 0 Page 6 Max debt (With Bank Debt) ote Payment Yr Interest 1 2 3 4 5 38 32 26 19 10 Principal 31 37 43 50 59 Total 69 69 69 69 69 1 2 3 4 5 6 96 95 94.5 93.6 92.6 91.4 6 7 7.5 8.4 9.4 10.6 102 102 102 102 102 102 Yr Interest Principal Total onstant Payment of $102k Year Contact lens salesMarket size Clarion share Resultant sales Total COGS 1 1100 1000 0% 0 1100 330 2 1280 2500 0% 0 1280 384 3 1600 5000 0% 0 1600 480 4 1600 10000 40% 4000 5600 1680 5 1600 20000 40% 8000 9600 2880 6 1600 40000 40% 16000 17600 5280 7 1600 65000 40% 26000 27600 8280 Real Estate Cash Flows ('000) Page 7 Max debt (With Bank Debt) Year 1 2 3 4 5 6 Rent 165 173 182 191 200 211 Maintenance 40 41 42 44 45 46 Taxes 25 25 25 25 25 25 Net Operating income 100 107 115 122 130 140 Finance payment 102 102 102 102 102 102 5 13 20 28 38 7.5 8.4 9.4 10.6 11.8 61.4 (24.0) 11.5 39.5 49.0 (0.4) (0.2) 37.8 Pretax cash flow (2) 6.0 - Depreciation Taxable income + Tax benefits/ (cost) Aftertax cash flow 6.7 150.0 (146.0) 73.0 71.0 120.0 (108.3) 54.1 59.1 Page 8 96.0 (75.5) 38.2 51.2 76.8 (48.4) 24.2 44.2 Max debt (With Bank Debt) 7 90.2 11.8 102 Page 9 Max debt (With Bank Debt) 7 221 48 25 148 102 46 39.2 18.6 (9.2) 36.8 Page 10 All equity All Equity Option Excess Cash 200,000 Jerry & Iris (Equity) 40,000 Rebel Ventures (Equity) 3,500,000 3,740,000 Income Statement - Clarion ($'000) 0 1 2 3 4 1,100 4,100 7,100 13,100 - COGS 330 930 1,530 2,730 - SG&A 107 500 600 700 - Executive Salaries 100 100 100 200 - - - 350 550 950 - Interest on Mortgage - - - - - Interest (Accts Rec.,Inv. & Loan) - - - - - Lease Payment - - - - - Real Estate Maintenance 40 41 42 44 - Real Estate Property Tax 25 25 25 25 1,954 3,853 7,651 551 1,927 3,826 1,403 1,927 3,826 350 550 950 1,750 - - - 263 600 600 1200 1,000 Sales - R&D 1,000 - Depreciation Taxable income (852) - Corporate Income Tax (50%) - Net Income (852) + Depreciation - Principal Repayment (Mortgage + Note) - Investment (NWC) - Investment (Equipment) Cash Flow From Operations 1,750 - 1,000 2,000 4,000 (1,765) 353 277 376 (1,765) 353 277 376 Terminal Value Cash Flow From Clarion Page 11 All equity Clarion Value at exit $151,808,500 FV of return for Rebel @60% IRR FV=PV(1+r)^7 3.5*(1+0.6)^7= If Rebel had to make $93,952 then Jerry and Iris get IRR for clarion 118% Percentage ownership for Rebel 61.9% Percentage ownership for Jerry and Iris 38.1% IRR for Jeff and Iris ???? $57,856,090 Page 12 $93,952,410 All equity Year $'000) Contact lens Market size 1 1100 1000 2 1100 5000 3 1100 10000 4 1100 20000 5 1100 40000 5 6 7 6 1100 60000 25,100 37,100 49,100 7 1100 80000 5,130 7,530 9,930 800 900 1,000 300 400 500 - - - 3,350 5,550 Investment R&D expended in year 1 8,350 Yr Working Cap - - - NWC - - - Equipment - - - 45 46 48 25 25 25 15,450 22,649 29,247 7,725 11,325 14,624 7,725 11,325 14,624 3,350 5,550 8,350 - - - 2400 2400 2400 Excess cash of $200k available in year 1 if all equity is from Rebel 8,000 12,000 15,000 675 2,475 5,574 146,235 675 2,475 151,809 Page 13 1 2 63 600 -23 537 1000 1000 All equity Clarion shareResultant sal Total COGS 0% 0 1100 330 60% 3000 4100 930 60% 6000 7100 1530 60% 12000 13100 2730 60% 24000 25100 5130 60% 36000 37100 7530 60% 48000 49100 9930 3 4 5 6 7 600 1200 2400 2400 2400 0 -600 -1200 0 0 2000 4000 8000 12000 `5000 Page 14 Book Value 1 2 3 4 5 1 2 3 4 5 6 Land and Building Equipment Inventory Account Receivable Cash 200,000 100,000 200,000 300,000 200,000 1,000,000 Purchase Price R&D Total Needed 2,000,000 1,000,000 3,000,000 New England Pension Trust Michael Grund Georgia Bank Rebel Ventures Bank of Atlanta General Insurance credit Potential fund Valuation 800,000 250,000 320,000 3,500,000 300,000 300,000 3,500,000 Lawrence Suggestion Sell the building to Grund for $1.0M Raise additional $1M from - Excess cash '- Borrow on accounts '- Borrow on inventory ' - Sale / leaseback of eqipm '- note / personal guarantee Total to buy Clarion Drawbacks - Risk - No margin for error - No fund for R&D - Delay entry into market - Reduce share when they enter the mkt - Make mkt smaller - No sufficient cash flow to purchase equipment Adams Suggestion - Fully VC 1,000,000 500,000 500,000 300,000 200,000 2,500,000 1,970,000 1,000,000 100,000 240,000 80,000 300,000 280,000 1,000,000 2,000,000 Raise entire funds with VC Investment in plant & equipment to produce lens - Reduce the COGs to 20% of sales; and - Increase depreciation charges Fixed charges would drop - No rent - No lease payments - no interest payments 3,500,000 20yr old building fully depreciated. Cost represent land only Market valuation for land (250k) and Building (750k) Rapid depreciation to 100k but worth $500k Inventory worths $500k Reflects true value Need only $100k as working capital 80% of Land & Building Cost at 12% mortgage If IRR of 30% Will lend 80% of AR, 40% of book value of Inventory all at 15% Require 60% pretax IRR and 40,000 of managements funds Will lend @ 17% with personall gurantee as security - founders worth 250k each in home e Purchase equipment @ $300k, lease back at $100k per year for 5yrs 250k each in home equity