Help me with this excel sheet for finance class.

Question 6 (10 points) \begin{tabular}{|l|l|} \hline \multicolumn{2}{|c|}{ Model X } \\ \hline Risk-Free Rate \\ Market Rate & \\ Beta & \\ Cost of Capital & \\ Model X NPV & \\ \hline \end{tabular} Explain: New Model Z: Purchase cost is $650,000 and installation costs are $22,000. The same 5-year MACRS depreciation schedule will be used. At the end of 5 years, the molder can be sold to net $200,000 before taxes. No effect on the firm's current accounts is expected. Estimated earnings before depreciation and taxes for each of the three loaders (Existing, X and Z ) over the next 5 years is shown in the table below. DCl is subject to a corporate tax rate of 21% and cost of capital of 7%. novative Automation Company (IAC) manufactures and sells robotic heavy equipment to many lanufacturing, agriculture and distribution industries. The firm's customers include most of the top argo loading companies (by total annual units) in the world. The cargo loading companies are ontinually searching for more advanced technology solutions to increase truck loading efficiency. One uch company, Dynamic Cargo Inc. (DCI), has been in discussions with IAC about replacing one of its midzed loaders with a more advanced model. IC's sales manager has proposed two cash purchase alternatives for DCl's consideration. Both would oeed up flatbed trailer loading times and reduce labor costs since fewer changeovers and less laintenance would be required. Key financial data for the existing loader and the two proposed ternatives are summarized below. Existing loader: Originally purchased 3 years ago at an installed cost of 425,000, it is being depreciated under the MACRS 5-year schedule. It has 5 more years of economic life. The loader can be sold now for $180,000 before taxes. If retained instead of being replaced, it can be sold at the end of 5 years (its remaining economic life) to net $110,000 before taxes. New Model X: The more advanced of the two recommended alternatives, it can be purchased for $800,000 and will require $24,000 of installation costs. The 5-year MACRS depreciation schedule will be used. At the end of 5 years, it's estimated the machine could be sold to net $375,000 before taxes. Current account changes associated with the acquisition of this loader are listed in the table below. Instructions: Use this template to complete the case questions. Leverage the Helpful Hints in the second sheet of this workbook. See the color key to the right for additional input instructions. Question 1 (30 points) Read the Helpful Hints in the next sheet before starting. \begin{tabular}{|c|c|c|} \hline & Model X & Model Z \\ \hline \multicolumn{3}{|l|}{ Acquisition \& Setup: } \\ \hline \multicolumn{3}{|l|}{ Purchase Price } \\ \hline \multicolumn{3}{|l|}{ Installation Cost } \\ \hline \multicolumn{3}{|l|}{ Total Cost of New} \\ \hline \multicolumn{3}{|l|}{ After Tax Proceeds } \\ \hline \multicolumn{3}{|c|}{ from Sale of Existing: } \\ \hline \multicolumn{3}{|l|}{ Proceeds from Sale } \\ \hline \multicolumn{3}{|l|}{ Book Value } \\ \hline \multicolumn{3}{|l|}{ Tax on Sale } \\ \hline \multicolumn{3}{|l|}{ After Tax Proceeds } \\ \hline \multicolumn{3}{|l|}{ Change in NWC } \\ \hline Initial Investment & $0 & \$0 \\ \hline & & \\ \hline \end{tabular} \begin{tabular}{|c|l|} \hline Color Key & Follow the directions below for the color-coded cells in the template as you work through the case questions 1 - 5. \\ \hline & \begin{tabular}{r} Directly input figures as provided in the case; from select changes noted in MyCourses drop box instructions, if any; or appropriate for that cell. \\ Requires either a formula using cell references or just a cell reference.** Exception is text boxes which require type-written responses. \\ (MUST USE CELL REFERENCES; DON'T DIRECTLY INPUT FIGURES IN THE BLUE CELLS.) \end{tabular} \\ These are answer cells prefilled with formulas/references. Do not input anything into these cells, but reference them in formulas when needed. & \end{tabular} Adjust format to whole dollars for any solution dollar figures appearing with cents. \begin{tabular}{|l|r|} \hline \multicolumn{2}{|c|}{ 5-year MACRS Depreciation } \\ \hline Yr 1 & 20% \\ Yr 2 & 32% \\ Yr 3 & 19% \\ Yr 4 & 12% \\ Yr 5 & 12% \\ Yr 6 & 5% \\ \hline \end{tabular} See Textbook Table 4.3 1) When calculating the Tax on Sale of the Existing loader (for the Initial Investment), you need to first calculate the Book Value of the existing loader. Remember that at this point in our analysis, the Existing loader has been in operation for 3 years, therefore it has been depreciated 3 years...Also, be careful to use the Original installed cost when calculating Book Value, as opposed to the proceeds from the sale. 2) When calculating change in Net Working Capital (NWC): The table of the case gives you the dollar changes. You need to then apply the 'effects' of these changes towards your NWC figure. See pages 152-153 of your text...The NWC figure for Model X (there is no change in NWC for Model Z) is added to the other components in the analysis for Initial Investment because it is a Cost of the project (an increase in NWC needed for the project--a cash outflow). 3) Before constructing the operating and incremental cash flows, complete the depreciation schedule for each of the 3 loaders (Existing, X, Z)...When creating the schedule for the Existing loader, remember that it has already been depreciated 3 years, so year 1 of our analysis is year 4(12%) of MACR's depreciation for the Existing loader. 4) The ultimate goal for the Operating Cash Inflows part is to find the Cash Flow for each year of the Existing loader and the Incremental cash flows for Models X and Z. Incremental cash flows are the Cash Flow of the new loader minus the cash flow of the Existing loader. 5) To calculate Terminal Cash Flow, you may want to revisit pages 521-524...Don't forget the salvage value of the Existing loader...For NWC in this part it is a reversion, meaning the NWC amount is no longer needed due to the end of the project; however, we still Add this figure when calculating Terminal (cash flow) value because it is considered a Cash Inflow (recuperating cash). 6) The blue answer cells are to be completed using formulas with cell references. i.e. Don't input figures directly. For example, when needing to apply the tax rate Chapter 10 discusses three main capital budgeting techniques used for making accept/reject project decisions (payback period, NPV, IRR), while chapter 11 digs into how the cash flows used for the analysis are determined. Here are some key takeaways from the process of determining these cash flows (as illustrated in several homework problems): There are a number of items utilized for ultimately determining the Initial Investment of a project: Depreciation via the MACRS table, accumulated depreciation, book value, tax on sale of asset, after-tax proceeds from sale, and change in net working capital. Change in net working capital is the difference in the amount of increase of current assets compared to the increase in current liabilities. Depreciation is an accounting maneuver used to spread the tax deductibility of an asset out over multiple years, preventing companies from further delaying taxes on current income. Add back depreciation to net profit after tax in determining operating cash flows, because depreciation is not cash. We are concerned with cash because this is what pays the bills and helps us decide if a project is worth doing. When we determine the operating cash flows associated with an old asset versus a new asset being considered for purchase, the difference between the figures is the incremental cash flow (also called relevant cash flow). This incremental cash flow is what is used for making capital budgeting decisions using the techniques described in chapter 10 (payback period, NPV, IRR). 5) (10 points) Based on results from question 3, recommend and explain which, if either, of the new loaders DCl should acquire if the firm has A. unlimited funds B. capital rationing 6) (10 points) Assume now that the cash inflows associated with Model X are considered significantly riskier than Model Z. The company does additional research and determines the Model X beta to be 1.5, the expected return on the market to be 9% and the risk-free rate to be 3%. How does this new information impact your recommendation for which model would be selected (assuming no capital constraints/rationing)? Determine the new NPV for Model X as part of your answer and explain which model, if either, would be chosen. \begin{tabular}{|c|c|c|c|c|c|c|} \hline & & & Timeline & & & \\ \hline & & & & & & \\ \hline & & & Model X & & & \\ \hline \multicolumn{7}{|l|}{ Cash Flow } \\ \hline \multirow[t]{3}{*}{ Period } & 0 & 1 & 2 & 3 & 4 & 5 \\ \hline & & & & & & \\ \hline & & & Model Z & & & \\ \hline \multicolumn{7}{|l|}{ Cash Flow } \\ \hline Period & 0 & 1 & 2 & 3 & 4 & 5 \\ \hline & & & & & & \\ \hline \end{tabular} Question 3 (30 points) \begin{tabular}{|l|l|l|} \hline \multicolumn{3}{|l|}{ Cumulative Cash Flows for Payback Period } \\ \hline Year & Model X & Model Z \\ \hline 1 & 0 & 0 \\ 2 & 0 & 0 \\ 3 & & \\ 4 & & \\ 5 & & \\ \hline \end{tabular} Note: Subtract terminal cash flows for year 5 Payback Period Payback Model X: \# whole years + part of next year to recoup the initial investment. Model Z: \# whole years + part of next year to recoup the initial investment. Use the student Excel template provided in MyCourses to complete the questions below. All work must be shown. Follow the color key provided in the template and utilize the Helpful Hints in the second sheet of the template workbook. 1) (30 points) For each of the two proposed replacement loaders, calculate: A. Initial investment B. Operating cash inflows and Incremental (Relevant) cash inflows C. Terminal cash flow 2) (10 points) Based on the data from question \#1, depict in both table and timeline format the relevant cash flow stream for Model X and Model Z, assuming each is terminated at the end of year 5. 3) (30 points) Calculate for the new loader alternatives each of the following decision methods. A. Payback period B. NPV C. IRR 4) (10 points) Based on their Initial Investments and the company's cost of capital, what is the minimum average annual cash flow required over the five years to accept the new models? (Calculate with a TVM function.) How do your answer figures compare to each model's relevant cash flows over the five years of analysis