capital budgeting question. I need help from a Finance expert.

Question 1 Common size financial statements Input area: Assets 2013 Current assets Cash Accounts receivable Inventory Total $ $ 2014 16,298 $ 25,968 44,188 86,454 $ 18,840 26,808 47,644 93,292 Fixed assets Net plant and equipment $ 384,416 $ 406,820 Total assets $ 470,870 $ 500,112 Output area: 2013 Common size Assets Current assets Cash Accounts receivable Inventory Total Fixed assets Net plant and equipment Total assets $ 16,298 25,968 44,188 86,454 3.46% 5.51% 9.38% 18.36% 384,416 $ 470,870 81.64% 100% $ Liabilities and Owners' Equity Current liabilities Accounts payable Notes payable Total Long-term debt Owners' equity $ $ $ 22,902 29,436 52,338 42,000 4.86% 6.25% 11.12% 8.92% Common stock and paid-in surplus Accumulated retained earnings Total Total liabilities and owners' equity $ 50,000 326,532 $ 376,532 $ 470,870 10.62% 69.35% 88.88% 100% Liabilities and Owners' equity 2013 Current liabilities Accounts payable Notes payable Total Long-term debt Owners' equity Common stock and paid-in surplus Accumulated retained earnings Total Total liabilities and owners' equity $ $ $ 22,902 $ 29,436 52,338 $ 42,000 $ Liabilities and Owners' Equity Current liabilities Accounts payable Notes payable Total Long-term debt Owners' equity 24,768 30,366 55,134 60,000 $ 50,000 $ 50,000 326,532 334,978 $ 376,532 $ 384,978 $ 470,870 $ 500,112 2014 Assets Current assets Cash Accounts receivable Inventory Total Fixed assets Net plant and equipment Total assets 2014 $ Common size Common base year 18,840 26,808 47,644 93,292 3.77% 5.36% 9.53% 18.65% 1.1560 1.0323 1.0782 3.2665 $ 406,820 $ 500,112 81.35% 100.00% 1.0583 4.3248 4.95% 6.07% 11.02% 12.00% 1.0815 1.0316 2.1131 1.4286 $ $ $ $ 24,768 30,366 55,134 60,000 Common stock and paid-in surplus Accumulated retained earnings Total Total liabilities and owners' equity $ 50,000 334,978 $ 384,978 $ 500,112 10.00% 66.98% 76.98% 100.00% 1.0000 1.0259 2.0259 5.5675 Question 2 a. Use the spreadsheet to calculate as many of the company's Profitability, Turnover-Control, and Leverage and Liquidity ratios as you can for these years (see output area for a list of ratios). b. What do these ratios suggest about the company's performance over this period? Input area: ANNUAL BALANCE SHEET ($ MILLIONS) MENS WEARHOUSE INC 2010 ASSETS Cash & Short-Term Investments Net Receivables Inventories Prepaid Expenses Other Current Assets Total Current Assets Gross Plant, Property & Equipment Accumulated Depreciation Net Plant, Property & Equipment Intangibles Other Assets TOTAL ASSETS LIABILITIES Long Term Debt Due In One Year Accounts Payable Taxes Payable Accrued Expenses Other Current Liabilities Total Current Liabilities Long Term Debt Deferred Taxes 2009 2008 2007 2006 104.533 99.367 40.662 24.872 440.099 492.423 26.603 27.179 74.075 19.718 27.154 ------------------ ------------------ -----------------708.330 631.615 670.995 179.694 17.018 448.586 0.000 35.531 -----------------680.829 263.001 19.276 416.603 0.000 30.732 -------------729.612 866.005 885.981 865.084 521.259 498.509 454.917 ------------------ ------------------ -----------------344.746 387.472 410.167 59.414 65.268 75.609 119.616 103.375 99.696 ------------------ ------------------ -----------------1,232.106 1,187.730 1,256.467 669.340 379.700 -----------------289.640 61.765 64.718 -----------------1,096.952 611.957 342.371 -------------269.586 63.073 61.003 -------------1,123.274 0.000 111.213 19.676 75.458 19.791 -----------------226.138 0.000 125.064 21.086 72.531 19.404 -------------238.085 72.967 12.200 207.750 24.400 186.018 16.745 431.492 0.000 83.052 23.936 0.000 0.000 108.800 146.713 0.019 5.590 66.542 70.222 117.047 44.862 54.730 ------------------ ------------------ -----------------224.035 220.223 277.255 43.491 62.916 2.700 92.399 4.000 Other Liabilities TOTAL LIABILITIES EQUITY Common Stock Capital Surplus Retained Earnings Less: Treasury Stock TOTAL EQUITY TOTAL LIABILITIES & EQUITY Common Shares Outstanding 62.236 59.743 66.876 ------------------ ------------------ -----------------329.762 345.582 440.530 31.875 -----------------343.180 25.506 -------------495.741 0.705 0.700 0.696 327.742 315.404 305.209 986.523 938.580 923.713 412.626 412.536 413.681 ------------------ ------------------ -----------------902.344 842.148 815.937 ------------------ ------------------ -----------------1,232.106 1,187.730 1,256.467 0.691 286.120 775.857 308.896 -----------------753.772 -----------------1,096.952 0.671 255.214 641.558 269.910 -------------627.533 -------------1,123.274 53.919 53.069 52.288 51.918 51.479 ANNUAL INCOME STATEMENT (MILLIONS, EXCEPT PER SHARE) Jan10 Sales Cost of Goods Sold Gross Profit Selling, General, & Administrative Exp. Operating Income Before Deprec. Depreciation,Depletion,&Amortization Operating Profit Interest Expense Non-Operating Income/Expense Special Items Pretax Income Total Income Taxes Income Before Extraordinary Items & Discontinued Operations Savings Due to Common Stock Equiv. Jan09 Jan08 Jan07 Jan06 1,909.575 1,972.418 2,112.558 1,025.759 1,031.241 1,062.205 ------------------- ------------------ -----------------883.816 941.177 1,050.353 732.722 757.073 741.405 ------------------- ------------------ -----------------151.094 184.104 308.948 86.090 90.665 80.296 ------------------- ------------------ -----------------65.004 93.439 228.652 1,882.064 1,004.972 -----------------877.092 591.767 -----------------285.325 61.387 -----------------223.938 1,724.898 965.889 --------------759.009 531.839 --------------227.170 61.874 --------------165.296 0.332 4.300 5.046 0.000 2.592 5.987 3.200 (2.968) 0.000 ------------------- ------------------ -----------------67.872 88.763 229.593 22.364 29.919 82.552 ------------------- ------------------ ------------------ 9.216 9.786 0.000 -----------------224.508 75.933 ------------------ 5.888 3.280 0.000 --------------162.688 58.785 --------------- 45.508 58.844 147.041 ------------------- ------------------ -----------------(0.834) 0.000 0.000 148.575 -----------------0.000 103.903 --------------0.000 Adjusted Net Income ------------------- ------------------ -----------------44.674 58.844 147.041 -----------------148.575 --------------103.903 EPS Basic from Operations EPS Diluted from Operations 0.820 0.820 1.180 1.170 2.760 2.730 2.710 2.620 2.010 1.950 Dividends Per Share 0.280 0.280 0.230 0.200 0.000 52.280 52.280 51.645 51.944 53.258 53.890 53.111 54.749 53.753 55.365 Com Shares for Basic EPS Com Shares for Diluted EPS Output area: (a) 2010 2009 2008 2007 2006 Profitability Ratios (in %) Return on equity Return on assets Profit margin Gross margin 2.3% 46.3% 3.0% 47.7% 7.0% 49.7% 7.9% 46.6% 6.0% 44.0% Turnover-Control Ratios Asset turnover Fixed-asset turnover Inventory turnover Collection period (days) Days' sales in cash Payables period 1.5 5.5 2.4 3.2 35.6 29.6 1.7 5.1 2.3 7.5 19.3 38.5 1.7 5.2 2.2 4.3 17.2 50.4 1.7 6.5 2.2 3.3 34.8 40.4 1.5 6.4 2.3 4.1 55.7 47.3 1.4 26.8% 36.5% 195.8 3.7 1.2 1.4 29.1% 41.0% 21.7 3.4 0.9 1.5 35.1% 54.0% 45.3 2.9 0.6 1.5 31.3% 45.5% 24.3 3.2 1.0 1.8 44.1% 79.0% 28.1 2.3 1.3 Leverage and Liquidity Ratios Assets to equity Debt to assets Debt to equity Times interest earned Current ratio Quick ratio (aka acid test) (b) Question 3 Stac Corporation Financial Statements, 2015 and Projected 2016 ($ millions) INCOME STATEMENT Actual Projected 2015 2016 Sales $ 3,500 $ 4,025 COGS 2,775 3,019 Operating expense 360 403 EBIT 365 604 Interest expense 68 80 EBT 297 524 Tax 102 183 Net income $ 195 $ 341 Assumptions for 2016 Sales growth rate COGS/sales Oper. Exp./sales Dividend payout ratio Tax rate Interest rate on debt Total debt/equity 15.00% 75.00% 10.00% 40.00% 35.00% 7.20% 50.00% BALANCE SHEET Actual Projected 2015 2016 Cash $ 150 $ 173 Accounts receivable 540 621 Inventory 1,050 1,208 Total current assets 1,740 2,001 Property, plant, & equipment 1,578 1,815 Total assets 3,318 3,816 Total debt Shareholders' equity Total liabilities & equity External funding required Sustainable growth rate $ 1,106 2,212 3,318 $ actual growth rate this yrs sales-last yr sales / last yr sales 0.15 $ 525 #NAME? 1,208 2,416 3,625 191 9.24% Instructions: Use the pro forma financial statements to answer the questions below. Change the assumptions in the assumptions box as needed to answer the questions. In addition to the assumptions listed on the spreadsheet, also assume that all asset accounts will grow at the same rate as sales, and that no new equity will be issued in 2016. Questions: a. How much external financing does Stac need in 2016? Enter your answer (with viewable formula) in cell G19 Answers a. Enter your answer in cell G19 b. What is Stac's sustainable growth rate? Enter a formula for the sustainable growth rate in the second green box in G20. b. Enter your answer in cell G19 c. At what rate does the actual sales growth rate equal the sustainable growth rate? How much external financing is required at this growth rate? Show answer in two decimal places. (This can be determined by trial and error.) c. d. Return the sales growth rate to 15%. Suppose Stac wants to solve the financing shortfall by increasing profit margin. How low would the ratio of COGS/Sales have to go in order to make up the shortfall? With COGS/Sales at this lower level, what is the sustainable growth rate? Show answer in two decimal places. (Hint: The Goal Seek tool can help you find this quickly. Consult Excel Help if you are unfamiliar with the Goal Seek tool.) d. e. Return COGS/Sales to 75%. Now suppose Stac wants to solve the shortfall by increasing the retention ratio. How low would the dividend payout ratio have to be in order to eliminate the financing shortfall? Show answer in two decimal places. e. f. Return the dividend payout ratio to 40%. Now suppose Stac wants to make up any financing shortfall with increased debt. How high would the debt/equity ratio have to be to make up the difference? Show answer in two decimal places. f. Question 4 A company is considering two alternative methods of producing a new product. The relevant data concerning the appear below: Initial investment Annual receipts Annual disbursements Annual depreciation Expected life Salvage value Alternative I $64,000 $50,000 $20,000 Alternative II $120,000 $60,000 $12,000 $16,000 4 yrs 0 $20,000 6 yrs 0 At the end of the useful life of whatever equipment is chosen the product will be discontinued. The company's tax percent and the discount rate is 10 percent. a. Calculate the net present value of each alternative. b. Calculate the benefit cost ratio for each alternative. c. Calculate the internal rate of return for each alternative. d. If the company underrationing, capital rationing alternative should be chosen? Why?indefinitely rather than e. Again assumingisnonot capital supposewhich the company plans to produce the product equipment wears out. Which alternative company f. If the company is experiencing severe should capital the rationing, andselect? plans toWhy? terminate production when the equipment any of your answers above change? . The relevant data concerning the alternatives be discontinued. The company's tax rate is 50 Why?indefinitely rather than quit when the eosen? the product ate production when the equipment wears out, would