IENG 423 Design of Decision Support Systems

1. IENG 423Design of Decision Support Systems Modeling with Excel Excel Basics

2. So, let’s imagine… • In the spirit of the election season that we are in… • You are the treasurer for a newly formed political party… • Whose whole platform is to have more parties… • Your new political party is called the … • Party Party

3. You have a fund raising team- ..and, for December they have turned in… 3

4. You need the total contributions So, you can add the money column 4

5. It turns out that not all of the data was in when you start this table • … and Fred thought he could keep part of the money for his own party this weekend 5

6. So, we need to recalculate 6

7. MS Excel Oh, you need to do this for December and January 7

8. So, how do you get started • Like Dennis Hopper says “You need a Plan” • What is the spreadsheet suppose to do? – What is it purpose? -- write it down • What are the givens, the inputs • Are there categories of information that will be part of the spreadsheet? • What outputs do you need? • What are the relationships between the inputs and outputs

9. So, how do you get started • Like Dennis Hopper says “You need a Plan” • Make a sketch – yes on paper! • Is it laid out to be meaningful, usable and useful to - the end user – not you – the end user? • And don’t forget to test it • And test it again

10. So, how do you get started • This does not mean that the plan is “carved in stone” • In fact, Excel is pretty powerful with respect to letting you modify your model after you have created it (usually) • …and it is pretty smart about adapting your model as you make changes (usually)

11. Remember • You are developing your spreadsheet application for a customer, client or boss • Always try to put yourself in their shoes

12. MS Excel • Entering Data • Selecting a Cell • Entering Text • Excel sort of knows • Entering numeric data • Entering numbers as text • Entering Dates

13. MS Excel • Entering arithmetic expressions or formulae • Prefix entry with arithmetic operator • Recommend using = • For example, • Entering an = and a cell reference will enter whatever is in the referenced cell in the current active cell • We will come back to this!

14. MS Excel • Selecting • Cells • Columns • Rows • Contiguous Ranges • Extending selected contiguous ranges • Noncontiguous ranges

15. MS Excel • Selecting • Entire worksheet • Other worksheets

16. MS Excel • Inserting columns in a worksheet • Inserting rows in a worksheet • Adjusting column widths • Drag column edge • Set to specific width • Autofit • Adjusting row heights • Like above

17. MS Excel • Erasing cell data • Delete – tries to move neighboring cells or delete entire row or column • Point to cell and hit space bar – enters a space in cell – will cause cells that use the active cell in a formula to error • Clear Contents – will set cell to have no values

18. MS Excel • Autofill • Will copy active cell contents to other contiguous cells • Will copy formula in active cell to contiguous cells – with adjustments • Extend a series in two or more selected cells

19. MS Excel • Copy and Paste • Copies selected contents to other cells • Copies formulae with adjustments

20. MS Excel • Save • Save As • Save often

21. For Next Time • Read Chapters 3 and 4 of MS Excel 2003 Step by Step • Do some research on importing files • Txt files • Delimited files • Other Excel Workbooks • XML files