Ideko

.xlsx

School

University of Colorado, Boulder *

*We aren’t endorsed by this school

Course

FNCE 3010

Subject

Finance

Date

Feb 20, 2024

Type

xlsx

Pages

15

Uploaded by BrigadierRain12845

year 2005 2006 2007 0 1 2 Sales Data units growth/year total market growth 5.00% 5.00% 5.00% market size (000 units) 10,000 10,500 11,025 market share growth 1.00% 1.00% 1.00% market share 10% 11.00% 12.00% sales price inflation 2.00% 2.00% 2.00% average sales price ($/unit) $ 75.00 $ 76.50 $ 78.03 Cost of Goods Sold Data raw materials cost inflation 1.00% 1.00% 1.00% raw materials ($/unit) $ 16.00 $ 16.16 $ 16.32 direct labor cost inflation 4.00% 4.00% 4.00% direct labor costs ($/unit) $ 18.00 $ 18.72 $ 19.47 Operating Expense and Tax Data sales and marketing (% Sales) 15.0% 16.5% 18.0% administrative (% Sales) 18.0% 15.0% 15.0% tax rate 35.0% 35.0% 35.0% Production Volume (000 units) market size 10,000 10,500 11,025 market share 10% 11% 12% production volume 1,000 1,155 1,323 Capital Investments ($ 000) 2005 2006 2007 opening book value $ 50,000 $ 49,500 $ 49,050 capital expenditure $ 5,000 $ 5,000 $ 5,000 depreciation $ (5,500) $ (5,450) $ (5,405) closing book value $ 49,500 $ 49,050 $ 48,645 Debt and Interest Expense 2005 2006 2007 Outstanding Debt $ 100,000 $ 100,000 $ 100,000 Interest rate 6.80% 6.80% 6.80% 6.80% Interest Expense $ 6,800 $ 6,800 Pro Forma Income Statement Sales $ 75,000 $ 88,358 $ 103,234 Cost of Goods Sold Raw Materials cost $ 16,000 $ 18,665 $ 21,593 Direct labor costs $ 18,000 $ 21,622 $ 25,757 Gross Profit $ 41,000 $ 48,071 $ 55,883 Sales and Marketing Expenses $ 11,250 $ 14,579 $ 18,582 Administrative Expenses $ 13,500 $ 13,254 $ 15,485 EBITDA $ 16,250 $ 20,238 $ 21,816 Depreciation Expense $ 5,500 $ 5,450 $ 5,405
EBIT $ 10,750 $ 14,788 $ 16,411 Interest Expense (net) $ - $ 6,800 $ 6,800 Pre-Tax Income $ 10,750 $ 7,988 $ 9,611 Income Tax $ 3,763 $ 2,796 $ 3,364 Net Income $ 6,988 $ 5,193 $ 6,247 Statement of Cash Flows 2006 2007 Net Income $ 5,193 $ 6,247 Depreciation $ 5,450 $ 5,405 Changes in Working Capital Accounts Receivable $ (3,969) $ 2,445 Inventory $ 337 $ 1,112 Accounts Payable $ 878 $ 1,116 Cash From Operating Activities $ 15,152 $ 9,211 Net Capital Expenditures $ 5,000 $ 5,000 Other Investment Cash from Investing Activities $ (5,000) $ (5,000) Debt Issuance $ - $ - Dividends $ 9,054 $ 2,989 Sale of Stock 0 0 Cash from Financing Activities $ (9,054) $ (2,989) Change in Cash $ 1,098 $ 1,223 Discount Rates cost of unlevered equity 10.00% Cost of Debt Capital 6.80% Target D/V 40.00% WACC 9.05% terminal growth rate 4.50%
2008 2009 2010 3 4 5 Net Working Capital Assumptions 5.00% 5.00% 5.00% Accounts Receivable 11,576 12,155 12,763 Raw Materials Inventory 1.00% 1.00% 1.00% Finished Goods Inventory 13.00% 14.00% 15.00% Required Cash 2.00% 2.00% 2.00% Wages Payable $ 79.59 $ 81.18 $ 82.81 Other Accounts Payable 1.00% 1.00% 1.00% Working Capital Requirements $ 16.48 $ 16.65 $ 16.82 Accounts Receivable 4.00% 4.00% 4.00% Raw Materials Inventory $ 20.25 $ 21.06 $ 21.90 Finished Goods Inventory Required Cash Balance 19.5% 20.0% 20.0% Wages Payable 14.0% 13.0% 13.0% Other Accounts Payable 35.0% 35.0% 35.0% Net Working Capital Change in Net Working Capital 11,576 12,155 12,763 13% 14% 15% Sources and Uses of Funds for Acquisi 1,505 1,702 1,914 2008 2009 2010 $ 48,645 $ 61,780 $ 69,102 $ 20,000 $ 15,000 $ 8,000 $ (6,865) $ (7,678) $ (7,710) $ 61,780 $ 69,102 $ 69,392 2008 2009 2010 $ 115,000 $ 120,000 $ 120,000 6.80% 6.80% 6.80% $ 6,800 $ 7,820 $ 8,160 Pro Forma Balance Sheet $ 119,777 $ 138,149 $ 158,526 Assets Cash $ 24,808 $ 28,333 $ 32,193 Accounts Receivable $ 30,471 $ 35,834 $ 41,925 Inventories $ 64,498 $ 73,982 $ 84,407 Total Current Assets $ 23,356 $ 27,630 $ 31,705 Net PP&E $ 16,769 $ 17,959 $ 20,608 Goodwill $ 24,373 $ 28,393 $ 32,094 Total Assets $ 6,865 $ 7,678 $ 7,710
$ 17,508 $ 20,715 $ 24,384 Liabilities $ 6,800 $ 7,820 $ 8,160 Accounts Payable $ 10,708 $ 12,895 $ 16,224 Debt $ 3,748 $ 4,513 $ 5,678 Total Liabilities $ 6,960 $ 8,382 $ 10,545 Stockholders' Equity Beginning Equity Net Income Dividends 2008 2009 2010 Net Sale of Stock $ 6,960 $ 8,382 $ 10,545 ## Stockholders' Equity $ 6,865 $ 7,678 $ 7,710 ## Total Liabilities and Equity $ 2,719 $ 3,020 $ 3,350 ## $ 1,242 $ 1,385 $ 1,544 ## $ 1,231 $ 1,231 $ 1,338 ## Free Cash Flow ($000) $ 11,095 $ 12,885 $ 14,699 ## Net Income Plus After-Tax Interest $ 20,000 $ 15,000 $ 8,000 Unlevered Net Income Plus Depreciation $ (20,000) $ (15,000) $ (8,000) Less Change in NWC Less Net Cap Ex $ 15,000 $ 5,000 $ - Free Cash Flow $ 4,736 $ 1,375 $ 5,024 Plus Net Borrowing 0 0 0 Less After-Tax Interest $ 10,264 $ 3,625 $ (5,024) Free Cash Flow to Equity $ 1,360 $ 1,510 $ 1,675 Valuation Free Cash Flow Terminal Value at rWACC discount factor at rU Present value of FCF at rU Tax shield annually discount factor at rD Present value of tax shields Total present value Debt Equity Value NPV
year 2005 2006 2007 2008 0 1 2 3 days sales 90 60 60 60 Raw Materials Expense 45 30 30 30 COGS 45 45 45 45 days sales 30 30 30 30 Direct Labor and Admin 15 15 15 15 Raw Mat and Sales/MKT 45 45 45 45 $ 18,493 $ 14,525 $ 16,970 $ 19,689 $ 1,973 $ 1,534 $ 1,775 $ 2,039 $ 4,192 $ 4,967 $ 5,838 $ 6,815 $ 6,164 $ 7,262 $ 8,485 $ 9,845 $ 1,295 $ 1,433 $ 1,695 $ 1,941 $ 3,360 $ 4,099 $ 4,953 $ 5,938 $ 26,168 $ 22,756 $ 26,419 $ 30,509 $ (3,412) $ 3,663 $ 4,089 ition Sources Uses New Loan $ 100,000 Purchase Equity $ 150,000 Excess Cash 2005 $ 6,500 Repay Debt $ 4,500 KKP Equity Investment $ 53,000 Advisory Fees $ 5,000 Total Sources $ 159,500 Total Uses $ 159,500 $ 6,164 $ 7,262 $ 8,485 $ 9,845 $ 18,493 $ 14,525 $ 16,970 $ 19,689 $ 6,164 $ 6,501 $ 7,613 $ 8,854 $ 30,822 $ 28,288 $ 33,067 $ 38,388 $ 49,500 $ 49,050 $ 48,645 $ 61,780
$ 1,295 $ 1,433 $ 1,695 $ 1,941 $ 100,000 $ 100,000 $ 100,000 $ 115,000 $ 101,295 $ 101,433 $ 101,695 $ 116,941 $ 48,000 $ 53,193 $ 59,440 $ (5,000) $ 5,193 $ 6,247 $ 6,960 $ 53,000 $ 48,000 $ 53,193 $ 59,440 $ 66,400 2006 2007 2008 $ 5,193 $ 6,247 $ 6,960 $ 4,420 $ 4,420 $ 4,420 $ 9,613 $ 10,667 $ 11,380 $ 5,450 $ 5,405 $ 6,865 $ (3,412) $ 3,663 $ 4,089 $ 5,000 $ 5,000 $ 20,000 $ 13,474 $ 7,409 $ (5,844) $ - $ - $ 15,000 $ 4,420 $ 4,420 $ 4,420 $ 9,054 $ 2,989 $ 4,736 1 2 3 2006 2007 2008 $ 13,474 $ 7,409 $ (5,844) 90.91% 82.64% 75.13% $ 12,249 $ 6,123 $ (4,391) $ 2,380 $ 2,380 $ 2,380 93.63% 87.67% 82.09% $ 2,228 $ 2,087 $ 1,954 $ 190,473 $ 100,000 $ 90,473 $ 37,473
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help