Excel Tutorials (MS Excel 2007)

1. Excel Tutorials(MS Excel 2007) • Basic Excel • Data Analysis • Solver • XLSim • Tornado Diagram

2. Excel Basics

3. Excel tutorial: Excel Information • Excel training offered by University Computing Service: http://www.cam.ac.uk/cs/courses/ • Excel handbooks available in JBS Computer Lab • On-Line excel tutorials: http://people.pwf.cam.ac.uk/mmh33/ • Book available from JBS library • B.V. Liengme, A Guide to Microsoft Excel for Scientists and Engineers, Arnold Publisher, London 1997.

4. Excel tutorial: Some tips • Separate data, model (containing many formulas), and results • Ensure that you document and organize your models properly so that others can easily understand your model and repeat your experiments in the future • Use absolute and relative references but not use any numbers in formulas • Ensure the model is scalable so that you can modify it easily • Validate your models carefully • Use Excel building functions such as SUMPRODUCT • Use DATA TABLE which allows you to repeatedly evaluate a particular formula within the model while systematically varying one or two input cells on which the formula depends

5. Excel tutorial: Some basic Excel functions • FILLDOWN for editing efficiency • RELATIVE and ABSOLUTE REFERENCE for creating formulas • IF for logical computations • SUM, MAX, MIN for computations • SUMPRODUCT for scaling-free calculations • RAND, RANDBETWEEN for simulation • DATA TABLE for parametric calculation/simulation • HLOOPUP, VLOOKUP for sampling

6. Excel tutorial: Data Table • Create a table with values of the parameter in one column, say A2:A101 • and copy the value of the output in B1 • Select/highlight the whole table A1:B101 • Invoke DataData ToolsWhat-If AnalysisData Table from Excel menu and you get a dialogue box • Select column input cell, and click the input parameter cell, and then • click OK. You get the whole data table • WARNING: In order for Data Table to work, ensure that option Automatic in FormulasCalculationis ticked. • You could have a Data Table without a parameter in simulation or a Data Table with two parameters in a data table matrix. In the former, you need to choose an empty cell for the column input cell and in the latter, you need to correctly choose cells for both column input cell and row input cell.

7. Excel Data Analysis

8. Excel tutorial: Install Analysis ToolPak • Open Excel • Click the Office Button • Click Excel Options (on the taskbar at the bottom) • When the Excel Options dialog box appears: Select Add-Ins from the list of options (on the pane on the left). • In the Manage box, select Excel Add-Ins (in the next to last row) • Click Go • Select Analysis Toolpak and click OK. You should see Data Analysis in DataAnalysis. • You may need the computer installation CD to finish this task

9. Excel Solver

10. Excel tutorial: Install Solver • Open Excel • Click the Office Button • Click Excel Options (on the taskbar at the bottom) • When the Excel Options dialog box appears: Select Add-Ins from the list of options (on the pane on the left). • In the Manage box, select Excel Add-Ins (in the next to last row) • Click Go • Select Solver and click OK. You should see Data Analysis in DataSolver. • You may need the computer installation CD to finish this task

11. Excel tutorial: Solver- design and build models • Arrange data • Allocate Change Cell • Allocate Target Cell • Allocate Constraint Cells • Assign proper tags • Add proper comments • Add proper colours

12. Excel tutorial: Link to Solver • Go toTools->Solver • Set Target Cell • Equal To • By Changing Cells • Subject to the Constraints: Add, Change, Delete • Set Solver Options

13. Excel tutorial: Solver Options and Solution Reports • Max Time • Iterations • Tolerance • Assume Linear Model • Use Automatic Scaling • Assume Non-Negative

14. Excel tutorial: View Solver Reports • Answer • Sensitivity • Limits

15. Excel tutorial: Solver Enhancements • Insert names: Highlight the area and then insert name • SUMPRODUCT(row_variables, row_constraint_i) • Add comments and proper tags

16. Excel XLSim

17. Excel tutorial: Install Insight • Copy Insight Excel 2007.zip into your C folder. • Unzip Insight Excel 2007.zip. • Installation is accomplished by running Setup.exe

18. Excel tutorial: Install XLSim • Open Excel and Click the Office Button • Click Excel Options (on the taskbar at the bottom) • When the Excel Options dialog box appears: Select Add-Ins from the list of options (on the pane on the left). • In the Manage box, select Excel Add-Ins (in the next to last row) • Click Go • Select XLSim.xla from your Insight folder • Click OK • XLSim will appear in Add-Ins • If XLSim does not work, thenClick the Office ButtonExcel OptionTrust centerTrust Center SettingMacro Setting, and then tick Trust acess to the VBA object model

19. Excel tutorial: Conduct simulation • Go to Simulate  Simulation (You are given an editing window) • Add outputs • Choose simulation size • Click OK • You get another worksheet called Statistic • Go to Simulate  Graphs • Choose Bin Size for histograms • Click histogram to generate a histogram • Click cumulative to generate a CD graph

20. Excel tutorial: Parametric simulation • Determine which parameter for your sensitivity analysis • Select a few values for your parameter and store them in your worksheet • Go to Simulate  Parameterized Sim • You get a new dialog window • Select your output cell and give a name • Select the number of trials • Select the parameter cell used in your model • Give a name for your parameter • Select a range of values for your parameter that you stored before • Save this parametric simulation in a cell

21. Excel tutorial: Parametric simulation • You can save and load parametric simulations • After creating or loading a parametric simulation, click OK for experiments • Go to Simulate  Common Graphs in order to plot either histograms or CD graphs in one diagram. You could have a choice of 3D histograms • By varying parameter values or changing ranges of parameter values, you can conduct and refine your parametric simulation experiments For more detail, see Chapters 2 and 3 of Sam Savage, Decision Making with Insight

22. Excel Tutorial:Tornado Diagrams

23. Excel tutorial: Experiment with Tornado Diagram We use Customer Manag.xls as an example and assume that you have already done your calculations in workbook “Sensitivity”. We want to test how the system output is sensitive to various inputs. • Open Customer Manag.xls • Move to workbook “Sensitivity” • Open Tornado.xls and you see “TM” tab in the Excel menu bar • Go to Add-insTMTornado Diagram and window “Tornado Dialog” pops up

24. Excel tutorial: Experiment with Tornado Diagram • Move your curser to the box underneath “Data Range” • Select range A22:D25, which contains input parameters and the ranges of values in a pre-specified format. The first row of this matrix is for labels: Assumptions, Base, Low, High, each of the remaining rows are for a particular input parameter. For each row other than the first, the first column stores the parameter name, the second, third and fourth store its base, low and high values. • Move your curser to the box underneath “Result Cell” • Select P13, which stores the system output • Click “OK” • We are done!