1 / 36

Day 10: MICROSOFT EXCEL – CHAPTER 8 MICROSOFT EXCEL – CHAPTER 9 MICROSOFT EXCEL – CHAPTER 10

Day 10: MICROSOFT EXCEL – CHAPTER 8 MICROSOFT EXCEL – CHAPTER 9 MICROSOFT EXCEL – CHAPTER 10. Akhila Kondai akhila.kondai@mail.wvu.edu September 23, 2013. Announcements. Homework # 3 is due on 09/27/2013 by 11.59pm. What – if analysis. Data Table Goal Seek Scenario Manager.

elisha
Télécharger la présentation

Day 10: MICROSOFT EXCEL – CHAPTER 8 MICROSOFT EXCEL – CHAPTER 9 MICROSOFT EXCEL – CHAPTER 10

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. Day 10:MICROSOFT EXCEL – CHAPTER 8MICROSOFT EXCEL – CHAPTER 9MICROSOFT EXCEL – CHAPTER 10 AkhilaKondai akhila.kondai@mail.wvu.edu September 23, 2013

  2. Announcements • Homework # 3 is due on 09/27/2013 by 11.59pm

  3. What – if analysis • Data Table • Goal Seek • Scenario Manager

  4. WHAT-IF ANALYSIS • What if analysis is the process of changing variables to observe how changes effects calculated results. • A variable is an input value that can change to other values to affect the results of a situation.

  5. Data table • One variable Data Table • Two variable Data Table

  6. Creating a one variable data table • A data analysis tool that provides various results based on changing one variable. • 3 steps • Enter Substitution values for a one-variable data table • Enter formulas and complete the data table • Format the one-variable data table

  7. Substitution values… • Click cell D4, enter 4% • Home ribbon -> Editing group -> Fill -> Series • Select column radio button, and give Step value box value as 0.25% and Stop value box value as 6% • Format the range D4:D12 to percentages with 2 decimal points.

  8. Completing the data table… • E3->B12 : F3->B13 : G3->B14 • Select D3:G12 • Under DATA TAB go to What-If Analysis -> Data table • Give column input cell as $B$4 and click OK

  9. Format the table… • Select range E4:G12 and give the number format as Accounting with 2 decimal points • Enter APR in cell D3 • Select cell E3  Format cells  Select Custom  General  type “Payment” and click OK. • F3 as Total Repaid and G3 as Total Interest.

  10. Creating a two variable data table • A data analysis tool that provides various results based on changing two variables. • 2 steps • Set up the structure for a Two-Variable data table • Complete the Two-Variable Data Table

  11. Data table structure… • Different “cost of car” in row J3:L3 • Enter 20000,25000,30000 • Different APR in column I4:I20 • Increasing values from 4% to 6% at a step value od 0.125% • Format I4:I20 as Percent style with 3 decimal points • Specify which variation do you want to observe (eg. Monthly Payment B12) in cell I3

  12. Completing the table... • Select I3:L20 • Under DATA TAB go to What-If Analysis -> Data table • Give B2 as row input cell box and B4 as column input cell box. • Format cell I3 to display APR

  13. Goal seek

  14. Create a Goal seek • Goal seek is a tool that identifies the necessary input value to obtain a desired goal. • What If Analysis ->Goal seek • Set cell box  B12 • Enter 300 in To Value box • By changing cell box  B2 • Click OK to accept the solution

  15. Scenario manager

  16. SCENARIO MANAGER • 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.

  17. Using scenario manager • Create and edit Scenarios • View Scenarios • Generate Scenario Summary Report

  18. Create a Scenario • What-If Analysis -> Scenario manager • Click Add • Scenario name : Best-case • Changings cell : B2:B5 • Enter : 25000 in $B$2 , 5000 in $B$3, 0 in $B$4, 6 in $B$5 • Create Additional Scenarios • Click Add • Scenario name : Most Likely-case • Changings cell : B2:B5 • Enter : 22500 in $B$2, 6500 in $B$3, 4.25% in $B$4, 5 in $B$5

  19. Create A summary report • What-If Analysis Scenario manager  Summary • Result cells box : Range B12:B14 • Click OK

  20. solver • Solver is an add-in application that manipulates variables based on constraints to find the optimal solution to a problem. • Solver is one of the most sophisticated what-if analysis tools, and people use Solver in a variety of situations and industries.

  21. Loading the solver add-in • Go to file tab • Select options  Add-ins • Manage->Excel Add-ins and click GO • Select Solver Add-in • You can see Solver option in Data ribbon

  22. Optimizing results with solver • Identify Objective cell, contains the formula-based value that you want to maximize, minimize. Or set to a value in Solver. • Identify Changing cells, contains a variable whose value changes until Solver optimizes the value in the objective cell

  23. SOLVER EXAMPLE • Step 1: Set the Objective and Variable cells • Step 2: Define Constraints • Step 3: Generate a report

  24. Step 1… • Enter 25000 in B2 and 5000 in B3 • Define objective cell and changing variable cells. • Objective cell -> B12 • To value off : 300 • By changing variable cells : B2:B5

  25. Step 2… • Define constraints • B2<=30,000 • B2>=20,000 • B3<=7,500 • B3>=5,000 • B4<=6% • B4>=4% • B5<=6 • B5=integer • B5>=4

  26. Step 3… • Generate a report. • Click on SOLVE • Select ANSWER in the REPORT LIST • Observe the result in Answer Report 1 worksheet tab.

  27. Multiple-sheet workbook management

  28. Grouping and ungrouping worksheets • All worksheets • Adjacent worksheets use SHIFT • Non Adjacent worksheets use CONTROL

  29. Worksheet reference • Pointer to a cell in another worksheet • Syntax : ‘Worksheet_name’!Range_Of_Cells • Select cell B1 in sheet 1 and calculate the average for cells J4:J20

  30. 3d formula • Formula or function that refers to the same range in multiple worksheets. • Select cell B2 in sheet 1 • Type =SUM( • Click sheet 2, press and hold shift and then click sheet 3 and select cell A1 • The formula becomes =SUM(Sheet2:Sheet3!A1)

  31. Inserting hyperlinks • Hyperlink is an electronic marker to another location in a worksheet, workbook, file, web page or email. • Select B1  Insert ribbon  Hyperlink in the Links group • Type J4:J20 in the Type the cell reference box and click OK.

  32. Templates, themes and styles

  33. Selecting a template • Template is a special workbook file used as a model to create similarly structured workbooks • File -> New • Search for template • Select one • Preview of selected template

  34. Applying themes and backgrounds • A theme is a collection of colors, fonts, and special effects. • Page Layout ribbon • Themes • Select one • A background is an image that appears behind the worksheet data onscreen • Page Layout ribbon • Background -> select desired file

  35. Applying cell styles • A cell style is a set of formatting options applied to worksheet cells • Home ribbon • Styles -> Cell styles

  36. Questions ?

More Related