Assume the following information is available about a company (on the last page and on D2L as an Excel file you can copy and use to look at the formulas). Build a forecasting model making the following the assumptions: Assumptions 1. The company must pay off $100,000 of LTD each year. 2. On the first day of 2007 they will install a new machine that $200,000 and will be depreciated SL over 10 years. 3. On the first day of 2008 they will purchase a $100,000 machine to be depreciated over 10 years SL. 4. On the first day of 2009 they will purchase an $800,000 machine that will be depreciated over 10 years SL. 5. Assume stock can be sold for $25 a share and they initially have 25,000 shares outstanding. B D E F $200,000 $100,000 New Machinery 10.00% $800,000 Proj 2008 Proj 2009 1 Forecasting 2 3 Growth Rate of Sales 4 5 6 Sales 7 Labor 8 Raw material 9 COGS 10 Gross Profits 11 Operating Costs 12 Other Fixed Costs 13 Depreciation 14 EBIT 15 Interest 16 Taxable income 17 Taxes (40%) 18 After Tax Income 19 Dividends (30% payout) 20 Contri. To RE 21 22 23 Cash 24 AR 25 Inv 26 Curr Assets 27 28 GFA 29 Accum. Dopr 30 NFA 31 Total Assets 2006 $1,250,000 $312,500 $400,000 $712,500 $537,500 $125.000 $55,000 $50,000 $307,500 $76,000 5231,500 $92,600 $138.900 $41,670 $97.230 Proj 2007 $1,375,000 $343.750 $440,000 $783,750 $591,250 $137.500 $55,000 $70,000 $328,750 $84,804 $243.946 $97,578 S146,367 $43,910 $102,457 $175.000 $275,000 S255.000 $705.000 $192,500 $302.500 S280 500 S775,500 $1,550,000 $475 000 $1.075,000 $1.780,000 $1,750,000 5545.000 $1,205,000 $1,980,500 31 Tors Asters 11780.000 01.980.500 33 34 AIP NUP 36 Accruals 37 ST portion of LTD 38 Curr Lieb 155.000 175.000 155.000 1100.000 1285.000 350.000 $75.000 150.000 $100.000 $275.000 3600 000 480000 1425.000 11780.000 10 3688,043 $480,000 1527457 $1.380.500 10 2.6 16 2.7 17 492% 4.0 491 3.9 40 LTO 10% 41 CS 42 PE 43 TL 8 NW 44 Additional Financing Necessary 45 46 Ratios 47 Current Ratio 48 Qack Rio 49 50 51 Debe Rio TE 5 54 Inw. TO 55 TATO 50 APTO 57 ARTO 50 59 60 Return on Sales 61 Gross ProtMargin 62 EPS 63 Shares of Stock 64 65 65 67 28 0.7 143 45 28 07 143 45 POE 14.5% 10.6% 15.3% 111% 430% 15.56 25.000 15.05 25.000 O Assume the following information is available about a company (on the last page and on D2L as an Excel file you can copy and use to look at the formulas). Build a forecasting model making the following the assumptions: Assumptions 1. The company must pay off $100,000 of LTD each year. 2. On the first day of 2007 they will install a new machine that $200,000 and will be depreciated SL over 10 years. 3. On the first day of 2008 they will purchase a $100,000 machine to be depreciated over 10 years SL. 4. On the first day of 2009 they will purchase an $800,000 machine that will be depreciated over 10 years SL. 5. Assume stock can be sold for $25 a share and they initially have 25,000 shares outstanding. B D E F $200,000 $100,000 New Machinery 10.00% $800,000 Proj 2008 Proj 2009 1 Forecasting 2 3 Growth Rate of Sales 4 5 6 Sales 7 Labor 8 Raw material 9 COGS 10 Gross Profits 11 Operating Costs 12 Other Fixed Costs 13 Depreciation 14 EBIT 15 Interest 16 Taxable income 17 Taxes (40%) 18 After Tax Income 19 Dividends (30% payout) 20 Contri. To RE 21 22 23 Cash 24 AR 25 Inv 26 Curr Assets 27 28 GFA 29 Accum. Dopr 30 NFA 31 Total Assets 2006 $1,250,000 $312,500 $400,000 $712,500 $537,500 $125.000 $55,000 $50,000 $307,500 $76,000 5231,500 $92,600 $138.900 $41,670 $97.230 Proj 2007 $1,375,000 $343.750 $440,000 $783,750 $591,250 $137.500 $55,000 $70,000 $328,750 $84,804 $243.946 $97,578 S146,367 $43,910 $102,457 $175.000 $275,000 S255.000 $705.000 $192,500 $302.500 S280 500 S775,500 $1,550,000 $475 000 $1.075,000 $1.780,000 $1,750,000 5545.000 $1,205,000 $1,980,500 31 Tors Asters 11780.000 01.980.500 33 34 AIP NUP 36 Accruals 37 ST portion of LTD 38 Curr Lieb 155.000 175.000 155.000 1100.000 1285.000 350.000 $75.000 150.000 $100.000 $275.000 3600 000 480000 1425.000 11780.000 10 3688,043 $480,000 1527457 $1.380.500 10 2.6 16 2.7 17 492% 4.0 491 3.9 40 LTO 10% 41 CS 42 PE 43 TL 8 NW 44 Additional Financing Necessary 45 46 Ratios 47 Current Ratio 48 Qack Rio 49 50 51 Debe Rio TE 5 54 Inw. TO 55 TATO 50 APTO 57 ARTO 50 59 60 Return on Sales 61 Gross ProtMargin 62 EPS 63 Shares of Stock 64 65 65 67 28 0.7 143 45 28 07 143 45 POE 14.5% 10.6% 15.3% 111% 430% 15.56 25.000 15.05 25.000 O
