Joseph Production Company is bidding a 10-year contract to provide the customer with 4,000 units of product per year. Their accounting department has estimated a labor and material costs of $30 per unit. An initial capital investment of $1,000,000 is required. The equipment belongs to CCA class 43 (rate 30%). Initial net working capital of $50,000 is also needed. The firm must pay factory lease expenses of $50,000 per year. Equipment maintenance expenses are projected to be $30,000 per year. Both lease expenses and maintenance expenses are payable at the end of the year. At the end of the contract, the capital equipment can be sold for $20,000. The firm has a tax rate of 40% and a required return rate of 15%.

Required: DETERMINE THE TAX BEFORE UNIT PRICE of Joseph should bid for this contract. Round the unit price to the nearest dollar. Show all calculations and formulas using EXCEL.

!! I have the steps here on how to do it, I just don't know how to do it:

Step 1 - list out all the costs by year Step 2 - calculate the total after-tax cost by year Step 3 - include initial investment, salvage value, NWC, etc. cash flows Step 3 - add up all the cash flows year by year Step 4 - calculate the NPV of the cash flow of all years Step 5 - use the PV CCA tax shield formula to calculate the tax shield amount using:

Where:

I = Total Capital Investment

d = CCA tax rate

Tc = Corporate Tax Rate

k = discount rate

Sn = Salvage value in year n

n = number of periods in the project

Step 6 - add the NPV (cash outflow, negative number) and the tax shield amount (cash inflow, positive number) to get the final NPV Step 7 - Use the NPV amount in step 6 as the PV, and calculate the PMT amount (to find out what is the minimum revenue needed per year) Step 8 - Divide the PMT amount by the number of product units per year to find out the revenue per unit Step 9 - The amount in step 8 is the after-tax amount because we calculated the NPV with after-tax costs. Then you need to find out what is the before-tax revenue per unit. that is the final answer.

I GAVE OUT THE GIVEN QUESTION AND STEPS THAT NEED TO FOLLOW. JUST PLEASE DO IT IN "EXCEL" WITH TABLES, SHOW CALCULATIONS AND FORMULAS YOU USED, PLEASE AND THANK YOU SO MUCH! :)

IdT 1+0.5k S,dTex_1 PV tax shield on CCA= X d+k 1+k d+k (1+k)" IdT 1+0.5k S,dTex_1 PV tax shield on CCA= X d+k 1+k d+k (1+k)