Help would be awesome!

1. To take on this contract, CheeseQueen needs to purchase new equipment at the cost of $460,000. The new equipment will be depreciated using a five-year MACRS schedule. The used equipment can be sold at 20% of its purchase price at the end of year 4. 2. The new production line would be placed on a farm currently rented by a small business. The rental income is $15,000/year (after tax). If your parents accept the project, they would have to terminate the rental contract. Your parents also have an agreement to sell this farm in five years to a real estate developer for $250,000 (after tax). It means that after this project is completed, your parents have to restore the farm to its original condition. The restoration will happen in year 5, and the cost is $80,000. 3. The contract calls for delivery of 55,000 pounds of cheese per year at $7 per pound for four years. To utilize the capacity of the new equipment, your parents plan to produce 60,000 pounds, 83,000 pounds, 85,000 pounds, and 74,000 pounds, respectively, over the next four years. The excess production is going to be sold at $9 per pound in retail market. 4. The estimated variable cost is $2.1 per pound, and the fixed cost is $200,000 per year. 5. CheeseQueens net working capital (NWC) investment is 10% of sales. The NWC will be built up at the beginning of each year. (i.e. NWC needed for year t = 10% of sales of year t) 6. CheeseQueens tax rate is 30%. Its cost of capital is 10% (discount rate). Assume a loss in any year will result in a tax credit. You are asked to build an Excel model to calculate payback period (use user-defined function), net present value, and internal rate of return for the project. Upon completion of the analysis, would you recommend your parents to take the project?

If you could please provide formulas so I understand what you did! Thanks!

AutoSave OF: ABOG dud Templeate for Mini Case V2 Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments v 12 Calibri v Al A Insert General X G 27 O v DX Delete Paste B 1 U UV Y y Av = = = Y $ %) 99 Conditional Format Cell Formatting as Table Styles Farmst v Sort & Filter Find a Select Analyze Data Create and Share Adobe PDF D35 fx A C D E F G H N 5 E 7 30% $ 2.10 $ 200,000.00 10% Inputs Tax rate: Variable Cost/pound: Fixed Cost/year % of NWC of annual sale: Cast of machine Restoration Cost of the Farm % of Pretax salvage value of initial purchase price: Rental income from the farm/year Contract Price/pound Retail price per pound: cost of capital (discount ratel Sales Price of the Farm $ 460,000.00 $ 80,000.00 20% $ 15,000.00 $ 7.00 $ 9.00 10% $ 250,000.00 Year 1 Year 2 Year 3 Year 4 Year 5 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Tatal Pounds sold per year Pourds sold through contract: Contract Sales Pound sold in the retail market per year: Retail sales Total Revenue Variable Cost: Fixed Cost Tatal Cast: Year 1 Year 2 Year 3 Yeur 4 Year 5 Depreciation rate: Depreciation Accumulated depreciation Ad usted basis of machine Year 1 Year 2 Year 3 Year 4 Year 5 Net worlding capital Beginning NWC End of year NWC NWC cash flow 45 Sheet1 + Ready + 100% Pretax salvage value Taxes on sale Aftertax salvage value Year 1 Year Year Year 4 Year 5 Sales revenue Operating costs Depreciation Income before taxes Taxes Net income Year o Year: Year 2 Year 3 Year Year 5 ERIT Depreciation Taxes Cash flow from operations Fquipment Opportunity Cost (Farm) Expense associated with Farm Restoration Net working capital Cash Flows (change of NWC) Total cash flow of project NPV Paytack