Presentation Transcript

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?