EXCEL ASSIGNMENT #3 Fall 2014

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.
.jpg”>

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.