Spreadsheet Design & Assignment 1

1. Spreadsheet Design & Assignment 1 Based on: M. Banarjee, Personal Communication, 2008 J. Nevison, The Elements of Spreadsheet Style, Prentice-Hall, 1987

2. Good character • Easy to build • Easy to read • Easy to use • Easy to change • Error free Spreadsheet Properties

3. Example of Poor Spreadsheet

4. Introduction: • Introduction, title, description, and contents • Model: • Main form data • Data Dictionary • Explains columns and calculations • Data: • Data used in your sheet • Dashboard • Visual reports (charts) Contents of a Spreadsheet

5. The actual form is determined by the purpose of the spreadsheet • What is the function of the sheet? • Sections should be in different ‘tabs’ (sheets) • Especially for larger examples Notes

6. Create a dashboard in Excel that summarizes the complex statistical data collected from students on their movie preferences • The goal is to visualize all the information in the spreadsheet in a meaningful way • that extracts knowledge about what each component says about the students and movies Assignment 1

7. The Dashboard: Design 3 charts • to summarize the data with appropriate titles, legends, and annotations • Include a few sentences interpreting each chart • Make your information dashboard clear, concise, and informative • using good visual design principles Assignment 1

8. Assignment 1 - Tabs

9. How/where do you watch movies? • TV cable • Home DVD • Youtube (or any other streaming source) • PC Download • Theatre • Rate from 1 to 5 Assignment 1 Raw Data

10. Which category of movies do you watch most? • Action • Documentary • Science fiction • Horror • Comedy • Animated • Romance • Rate from 0 to 4 Assignment 1 Raw Data

11. Which of these movies have you seen? • (list of few movies) • Answers are Y or N Assignment 1 Raw Data

12. List up to three film types that you watch beginning with the one you mostly prefer. • (A movie type can be: America, European, Hindi, Chinese, or any other type that you like). Assignment 1 Raw Data

13. Make a formal introduction • Title of the sheet must be informative • Declare the purpose • Give clear instructions • Reference critical ideas • Map the content Intro Section – Design Tips

14. Intro section should give the user a clear idea of how the sheet ties with the real world • Intro devices: • Include a title that passes critical info • Declare the purpose • Give directions on how to use the model • Include references • Include a table of contents Intro – 1. Make a formal intro

15. This and the following examples are from an older 203 assignment Example Intro

16. Title must at least include: • Name of the model • Date • Name of creator • Properties of a title: • Short • Apt (to the point) • Memorable Intro – 2. Informative Title

17. Tell the spreadsheet user what the spread sheet is all about • For instance, the purpose of your assignment 1 spreadsheet could be: Analyzing movie preferences and watching habits of the computer science 203 class, namely blah blah … Intro – 3. Declare the Purpose

18. Example Intro

19. Give clear step-by-step Instructions on how to use the spreadsheet • Especially important if the user needs to later make changes to the data or calculations Intro – 4. Give Instructions

20. Example Intro

21. Cite all resources used to create the spreadsheet if any • Use APA or other format for citation: • J. Nevison, The Elements of Spreadsheet Style, Prentice-Hall, 1987 • A Table of Contents (TOC) can be a good idea too Intro – 5 & 6. Refs and TOCs

22. Model includes assumptions and calculations • Tips for a good model section: • Explain the model • Point to the right source Model Section

23. Should provide three levels of explanation: • Explain the values appearing in the model • Explain tricky formulas • A complete listing of all used formulas Model – Explain it

24. Example Model

25. Example Model

26. Example Model

27. Example Model

28. Explains the meaning of your data • Give each field: • A type: categorization, raw data, column calculation, row calculation • A data type: text, number, integer, percentage, etc… • A sheet/cell reference: the sheet where the field is and where in the sheet (cell range) • A description: brief narrative Data Dictionary

29. Example Data Dictionary

30. Example Data Dictionary

31. Includes raw and calculated data • This is the actual spread sheet Data

32. Example Raw Data(Most Visited Websites)

33. Example Raw Data(Internet Interests)

34. Example Raw Data(Computer Experience)

35. Example Calculated Data(Most Visited Websites)

36. Example Calculated Data(Internet Interests)

37. Example Calculated Data(Computer Experience)

38. Visual Charts and conclusions Dashboard

39. Example Dashboard

40. Example Dashboard

41. Example Dashboard

42. Example Dashboard

43. Your assignment is different from the assignment from which these examples are taken from • Only use the examples as ideas • Your specifics are going to be different: • Data • Calculations • Charts (dashboard) • So, do not copy from these examples, not even partially WARNNING