Akhila Kondai akhila.kondai@mail.wvu September 23, 2014

1. Day 11:MICROSOFT EXCEL – CHAPTER 7 CONTD.MICROSOFT EXCEL – CHAPTER 6MICROSOFT EXCEL – CHAPTER 9MICROSOFT EXCEL – CHAPTER 12 AkhilaKondai akhila.kondai@mail.wvu.edu September 23, 2014

2. Reminder • The Homework # 3 is due on September 26th, 2014, Friday

3. Microsoft Excel – Chapter 7 CONTD.

4. Specialized functions • Math and Statistical Functions • Logical and Lookup Functions • Database Functions • Financial Functions

5. Database functions • DSUM and DAVERAGE • DMAX and DMIN • DCOUNT

6. DSUM AND DAVERAGE • DSUM function adds the values in a numeric database column based on conditions you specify in a range Syntax: DSUM(database, field, criteria) • DAVERAGE function determines the arithmetic mean, or average of numeric entries in database column that match conditions you specify Syntax: DAVERAGE(database, field, criteria)

7. DMAX and dmin • DMAX function identifies the highest value in a database column that matches conditions you specify Syntax: DMAX(database, field, criteria) • DMIN function identifies the lowest value in a database column that matches conditions you specify Syntax: DMIN(database, field, criteria)

8. DCOUNT • DCOUNT function counts the cells that contain numbers in a database column that match conditions you specify Syntax: DCOUNT(database, field, criteria)

9. FINANCIAL functions • IPMT and PPMT • CUMIPMT and CUMPRINC • PV and FV

10. Calculate interest and principal payments • IPMT function calculates periodic interest for a specified payment period on a loan or investment given a fixed interest rate, term and periodic payments Syntax: IPMT(rate,per,nper,pv,[fv],[type]) • PPMT function calculates the principal payment for a specified payment period on a loan or investment given a fixed interest rate, term, and periodic payments. Syntax: PPMT(rate,per,nper,pv,[fv],[type])

11. Calculate cumulative interest and principal payments • CUMIPT function calculates cumulative interest for specified payment periods Syntax: CUMIPMT(rate,nper,pv,start_period, end_period) • CUMPRINC function calculates cumulative principal for specified payment periods Syntax:CUMPRINC(rate,nper,pv,start_period, end_period)

12. Calculate present and future values • PV function calculates the present value of an investment Syntax: PV(rate,nper,pmt,[fv],[type]) • FV function calculates the future value of an investment Syntax: FV(rate,nper,pmt,[pv],[type])

13. Microsoft Excel – Chapter 6

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

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

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

17. one-variable data table • A one-variable data table ─ a data analysis tool that provides various results based on changing one variable • A substitution value ─ replaces the original value of a variable in a data table

18. Create a One-Variable Data Table • List substitution values in the left column or first row • Enter formulas in the first row or left column (whichever was not used above) • Create the one-variable data table • Format the results of the data table • Create custom number formats to disguise the formulas as headings

19. One-Variable Data Table To complete a one-variable data table: • Select entire table starting in the blank cell in the top-left corner • Click What-If Analysis in the Data Tools group on the Data tab and select Data Table • Enter address of the cell to be changed in the Data Table dialog box • Click OK

20. One-Variable Data Table

21. Two-Variable Data Table • A two-variable data table ─ a data analysis tool that provides results based on changing two variables • Creating a two-variable data table ─ similar to creating a one variable data table; however, you are limited to comparing one result. • Recommendations include: • Use the top row for one variable’s substitution values • Use the first column for the other variable’s values • Apply a custom number format to the formula cell in the top-left cell

22. Two-Variable Data Table

23. Goal seek

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

25. Scenario manager

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

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

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

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

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

32. Step 1: Define objective cell and changing variable cells. • Step 2 : Define constraints • Step 3 : Generate a report. • Click on SOLVE • Select ANSWER in the REPORT LIST • Observe the result in Answer Report 1 worksheet tab.

33. Microsoft Excel – Chapter 9

34. Multiple-sheet workbook management

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

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

37. 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)

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

39. Microsoft Excel – Chapter 12

40. Templates, themes and styles

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

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

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

44. Questions ?