Transcript Day 8: Excel Chapter 6

TIP OF THE DAY:
MACS ARE NOT MORE
SECURE THAN PCS.
DAY 11:
EXCEL CHAPTER 7
Cody Cutright
CS 101
September 22nd, 2014
Data Analysis Toolpak
The Analysis ToolPak is an add-in program
that contains tools for performing complex
statistical analysis.
How do I use it?
If the ToolPak is installed, it must be loaded
into Excel before it can be used.
The following slides will guide us through
turning on the ToolPak.
File Tab -> Options
Select Add-Ins on the Left Side
Make sure Excel Add-Ins is
selected
Select the Analysis ToolPak
***Make sure the Analysis
ToolPak is selected, not
Analysis ToolPak VBA.
Using the ToolPak
Now that the ToolPak is enabled, to utilize it
for data analysis follow these steps:
• Data Tab -> Analysis Group -> Data
Analysis
• Select the desired tool
• Enter the parameters and click OK
Variety of choices
Microsoft Help
Advanced Financial Functions
The following functions are extremely useful in financial
planning and goal setting, even as students. The use of these
functions should be especially considered for student loan
situations!! I highly recommend that while these functions are
fresh in your mind you create an Excel sheet for your own
personal use, on your own time of course.
IPMT
• The IPMT function calculates periodic
interest for a fixed-term, fixed-rate loan or
investment.
• = IPMT(rate,per,nper,pv,[fv],[type])
IPMT Arguments
• Rate. The rate argument is the periodic interest rate. If the APR is 5.25% and
monthly payments are made, the rate is 5.25%/12, or 0.438%.
• Per. The per argument is the specific payment or investment period to use to
calculate the interest where the first payment period is 1. If you include a
payment number column as in Figure 7.29, you can use a relative cell
reference to avoid having raw numbers in the argument.
• Nper. The nper argument represents the total number of payment or
investment periods.With a four-year loan consisting of monthly payments, the
nper is 48. You should perform the calculation using the input cells, such as
B$4*B$5, in the nper argument instead of typing the value 48 in case the
number of years or number of payments per year changes.
• Pv. The pv argument represents the present value of the loan or investment.
Enter a minus sign in front of the cell reference to avoid having a negative
interest payment returned. In this example, pv would be –B$2.
IPMT Optional Arguments
• Fv. The optional fv argument represents the future
value of the loan or investment. If you omit this
argument, Excel defaults to 0. For loan payments,
the balance should be zero after you pay off your
loan.
• Type. The optional type argument represents the
timing of the payments. Enter 0 if the payments
are made at the end of the period, or enter 1 if the
payments are made at the beginning of the period.
If you omit this argument, Excel assumes a default
of 0.
PPMT
• The PPMT function calculates the
principal payment for a specified payment
period given a fixed interest rate, term,
and periodic payments.
•
=PPMT(rate,per,nper,pv,[fv],[type])
• Same four required arguments as IPMT
CUMIPMT
• The CUMIPMT function calculates
cumulative interest for specified loan
payments.
• =CUMIPMT(rate,nper,pv,start_period,end_
period,type)
• Rate,nper,pv, and type work as before.
CUMPRINC
• The CUMPRINC function calculates
cumulative principal for specified
payment periods.
• =CUMPRINC(rate,nper,pv,start_period,e
nd_period)
• **Same argument types as CUMIPMT
PV
• The PV function calculates the present
value of an investment.
• =PV(rate,nper,pmt,[fv],[type])
• Required arguments
• rate
• nper
• pmt
PV – Cont’d
• Rate, nper, and type have the same
definitions as before.
• pmt: The fixed periodic payment
• fv: The future value of the investment
• ***If you omit the pmt argument, you
must enter a value for the fv argument.
FV
• The FV function calculates the future
value of an investment.
• =FV(rate,nper,pmt,[pv],[type])
• Required arguments
• rate
• nper
• pmt
FV – Cont’d
• Rate, nper, and type have the same
definitions as before.
• pmt: The fixed periodic payment
• pv: The future value of the investment
• ***If you omit the pmt argument, you
must enter a value for the pv argument.
Interesting sidebar
• Assume that you plan to contribute $3,000
a year to an IRA for 40 years, and that you
expect the IRA to earn 7% interest
annually.
• You will have contributed $120,000
($3,000 a year for 40 years).
• At age 65 you’’ll have… wait for it…
Interesting sidebar
$598,905.34!
Two other useful functions
• The NPER function calculates the
number of periods for an investment or
loan.
• =NPER(rate,pmt,pv,[fv],[type])
• You can use NPER to calculate the number of monthly
payments given a car loan of $30,000, an APR of
5.25%, and a monthly payment of $694.28.
About 48.0001 payments
Two other useful functions
• The RATE function calculates the
periodic rate for an investment or loan.
• =RATE(rate,pmt,pv,[fv],[type])
• You can use RATE to calculate the periodic rate of a
four-year car loan of $30,000 and a monthly payment of
$694.28. The periodic rate would be 0.44%. Keep in
mind that this is the periodic or monthly rate. The APR
(annual percentage rate) is then found by multiplying
the periodic rate by 12: 5.25%.
EXCEL CHAPTER 8
Cody Cutright
CS 101
September 22nd, 2014
Goal Seek
Goal Seek is a tool that identifies the
necessary input value to obtain a desired goal.
Essentially, goal seek works backwards from
your desired result to show what would make
that occur.
*Goal Seek manipulates only one variable and
one result!
Goal Seek Steps
1. Data Tab -> Data Tools group -> What-If
Analysis
2. Select Goal Seek
3. Enter the cell to be optimized in the Set
cell box (this cell must contain a formula)
4. Enter the result you want to achieve (the
goal) in the To Value box
Goal Seek Dialog Box
Scenario Manager
A scenario is a set of values that represent
a possible situation.
Scenario Manager enables you to define
and manage scenarios to compare how they
affect results.
Create and Edit Scenarios
Before you start the Scenario Manager:
Identify cells that contain the variables you
want to change or manipulate.
Ex: For a car loan you might want to
manipulate: cost, down payment, interest
rate, loan duration.
Scenario Manager
Data Tab -> Data Tools
Group
What-If Analysis ->
Scenario Manager
Add Scenario
If there may be
numerous scenarios,
the names become
important. Make the
names descriptive
but short!
Set Cell Values for Scenario
Then either
select OK to
save it, or Add to
create more.
Viewing Scenarios
Scenario Manager -> Select the scenario
-> Click Show
Excel will place the defines values in their
respective cells, and display the results.
Scenario Summary Report
A scenario summary report is an
organized structured table of the scenarios,
their input values, and their respective
results.
To generate:
1. Open the Scenario Manager dialog box.
2. Click Summary to open the Scenario
Summary dialog box.
3. Select either Scenario summary or
Scenario PivotTable report. Enter the
references for the cell(s) whose values
change in the scenarios in the Result
Cells box.
4. Click OK
Example
Solver
Solver is an add-in application that
manipulates variables based on constraints
to find the optimal solution to a problem.
*This is one of the most sophisticated what-if
analysis tools, and people use Solver in a
variety of situations and industries.
Loading Solver
(Similar to Analysis ToolPak)
•
•
•
•
File Tab -> Options
Add-Ins
Manage -> Excel Add-Ins
Solver Add-In
Before Solving: Identify
Objective/Changing Cells
The objective cell is the cell that contains
the formula-based value that you want to
maximize, minimize, or set to a value in
Solver.
A changing variable cell is a cell containing
a variable whose value changes until Solver
optimizes the value in the objective cell.
Solve Parameters Dialog Box
Adding Constraints
Constraints are often
going to be
determined by the
business you’re
working for, such as
the price of a good or
service.
Create a Solver Report
• Click Solve in the Solve Parameters
Dialog Box
• Click Keep Solver Solution to keep the
changed objective and variable values, or
click Restore Original Values
• Select a report from the Reports list.
• Click OK to generate the summary.
Solver Results!
46
Customize Solver
• Solver is a mathematical modeler
– The trial solutions can be monitored as they
are attempted, following Solver’s steps
– Solver Parameters Dialog Box -> Options
– Select Show Iteration results -> OK
– Click Solve
– When Show Trial Solution appears:
Click Stop or Continue
47
Save/Restore a Solver Model
• Saving a Solver Model saves the objective
value, changing variable cells, and the
constraints
• Useful if the original data source changes
and yo uwant to compare results
48
Save/Restore – Cont’d
1. Solver Parameters Dialog Box
- Load/ Save
2. Click a blank cell in the worksheet to save
the data to
3. Click Save
Restoring (Loading) is the opposite, Load ->
Select Cell -> Load
49