Hi Shelly, can you help me with this two projects. In excel with formulas.

Project 1 Calculations must be done in Excel Polycorp is considering an investment in new plant of $3.25 million. The project will be partially financed with a loan of $2,000,000 which will be repaid over the next five years in equal annual end of year instalments at a rate of 6.23 percent pa. Assume straight-line depreciation over a five-year life, and no taxes. The projects cash flows before loan repayments and interest are shown in the table below. Cost of capital is 12.05% pa (the required rate of return on the project). A salvage value of $225,000 is expected at the end of year five and is not included in the cash flows for year five below. Year Cash Inflow Year One 920,000 Year Two 875,000 Year Three 895,000 Year Four 955,000 Year Five 925,000 You are required to calculate: (1) The amount of the annual loan repayment and produce a repayment schedule. (2) NPV of the project (to the nearest dollar) (3) IRR of the project (as a percentage to two decimal places) (4) AE, the annual equivalent for the project (AE or EAV) (to the nearest dollar) (5) PB, the payback and discounted payback in years (to one decimal place) (6) ARR, the accounting rate of return (gross and net) (to two decimal places) (7) PI (present value index or profitability index) (to two decimal places) (8) Is the project acceptable? You must provide a decision or explanation for each of the methods in parts (2) to (7). Why or why not (provide a full explanation)? Also a brief explanation of your treatment of Salvage Value and Loan Repayments is required. Project 2 Calculations must be done in Excel Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three-year contract. The new machine will cost $1.85 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $105,000. The contract will not continue beyond three years and the equipment's estimated salvage value at the end of three years is $291,000. The tax rate is 28 percent and is payable in the year following the year in which profit is earned. An investment allowance of twenty-five percent on the outlay is available. The after tax cost of capital is 14.65%pa. Addition current assets of $96,000 are required immediately for working capital to support the project. Assume that this amount less $20000 is recovered in full at the end of the three-year life of the project. The new product will be charged $139,500 of allocated head office administration costs each year even though head office will not actually incur any extra costs to manage the project. This is in accordance with the firm's policy of allocating all corporate overhead costs to divisions. Extra marketing and administration cash outflows of $178,000 per year will be incurred by the Steel Division for the project. An amount of $149,000 has been spent on a pilot study and market research for the new product. The projections provided here are based on this work. Projected sales in the first year for the new product are 40,000 units at $142 per unit per year. Unit sales are expected to increase by 5%pa for year two and three. Cash operating expenses are estimated to be 80 percent of sales (excludes marketing and administration, and head office items). Except for initial outlays, assume cash flows occur at the end of each year (unless otherwise stated). Assume diminishing value depreciation for tax purposes. Required (a) Construct a table showing net cash flow after tax (NCFAT). Use the method shown in lectures. (b) Calculate the NPV. Is the project acceptable? Why or why not? (c) Conduct a sensitivity analysis showing how sensitive the project is to operating expenses and to the cost of capital. Explain your results. (d) Write a short report explaining your calculation of relevant net cash flows after tax, justifying your selection of cash flows. Be sure to state clearly any assumptions made (implicit and explicit). Part (a) and (b) [3 marks] Must have correct NCFAT [may differ depending on tax treatment of loss] Must have correct NPV [may differ depending on tax treatment of loss] NPV positive so accept Deduct 1 mark for first error and .5 mark for each subsequent error. Do not penalise cascading errors