Spreadsheet Design & Simplicity

1. Spreadsheet Design& Simplicity Chapter S1: Introduction to Spreadsheet Design S2: Simplicity All Sections

2. Today’s Topic Security Hardware & Software IS Basics Information Quality Types of IS Telecommu-nications Systems Development PowerPoint Functional, Crossfunctional & Interorganizational Database Processing & Design Spreadsheet Design E-commerce Access Excel Business Intelligence

3. Simplicity Spreadsheet Design Efficiency Simplicity includes Clarity Accuracy Layout Choices Formulas Functions Macros Verifiability Formulas Functions Basic Formatting Conditional Formatting Freeze Panes Page Setup Sorting Charts implemented with Documentation Data Validation Protection

4. Objectives • Explain the distinction between spreadsheet design and implementation skills. • Explain the importance of spreadsheet design skills. • List the five spreadsheet design goals. • Define the simplicity goal. • Apply the spreadsheet design goal of simplicity.

5. Excel Prerequisites • Entering Data • Change Fonts • Number Formats • Border • Theme • Printing • Open, Close, Save Workbook • Insert & Delete Worksheet • Move & Copy Worksheet • Rename Worksheet • Insert & Delete Rows • Column Width & Row Height

6. Required Excel Skills • Page Break • Print Titles • Sort • Group • Data Validation • Worksheet Protection • Cell Shading • Wrap Text • Rename Worksheet • Comment • Freeze Panes • Conditional Format • Header & Footer

7. Why Study Spreadsheet Design? • Over 50% of all spreadsheets created by professionals contain major errors

8. What are the Different Types of Spreadsheets? • Throw-away • Reusable

9. What is the Spreadsheet Lifecycle? • Development • Design • Implementation • Use

10. What are the Five Spreadsheet Design Goals? • Simple • Clear • Verifiable • Accurate • Efficient

11. How Can I Achieve Simplicity? • Include only one idea per row and one idea per column. • Data that will be analyzed as a unit should be placed in columns rather than rows.

12. One Idea?

13. One Idea?

14. Rows vs. Columns?

15. Simple? Planning Your Pathways. VT. 2011. <http://www.uaac.vt.edu/planner/ExcelTemplate.xls>

16. Simple? VT Football. VT. 2011. <http://www.hokiesports.com/football/stats/2010/?gbg

17. Simple? VT Football. VT. 2011. <http://www.hokiesports.com/football/stats/2010/?gbg

18. Simple? Detail Report: Salaries Reported. VT Career Services. 2011. <http://www.career.vt.edu/PostGraduationReport/DetailReportSalaries.asp?College=00&Majors=N&Cohort=2009-2010>

19. Simple? Detail Report: Salaries Reported. VT Career Services. 2011. <http://www.career.vt.edu/PostGraduationReport/DetailReportSalaries.asp?College=00&Majors=N&Cohort=2009-2010>

20. Simple? Detail Report: Bonuses Reported. VT Career Services. 2011. <http://www.career.vt.edu/PostGrad2006/DetailReportBonuses.asp?College=00&Majors=Y&Cohort=2008-2009>

21. Simple? Detail Report: Employment related to career goals & major. VT Career Services. 2011. <http://www.career.vt.edu/PostGraduationReport/DetailReportEmploymentRelated.asp?College=00&Majors=Y&Cohort=2009-2010>

22. Class Exercise #1: Stock Portfolio • Design a spreadsheet that could be used to track the performance of each stock in your portfolio. • You want to record each stock’s NYSE ID, purchase price and quarterly closing price for one year. (That means four closing prices.) • At the completion of the year, you want to calculate the change in price for each stock. • You want to identify the highest priced stock in your portfolio as well as the stock that performed the best over the year.

23. Stock Portfolio Activity • STEP 1: What are the data items you will need to include in your spreadsheet? • STEP 2: Should each stock be a row or a column? • STEP 3: Where do the calculated items fit in?

24. Portfolio Example: Step 1 • What are the data items you will need to include in your spreadsheet?

25. Portfolio Example: Step 2 • Should each stock be listed as a row or a column?

26. Portfolio Example: Step 3 • Where do the calculated items fit in your previous layout?

27. Class Exercise #2: GPA • Design a workbook that could help you calculate your overall GPA for the current semester. • Try to achieve the Simplicity goal.

28. GPA Example: Background • Each letter grade is assigned a weight • A is weighted with a 4 • B is weighted with a 3 • C is weighted with a 2 • D is weighted with a 1 • F is weighted with a 0

29. GPA Example: Background • That weight is multiplied by the credit hours for the course • A three credit course in which you earn an A is weighted 12 (3 credits x weight of 4) • A three credit course in which you earn a D is weighted 3 (3 credits x weight of 1) • A two credit course in which you earn an A is weighted 8 (2 credits x weight of 4)

30. GPA Example: Background • These weighted credits are divided by Total number of credits completed to determine GPA • BIS course is worth 3 credits earned A = 12 weighted credit hours • LIT course is worth 3 credits earned C = 6 weighted credit hours • Total weighted credit hours = 18 divided by Total number of credits completed = 6 yields a GPA of 3.0

31. GPA Activity • STEP 1: What are the data items you will need to include in your spreadsheet? • STEP 2: Should each class be a row or a column? • STEP 3: Where do the calculated items fit in?

32. GPA Example: Step 1 • What are the data items you will need to include in your spreadsheet?

33. GPA Example: Step 2 Should each class be listed as a row or a column?

34. GPA Example: Step 3 • Where do the calculated items fit into your prevous layout?