Please help with equations and cell references needed

Check Figure: NPV $1,112 in favor of overhauling the old truck REOUIREMENTS: - Complete the requirements for P12-28 on page 603 of your textbook. - Do the Total Cost Approach first then, do an Incremental Cost Approach analysis below your Total Cost Annroach They should both be on the same page. - REQUIRED COMPONETS: - Two Logic "IF" statements MUST be used to label the Decision as being in favor of overhauling the old truck or in favor of purchasing the new truck. One Logic IF for the Total Cost Approach and one Logic IF for the Incremental Cost Approach. This is a required component for this assignment. If you do not complete both Logic IF stmts, you will not receive credit for this assignment. - Excel's PV function must be used to calculate the present value of the cash flows. Do not use the factor tables as is illustrated in the text. o A data block and cell referencing is required. - There is NO What IF part to this assignment. DATA BLOCK AND ANALYSES FORMATS: - An Excel #2 template has been posted also with the following format illustrated below for your data block page and the analysis report. - Include the total cost approach analysis and the incremental analysis both on one spreadsheet page. - The template posted for Excel #2 uses the format illustrated on the following page for your total cost approach analysis: - Do not include a 'Factor' column as is shown in the power point slides since excel will calculate that for you. - Under the 'year(s)' column, use ' 0 ' where there would be a 'Now', since no time has passed from now. Be sure to input the year(s) or period(s) in your data block so you can cell reference them to your spreadsheet formulas. - Under the "year(s)" or "periods" column, for the annuity (annual cash operating costs), use the total number of years for the annuity instead of the range. For example, instead of showing the range ' 15 ', you will use ' 5 ' in your spreadsheet. - To calculate present value, use excel formula function (PV) and NOT the tables in the textbook. See below for instructions on how to use the PV function in excel. - Use borders for your column headings and data underlines. Double underlines for Net Present Value solution. - The Excel #2 template posted uses the following format for your incremental cost analysis: GENERAL INFORMATION: Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets. We will assume you cheated and you both (or all) will get a zero for the assignment. Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work on a disk. SAVING YOUR FILE: Save the original file according to the following name format: Original data file. (Your Last Name, First Name Initial) Excel#2. For Example: SmithJExcel2.xls or SmithJExcel2.xlsx (depending on which version of Microsoft you are using). USING EXCEL TO CALCULATE PRESENT VALUE (PV) LOGIC IF STATEMENT INSTRUCTIONS: Present Value Function Instructions: 1. From the standard toolbar, select the button, ' fx,. This button will bring up a box called 'Insert Function'. 2. Under function category, select "Financial". 3. Under function name, find and select "PV". 4. After completing step 2 \& 3, select "ok" to bring up the box for the PV function. 5. In the PV function box, enter in the relevant information using cell references. For example, for the 'rate', put your cursor in the 'rate' area, go to the data block, click on the 'rate' cell, and then click on the next area that information is needed. Some of the areas will have no cell references if it is not relevant to the calculation. When all relevant information is entered, click 'ok'. In the example below, the cell references will be different from your cell references. 6. When placing the cell reference into the ' Fv ' area, make sure there is a negative (-) sign before the reference, otherwise your output will have the wrong sign. 7. You will need to use the Pmt box when calculating the PV of an annuity and you will use the Fv box when calculating the PV of a single sum. 8. The Nper box is for the time period of the inflow or outflow. Logical "If" Statement Instructions: To properly label your NPV result, use a "Logical IF statement." Instead of typing in Net Present Value to the left of the calculated NPV have excel determine the correct result by using a Logic IF statement instead. Remember, the general formula for a Logic IF statement is: = If (condition, true, false) You must tell Excel what the condition is, what to do if that condition is true, and what to do if that condition is false. Here, when doing the total cost approach, the condition is: if the result is = or > than 0 , then have excel type in "NPV in favor of overhauling the old truck" (this is if the condition is true), otherwise have excel type in "NPV in favor of buying the new truck" (this is if the condition is false). An easy way to have Excel help you create this formula is to use the formula wizard. Click on the ' f ' ' button beside the editing toolbar. Then use the arrow key to locate more formula options (sum should be the default). Choose IF. You will be presented with a box that asks for the necessary data. You can also modify the formula to include multiple results for multiple criteria. SUBMISSION OF YOUR EXCEL ASSIGNMENT: Put a footer on each page in the bottom right-hand corner which includes your name and ZID#. Before submitting your Excel assignment, check the Print Preview to make sure your report is centered (horizontally) in the page and you have included the footer. Make sure you attach the correct file before clicking the submit button on Bb. Excel #2 Info announcement Excel #2 Incremental Cost Analysis: When completing the Incremental Analysis portion of the excel assignment, you are looking from the point of view of overhauling the old truck against the other alternative, purchasing the new truck. Think about it this way: If I overhaul the old truck it is going to cost me $7,000 right now. Compare this to the other alternative; purchasing the new truck for $30,000. Is overhauling the old truck going to cost me more or less than if I purchased a new truck? $7,000 vs. $30,000. How much less? Your formula is: Cell reference for $7,000 - cell reference for $30,000= will result in a positive $23,000. Remember your algebra! You have a double negative in the formula; you are minusing a negative number which turns it positive so the result will be positive. Excel knows algebra too! You can use similar formulas for each of the other items. The only exception is the lost salvage value of $9,000. Since it is all lost you can just cell reference this number down and turn it negative by putting a negative sign in front of the cell reference. (=$9,000.) Also, in the Incremental Analysis, if you have a cost savings, then the number should be positive (NOT negative). If, instead, you have an additional cost, then the number should be negative (NOT positive). PROBLEM 1228 Net Present Value Analysis LO122 Bilboa Freightlines, S.A., of Panama, has a small truck that it uses for intracity deliveries. The truck is worn out and must be either overhauled or replaced with a new truck. The company has assembled the following information: If the company keeps and overhauls its present delivery truck, then the truck will be usable for five more years. If a new truck is purchased, it will be used for five years, after which it will be traded in on another truck. The new truck would be diesel-operated, resulting in a substantial reduction in annual operating costs, as shown above. The company computes depreciation on a straight-line basis. All investment projects are evaluated using a 16% discount rate. 1. What is the net present value of the "keep the old truck" alternative? Round to the nearest whole dollar. 2. What is the net present value of the "purchase the new truck" alternative? Round to the nearest whole dollar. 3. Should Bilboa Freightlines keep the old truck or purchase the new one? Check Figure: NPV $1,112 in favor of overhauling the old truck REOUIREMENTS: - Complete the requirements for P12-28 on page 603 of your textbook. - Do the Total Cost Approach first then, do an Incremental Cost Approach analysis below your Total Cost Annroach They should both be on the same page. - REQUIRED COMPONETS: - Two Logic "IF" statements MUST be used to label the Decision as being in favor of overhauling the old truck or in favor of purchasing the new truck. One Logic IF for the Total Cost Approach and one Logic IF for the Incremental Cost Approach. This is a required component for this assignment. If you do not complete both Logic IF stmts, you will not receive credit for this assignment. - Excel's PV function must be used to calculate the present value of the cash flows. Do not use the factor tables as is illustrated in the text. o A data block and cell referencing is required. - There is NO What IF part to this assignment. DATA BLOCK AND ANALYSES FORMATS: - An Excel #2 template has been posted also with the following format illustrated below for your data block page and the analysis report. - Include the total cost approach analysis and the incremental analysis both on one spreadsheet page. - The template posted for Excel #2 uses the format illustrated on the following page for your total cost approach analysis: - Do not include a 'Factor' column as is shown in the power point slides since excel will calculate that for you. - Under the 'year(s)' column, use ' 0 ' where there would be a 'Now', since no time has passed from now. Be sure to input the year(s) or period(s) in your data block so you can cell reference them to your spreadsheet formulas. - Under the "year(s)" or "periods" column, for the annuity (annual cash operating costs), use the total number of years for the annuity instead of the range. For example, instead of showing the range ' 15 ', you will use ' 5 ' in your spreadsheet. - To calculate present value, use excel formula function (PV) and NOT the tables in the textbook. See below for instructions on how to use the PV function in excel. - Use borders for your column headings and data underlines. Double underlines for Net Present Value solution. - The Excel #2 template posted uses the following format for your incremental cost analysis: GENERAL INFORMATION: Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets. We will assume you cheated and you both (or all) will get a zero for the assignment. Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work on a disk. SAVING YOUR FILE: Save the original file according to the following name format: Original data file. (Your Last Name, First Name Initial) Excel#2. For Example: SmithJExcel2.xls or SmithJExcel2.xlsx (depending on which version of Microsoft you are using). USING EXCEL TO CALCULATE PRESENT VALUE (PV) LOGIC IF STATEMENT INSTRUCTIONS: Present Value Function Instructions: 1. From the standard toolbar, select the button, ' fx,. This button will bring up a box called 'Insert Function'. 2. Under function category, select "Financial". 3. Under function name, find and select "PV". 4. After completing step 2 \& 3, select "ok" to bring up the box for the PV function. 5. In the PV function box, enter in the relevant information using cell references. For example, for the 'rate', put your cursor in the 'rate' area, go to the data block, click on the 'rate' cell, and then click on the next area that information is needed. Some of the areas will have no cell references if it is not relevant to the calculation. When all relevant information is entered, click 'ok'. In the example below, the cell references will be different from your cell references. 6. When placing the cell reference into the ' Fv ' area, make sure there is a negative (-) sign before the reference, otherwise your output will have the wrong sign. 7. You will need to use the Pmt box when calculating the PV of an annuity and you will use the Fv box when calculating the PV of a single sum. 8. The Nper box is for the time period of the inflow or outflow. Logical "If" Statement Instructions: To properly label your NPV result, use a "Logical IF statement." Instead of typing in Net Present Value to the left of the calculated NPV have excel determine the correct result by using a Logic IF statement instead. Remember, the general formula for a Logic IF statement is: = If (condition, true, false) You must tell Excel what the condition is, what to do if that condition is true, and what to do if that condition is false. Here, when doing the total cost approach, the condition is: if the result is = or > than 0 , then have excel type in "NPV in favor of overhauling the old truck" (this is if the condition is true), otherwise have excel type in "NPV in favor of buying the new truck" (this is if the condition is false). An easy way to have Excel help you create this formula is to use the formula wizard. Click on the ' f ' ' button beside the editing toolbar. Then use the arrow key to locate more formula options (sum should be the default). Choose IF. You will be presented with a box that asks for the necessary data. You can also modify the formula to include multiple results for multiple criteria. SUBMISSION OF YOUR EXCEL ASSIGNMENT: Put a footer on each page in the bottom right-hand corner which includes your name and ZID#. Before submitting your Excel assignment, check the Print Preview to make sure your report is centered (horizontally) in the page and you have included the footer. Make sure you attach the correct file before clicking the submit button on Bb. Excel #2 Info announcement Excel #2 Incremental Cost Analysis: When completing the Incremental Analysis portion of the excel assignment, you are looking from the point of view of overhauling the old truck against the other alternative, purchasing the new truck. Think about it this way: If I overhaul the old truck it is going to cost me $7,000 right now. Compare this to the other alternative; purchasing the new truck for $30,000. Is overhauling the old truck going to cost me more or less than if I purchased a new truck? $7,000 vs. $30,000. How much less? Your formula is: Cell reference for $7,000 - cell reference for $30,000= will result in a positive $23,000. Remember your algebra! You have a double negative in the formula; you are minusing a negative number which turns it positive so the result will be positive. Excel knows algebra too! You can use similar formulas for each of the other items. The only exception is the lost salvage value of $9,000. Since it is all lost you can just cell reference this number down and turn it negative by putting a negative sign in front of the cell reference. (=$9,000.) Also, in the Incremental Analysis, if you have a cost savings, then the number should be positive (NOT negative). If, instead, you have an additional cost, then the number should be negative (NOT positive). PROBLEM 1228 Net Present Value Analysis LO122 Bilboa Freightlines, S.A., of Panama, has a small truck that it uses for intracity deliveries. The truck is worn out and must be either overhauled or replaced with a new truck. The company has assembled the following information: If the company keeps and overhauls its present delivery truck, then the truck will be usable for five more years. If a new truck is purchased, it will be used for five years, after which it will be traded in on another truck. The new truck would be diesel-operated, resulting in a substantial reduction in annual operating costs, as shown above. The company computes depreciation on a straight-line basis. All investment projects are evaluated using a 16% discount rate. 1. What is the net present value of the "keep the old truck" alternative? Round to the nearest whole dollar. 2. What is the net present value of the "purchase the new truck" alternative? Round to the nearest whole dollar. 3. Should Bilboa Freightlines keep the old truck or purchase the new one