1 / 49

Tip of the day: Macs are not more secure than pcs.

Tip of the day: Macs are not more secure than pcs. Day 10: Excel Chapter 7. Cody Cutright CS 101 September 18, 2013. Data Analysis Toolpak. The Analysis ToolPak is an add-in program that contains tools for performing complex statistical analysis. How do I use it?.

awena
Download Presentation

Tip of the day: Macs are not more secure than pcs.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tip of the day:Macs are not more secure than pcs.

  2. Day 10:Excel Chapter 7 Cody Cutright CS 101 September 18, 2013

  3. Data Analysis Toolpak The Analysis ToolPakis an add-in program that contains tools for performing complex statistical analysis.

  4. 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.

  5. File Tab -> Options

  6. Select Add-Ins on the Left Side

  7. Make sure Excel Add-Ins is selected

  8. Select the Analysis ToolPak ***Make sure the Analysis ToolPak is selected, not Analysis ToolPak VBA.

  9. 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

  10. Variety of choices

  11. Microsoft Help

  12. 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.

  13. IPMT • The IPMT function calculates periodic interest for a fixed-term, fixed-rate loan or investment. • = IPMT(rate,per,nper,pv,[fv],[type])

  14. 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.Witha 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 nperargument 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.

  15. 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.

  16. 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

  17. 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.

  18. CUMPRINC • The CUMPRINC function calculates cumulative principal for specified payment periods. • =CUMPRINC(rate,nper,pv,start_period,end_period) • **Same argument types as CUMIPMT

  19. PV • The PV function calculates the present value of an investment. • =PV(rate,nper,pmt,[fv],[type]) • Required arguments • rate • nper • pmt

  20. 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.

  21. FV • The FV function calculates the future value of an investment. • =FV(rate,nper,pmt,[pv],[type]) • Required arguments • rate • nper • pmt

  22. 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.

  23. 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…

  24. Interesting sidebar $598,905.34!

  25. 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

  26. 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 multiplyingthe periodic rate by 12: 5.25%.

  27. Excel Chapter 8 Cody Cutright CS 101 February 10th, 2014

  28. 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!

  29. Goal Seek Steps • Data Tab -> Data Tools group -> What-If Analysis • Select Goal Seek • Enter the cell to be optimized in the Set cell box (this cell must contain a formula) • Enter the result you want to achieve (the goal) in the To Value box

  30. Goal Seek Dialog Box

  31. 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.

  32. 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.

  33. Scenario Manager Data Tab -> Data Tools Group What-If Analysis -> Scenario Manager

  34. Add Scenario If there may be numerous scenarios, the names become important. Make the names descriptive but short!

  35. Set Cell Values for Scenario Then either select OK to save it, or Add to create more.

  36. Viewing Scenarios Scenario Manager -> Select the scenario -> Click Show Excel will place the defines values in their respective cells, and display the results.

  37. Scenario Summary Report A scenario summary report is an organized structured table of the scenarios, their input values, and their respective results.

  38. To generate: • Open the Scenario Manager dialog box. • Click Summary to open the Scenario Summary dialog box. • 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. • Click OK

  39. Example

  40. 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.

  41. Loading Solver (Similar to Analysis ToolPak) • File Tab -> Options • Add-Ins • Manage -> Excel Add-Ins • Solver Add-In

  42. 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.

  43. Solve Parameters Dialog Box Cell containing formula to optimize its value Type of objective Variables to optimize objective Constraints

  44. 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.

  45. 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.

  46. Solver Results!

  47. 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

  48. 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 youwant to compare results

  49. Save/Restore – Cont’d • Solver Parameters Dialog Box • Load/ Save • Click a blank cell in the worksheet to save the data to • Click Save Restoring (Loading) is the opposite, Load -> Select Cell -> Load

More Related