Complex question on excel. Please show inputs.

Nottinghamm Packaging makes the best camera bags in the world. It is considering expanding its production capacity by purchasing a new machine, the xc-750. The cost of the xc-750 is $4.75 million The costs of loading, unloading, transportation, installation, engineering, etc., will total $250,000. The installation and preparation for operations will take several months and will partially disrupt production. The firm has just completed a $50,000 feasibility study to analyze the decision to expand the production capacity either by additional machines or by replacing the existing machines with new ones. The salient points of the feasibility study are as follows: Machine characteristics. The (economic and physical) life of the machine will be N- 10 years and the salvage value at the end of the life will be 10% of the cost. The firm's policy is to consider the salvage value for the straight-line depreciation method and ignore the salvage value for an accelerated depreciation method. The preferred accelerated depreciation method is MACRS with half-year convention. 1. Revenue structure. While the installation of the new machine takes place, the firm will not be able to handle the currently organized production, consequently sales will decline by $5 million from the current year's expected sales of x million dollars. If the new machine were installed this year (year 0 or date 0), then the new sales would have been generated at $14.0 million. Once the xc-750 is operational the following year (year 1 or date 1), the growth rate in sales expected to be 6.60% in year 0 (date 0) will decline to the long-run average of 1.60% in year N+1 in the following way 2. Cost structure. The cost of goods sold (without depreciation) will be 70% of sales. The sales, general and administrative expense will be 20% of sales. The firm will consider SG&A in year 0 (date 0) as fixed cost, i.e., the firm will not see any change in sG&A expense at year 0 (date 0), if sales change. 3. 4. Accounting. The fim's policy is to consider the salvage value for the straight-line depreciation method and ignore the salvage value for an accelerated depreciation method. The preferred accelerated depreciation method is MACRs with halfyear convention for a ten-year property class. The current marginal tax rate is 21% and is expected to continue for the life of the project and beyond even though the tax management can be improved with a decline of 1% points every year such that the rate will be a manageable 10% at the 7. Cost of capital. The firm is family-owned and has zero debt. The current capital structure is considered optimal. You, the analyst, do not have information about the availability and quantity of retained eamings that could be used towards the new investment You, however, are assuming that the book-value proportion of retained earnings would be available. See information on component costs in Item 10 below. Ideally the weights of the components should be based on market values. You, however, wish to use the book-value proportions. The WACC is used as the discount rate (ak.a cost of capital) and is calculated from: 0.1 -WACO-ko,' weighted average cost ofcapital foryear wweight of retained eamings rl = weight ofprefe red stock "-= weight ofcommon stock w-weight of long-term debt = cost ofcapital forretained eamings cost ofcapital forpreferred stock ' cost ofcapital for common stock tcost of capital for long term debt (marginal) tax rate for year expected loss of sales) and balance sheet (without the expected loss of sales) are 8. Financial statements. The current (ie., date 0) income statements (without and with the given below. Income Statement at t-0 (in '000) Sales Cost of goods sold SG&A expenses EBITDA Depreciation EBIT Interest expense EBT Tax 265,000.00 185,500.00 53,000.00 26,500.00 4,000.00 22,500.00 0.00 22,500.00 4,725.00 17,775.00 260,000.00 182,000.00 53,000.00 25,000.00 4,000.00 21,000.00 0.00 21,000.00 4,410.00 16,590.00 Notes Notes 25,000.00 4,000.00 21,000.00 0.00 21,000.00 4,410.00 16,590.00 EBITDA Depreciation EBIT Interest expense EBT Tax NI Notes: 1. Pro forma numbers, without the expected loss of With the expected sales, if new machine is installed 26,500.00 4,000.00 22,500.00 0.00 22,500.00 4,725.00 17,775.00 Notes loss of sales Balance Sheet at t-0 (in 000) Assets Liabilities Cash Accounts receivable Inventories Total current assets Fixed assets 39,750.00 Short-term debt 39,750.00 Accounts payable 26,500.00 Total current liabilities 106,000.00 Long-term debt 40,000.00 Stockholders' equity 87,450.00 18,550.00 106,000.00 0.00 Preferred stock 12,000.00 8,000.00 Retained earnings Balance Sheet at t-0 (in '000) Assets Liabilities 20,000.00 40,000.00 146,000.00 Common stock Total SE Total assets 46,000.00 Total liabilities and SE Notes: 1. Pro forma numbers, without the expected loss of sales, if new machine is installed. 2. Short-term debt is carried at 0% interest rate. 3. Preferred stock carries a dividend of 8% pa. The par value of preferred shares is $100. 4. The number of shares outstanding of common stock is 8 million. 4. Accounting The firm's policy is to consider the salvage value for the straight-line depreciation method and ignore the salvage value for an accelerated depreciation method The preferred accelerated depreciation method is MACRS with half year convention for a ten-year property class. The current marginal tax rate is 21% and is expected to continue for the life of the project and beyond even though the tax management can be improved with a decline of 1% points every year such that the rate will be a manageable 10% at the horizon date. The year has 365 days. 5. Working capital ratios The ratios are given below. The incremental cost will be incured at year 0 (date 0) and recovered at one year after the end of the life of the machines. a Receivables Tumover-Sales Receivables-6 and 2/3. b. Payables Tumover-COGS/Payables- 10 c. Inventory Turnover = Sales/Inventory-10 d. Cash Tumover-Sales/Cash-6 and 2/3. 6. Marketing. The advertising and marketing campaign is expected to cost $40,000 pa. in fixed fees for the first five years and 1% of sales pa. in the first three years of production. This campaign is expected to result in additional revenues as follows: Year Growth Rate in Revenue 100% 1-50% i 50% 0 50%