EXCEL ASSIGNMENT #2 . Check Figure: NPV $1,112 in favor of overhanling the old truck REQUIREMENTS: Complete the requirements for P12-28 on page 602 of your textbook Do the Total Cost Approach first then, do an Incremental Cost Approach analysis below your Total Cost Approach 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. . A data block and cell referencing is required There is NO What IF part to this assignment DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page Data Block Biboa Freightlines Required Rate or Return Present New Year! Thuck Truck Periods Purchase cost new Overhaul needed now cash flows. Do not use the factor tables as is illustrated in the text, 6. A data block and cell referencing is required. There is NO What IF part to this assignment. DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page. Data Block Bilboa Freightlines 1 Required Rate of Retur . Present Truck New Truck Year! Periods Purchase cost new Overhaul needed now Annual cash operating costs Savage value now Savanje value five years from now Include the total cost approach analysis and the incremental analysis both on one spreadsheet page You can use the format illustrated on the following page for your total cost approach analysis . PV Biboa Freightlines, SA Total Cost Approach Net Present Valle Calculation Amount of Item Year(s) Cash Flows Overhaul old truck Overhaul now Annual cash operating cost Savage value in five years Net Present Value 1 Purchase new truck Purchase cost new Salvage value of old truck now Annual cash operating costs Savage value of new truck in five years Net Present Value Logic IF statement belongs here . Do not include a 'Factor' column as is shown in the power point slides since excel will calculate that for you. Under the year(8) column, use where there would be a "Now since no time has passed from now. Be sure to input the year(s) or period($) 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 1-5 you will use 'S' 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 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 '1-5', 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. You can use the following format for your incremental cost analysis: Bboa Freightlines, SA Incremental Cost Approach Net Present Value Cakulation Amount of Year(s) Cash Flows Item PV Incremental investment in favor of old truck Additional annual cash operating costs Lost savage value in five years Lost salvare value for old truck now Logic IF statement belongs here GENERAL INFORMATION: Please be sure what you turn in is a unique product. You may ork 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 Initiat) Excel For example: SmithExce12.xixor SmithExcel2.xlsx (depending on which version of Microsoft you are using) USING EXCEL TO CALCULATE PRESENT VALUE (PND LOGICIE STATEMENT INSTRUCTIONSA Present Value Function Instruction 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. AL HEX PV[Osta Block 183,613..0133 -0.2 Rate face look189 03 Pri 13 Type 122000 120000 w the primary the out De worth now the war able to work pad 120000 HO 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 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) EXCEL ASSIGNMENT #2 . Check Figure: NPV $1,112 in favor of overhanling the old truck REQUIREMENTS: Complete the requirements for P12-28 on page 602 of your textbook Do the Total Cost Approach first then, do an Incremental Cost Approach analysis below your Total Cost Approach 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. . A data block and cell referencing is required There is NO What IF part to this assignment DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page Data Block Biboa Freightlines Required Rate or Return Present New Year! Thuck Truck Periods Purchase cost new Overhaul needed now cash flows. Do not use the factor tables as is illustrated in the text, 6. A data block and cell referencing is required. There is NO What IF part to this assignment. DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page. Data Block Bilboa Freightlines 1 Required Rate of Retur . Present Truck New Truck Year! Periods Purchase cost new Overhaul needed now Annual cash operating costs Savage value now Savanje value five years from now Include the total cost approach analysis and the incremental analysis both on one spreadsheet page You can use the format illustrated on the following page for your total cost approach analysis . PV Biboa Freightlines, SA Total Cost Approach Net Present Valle Calculation Amount of Item Year(s) Cash Flows Overhaul old truck Overhaul now Annual cash operating cost Savage value in five years Net Present Value 1 Purchase new truck Purchase cost new Salvage value of old truck now Annual cash operating costs Savage value of new truck in five years Net Present Value Logic IF statement belongs here . Do not include a 'Factor' column as is shown in the power point slides since excel will calculate that for you. Under the year(8) column, use where there would be a "Now since no time has passed from now. Be sure to input the year(s) or period($) 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 1-5 you will use 'S' 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 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 '1-5', 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. You can use the following format for your incremental cost analysis: Bboa Freightlines, SA Incremental Cost Approach Net Present Value Cakulation Amount of Year(s) Cash Flows Item PV Incremental investment in favor of old truck Additional annual cash operating costs Lost savage value in five years Lost salvare value for old truck now Logic IF statement belongs here GENERAL INFORMATION: Please be sure what you turn in is a unique product. You may ork 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 Initiat) Excel For example: SmithExce12.xixor SmithExcel2.xlsx (depending on which version of Microsoft you are using) USING EXCEL TO CALCULATE PRESENT VALUE (PND LOGICIE STATEMENT INSTRUCTIONSA Present Value Function Instruction 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. AL HEX PV[Osta Block 183,613..0133 -0.2 Rate face look189 03 Pri 13 Type 122000 120000 w the primary the out De worth now the war able to work pad 120000 HO 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 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)