1 / 46

Advanced Excel for Finance Professionals

Advanced Excel for Finance Professionals. A self study material from South Asian Management Technologies Foundation. Welcome Back to Session 4. Session 6: What-if. What If Analysis GOAL SEEK SCENARIO DATA TABLE SOLVER. Goal Seek.

miriam
Download Presentation

Advanced Excel for Finance Professionals

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. Advanced Excel for Finance Professionals A self study material from South Asian Management Technologies Foundation

  2. Welcome Back to Session 4

  3. Session 6: What-if • What If Analysis • GOAL SEEK • SCENARIO • DATA TABLE • SOLVER

  4. Goal Seek • We have a budget scenario and look for the input value of a cell if we want to get a specific value in output cell. • Price for a Shirt if target total profit is 90000

  5. Invoking Goal Seek • Invoke Goal Seek from What-if-Analysis menu under Data tools. • Provide the specified value

  6. The Solution • The solution is provided. If we press OK the displayed values will be stored.

  7. Scenario • Allows us to keep multiple values of same table logic with different values in same worksheet range • We do not need to create multiple tables • Most useful when you use only one set of value i.e. the multiple sets of data are mutually exclusive

  8. Creating a Scenario • We have a simple profit budget. • We are often required to create budget for different sales level

  9. Creating a Scenario • Select Add Option under Scenario Manager under What-if-Analysis Option from Data

  10. Specifying the Scenario • Provide a scenario name and cell(s) which you want to change. • Keep prevent change option selected

  11. Provide values for Scenario • Create a scenario with original values before you create a scenario with changed values

  12. Recalling a Scenario • Recall the scenario from What-if-Analysis and select show. • Get the old values back by selecting original scenario

  13. Scenario Summary • This option provides a summary view of data input and output

  14. Data Table • Data table defines a range of data as a group and allows manipulating them in some predefined ways irrespective of the data lying outside the table. • Most of the functions that can be performed on a table can be performed on the range but mostly in a more complicated manner • You can disassociate the range from table functionality without deleting data

  15. Create a Table • We can create a table in two ways • Insert a table • Convert a range to table by using Style option in Home menu • The formatting and filter option comes about automatically • Check if you have headers in your table

  16. Table Created • The table functions are visible once you select any cell inside the table • You can name the table

  17. Remove Duplicate Values • Specify the columns on which you will search for duplicates • Enter a data outside table in same rows with duplicate value and remove duplicate

  18. Convert Back to Range • Just click on any cell and activate the table menu option • Select Convert to Range

  19. Solver • A powerful tool that solves for a constraint driven optimisation model. • For example necessary sales quantity for a target profit subject to limited demand

  20. Start Solver • Solver is a component of Analysis group under Data option. You may need to install it. • Provide for the parameters

  21. Major Solver Parameters • Initial components are like Goal Seek • Set Objective: The cell value that you want to • To: Maximise, minimise, or provide a value • By Changing: The input cell ranges • The Constraints • Identify the constraint cell • Provide the logical operator • Define the value for the constraint cell

  22. Providing Solver Parameters • We want to, maximise profit by changing units sold subject to maximum demand

  23. Add a Solver Constraint • Provide values for all constraints by adding every constraint

  24. The Solver Model

  25. The Solver Solution • Optimal solution provided, replace values with optimal solution or restore original values

  26. Session 6: Special Features

  27. Special Features • External data • Text to columns

  28. Using External Data • Excel can import data from various sources and allow users to analyse the data using Excel functions • Data source may be Access, Webpage, text, or other database

  29. Data from Web • Select From Web option under Get External Data and specify the web page • Select table in webpage you want to import

  30. Imported Data • The data is now in the worksheet • Right click on any cell and see the web query • You can refresh the same.

  31. Text to Column • This is a standard function for importing large text data into various columns depending on the structure of the data. • You can copy a line containing a string where each item is separated by some common element like space, then use this function to convert it.

  32. Text to Column • Invoke the menu from Data option and follow the on-screen direction • Specify data type for each column

  33. Text to Column • Here is the formatted data

  34. Session 7: Macros

  35. Session 8: Macros • These are sequence of steps that we need to perform regularly. • Hence we automate the process by recording these steps and playing them back whenever necessary.

  36. Setup for Macro • If you do not see the Developer ribbon • Click on File Menu (top left) • Go to Options • Select “Customize Ribbon” • Ensure “Developer tab” is checked • Click ok.

  37. Record a Macro • Click on Record Macro • Complete the box

  38. Record a Macro • Go to HOME menu and colour the cell red • Go to DEVELOPER menu and stop recording • Check the macro – press Ctrl-Shift-P • Current cell will become red

  39. Create a Macro Button • Go to INSERT menu and create a rectangle anywhere in the sheet. • Put a text there, say “Red Painter”

  40. Assign the Button Macro Function • Right click the rectangle and assign macro • Now whenever you click the button, the selected cell area will become red!

  41. Macro Exercise • Create a button for computing average for values in ten cells located above the cell where average will be printed.

  42. Session 8: Word Integration

  43. Integrating Word • You can link a table / cell in a Excel document into a Word document. • Once it is linked, any change in the worksheet will automatically update the word document. • Very useful for creating automatic reports

  44. Word Integration • Open Word document and copy a Excel table; Select Link & Use Destination Style

  45. Embedding Worksheet • You have to have Excel and Word file together for continuous updation. • You can embed excel file– this will increase size of word file but excel file can be edited

  46. Thank You!knowledge@south-asian.org

More Related