Question;EXCEL ASSIGNMENT #3;Fall 2014;Due Date: Wednesday, 12/03/14;Check Figure: NPV $3,952,471 in favor of leasing the land;and building;REQUIREMENTS;Complete Parts 1 and 2 of the Analytical Thinking on page 527;of your textbook.;The incremental cost approach analysis should be below your;total cost approach analysis. They;should both be on the same page.;REQUIRED;COMPONENTS;Cell referencing from a data block page.;Present Value;function must be used to calculate the;present value of the inflows and outflows.;Logic ?IF? statements;(there will be two Logic IF statements) MUST be used to label;the Decision as being in favor of leasing the land & building or in;favor of buying the land & building in the total cost approach and in;the incremental approach.;These are required components for this assignment. If you do not use cell references from;a data block page or the PV and Logic IF functions, you will not receive;credit for this assignment.;There is NO What IF part to this assignment.;SUGGESTIONS;Use the exhibit 11-5 and 11-6 on page 501 as sample templates;for the total cost approach and the incremental cost approach. The exhibits will not be identical to your problem.;Do not include the ?Factor? column 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)? column, for any annuities, use the total;number of years for the annuity instead of the range. For example, the exhibit uses the range;?1-6?, you would have used ?6? in your spreadsheet if this had been your;problem data.;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.;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.;You must use a Data Block area, cell;references and formulas. You can use the;format presented in P11-18A as an example general format. Don?t forget though, you will need to also;add a column for the time period (when the cash inflow or outflow occurs). Also, add a line for the discount rate.;Include a centered heading for each part. There is no date. For example, Part 1 heading might look like;this;Wyndham;Stores;Total;Cost Approach;Net;Present Value Calculation;Don?t forget to show dollar signs at the;top if each column and then again at each of the solutions you calculate.;Include the total cost approach analysis;and the incremental analysis both on one spreadsheet page.;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#3.;For Example: SmithJExcel3.xls or;SmithJExcel3.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 (for single sums) or the Pmt box area (for annuities), 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 incremental cost;approach, the condition is: if the;result is = or > than 0, then have excel type in ?NPV in favor of leasing the land and building? (this is if the;condition is true), otherwise have excel type in ?NPV in favor of buying the;land and building? (this is if the condition is false).;When doing the total cost approach, the condition is: if the PV;of the first option (leasing the land and building) is greater than the PV of;the second option (buying the land and building), then have excel type in ?NPV in favor of leasing the land and building?;(this is if the condition is true), otherwise have excel type in ?NPV in favor;of buying the land and building? (this is if the condition is fales).;An easy way to have Excel help you create;this formula is to use the formula wizard. Click on the ?fx? 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.
Paper#61310 | Written in 18-Jul-2015Price : $28