Slide 1: Computer Technology 202 Applications of Spreadsheets University of New Mexico Los Alamos Campus Spring 2006
Slide 2: Did you Know …  Spreadsheets:     have been described as a way of word processing numbers. were the first application software available for microcomputers have been credited as the pioneers of the microcomputer revolution. make processing calculations    faster more accurate visually pleasing (providing visual feedback in the forms of charts and graphs).
Slide 3: Spreadsheet Software  In general, software is considered to have spreadsheet capabilities if it can:       make calculations and comparisons automatically recalculate data copy and paste cells line up data in columnar form create visual representations of data, (charts, graphs, etc) import documents from other spreadsheet programs.
Slide 4: Spreadsheets  The advantages of spreadsheet software are numerous:    a significant savings of time the ability to create accurate graphic representations of data the capability of posing and answering “what if” questions.
Slide 5: The Webopedia Definition of a Spreadsheet  A table of values arranged in rows and columns.   Each value can have a predefined relationship to the other values. If you change one value, therefore, you may need to change other values as well.
Slide 6: The Webopedia Definition of a Spreadsheet  Spreadsheet applications (sometimes referred to simply as spreadsheets) are computer programs that let you create and manipulate spreadsheets electronically.    In a spreadsheet application, each value sits in a cell. You can define what type of data is in each cell and how different cells depend on one another. The relationships between cells are called formulas, and the names of the cells are called labels.
Slide 7: The Webopedia Definition of a Spreadsheet  Once you have defined the cells and the formulas for linking them together, you can enter your data.   You can then modify selected values to see how all the other values change accordingly. This enables you to study various what-if scenarios.
Slide 8: Example of a Spreadsheet   A simple example of a useful spreadsheet application is one that calculates mortgage payments for a house. You would define five cells:      total cost of the house down payment mortgage rate mortgage term monthly payment  Once you had defined how these cells depend on one another, you could enter numbers and play with various possibilities.  For example, keeping all the other values the same, you could see how different mortgage rates would affect your monthly payments.
Slide 9: The Webopedia Definition of a Spreadsheet   There are a number of spreadsheet applications on the market, Lotus 1-2-3 and Excel being among the most famous. The more powerful spreadsheet applications support graphics features that enable you to produce charts and graphs from the data.
Slide 10: The Webopedia Definition of a Spreadsheet  Most spreadsheet applications are multidimensional, meaning that you can link one spreadsheet to another.   A three-dimensional spreadsheet, for example, is like a stack of spreadsheets all connected by formulas. A change made in one spreadsheet automatically affects other spreadsheets.
Slide 11: A Brief History of Spreadsheets   Spreadsheets have been used by accountants for hundreds of years. Computerized or electronic spreadsheets are of much more recent origin.   In 1961, Professor Richard Mattessich pioneered the development of computerized spreadsheets for use in business accounting. His computerized spreadsheets were the electronic equivalent of handkept ledgers.   The spreadsheet page consisted of a grid of cells, labeled as to rows and columns. Either numbers or labels could be entered into individual cells, just like in a ledger and you could enter headings for columns and/or rows and data under the appropriate headings.  No advantage over a handwritten ledger, except that the data was entered more neatly.
Slide 12: VisiCalc  VisiCalc was the first computer spreadsheet program.   It was released to the public in 1979, running on an Apple II computer. It was considered a fourth generation software program.  Prior to this, companies invested time and money in doing financial projections with manually calculated spreadsheets, where changing a single number meant recalculating every single cell in the sheet.  With VisiCalc, you could change any cell, and the entire sheet would be automatically recalculated.
Slide 13: VisiCalc  Dan Bricklin and Bob Frankston invented VisiCalc.   While a masters student in business administration at Harvard Business School, Dan Bricklin joined up with Bob Frankston to help him write the programming for his new electronic spreadsheet. The two started their own company, Software Arts Inc., to develop their product.   By the fall of 1979, an Apple II version of VisiCalc was ready, and the team started writing versions for the Tandy TRS-80, Commodore PET and the Atari 800. By October, VisiCalc was a fast seller on the shelves of computer stores at US $100.
Slide 14: VisiCalc  VisiCalc was soon sold to Lotus Development Corporation, where it developed into the Lotus 1-2-3 spreadsheet for the PC by 1983.   Bricklin never received a patent for VisiCalc. It was not until after 1981 that software programs were made eligible for patents by the Supreme Court. "I'm not rich because I invented VisiCalc, but I feel that I've made a change in the world. That's a satisfaction money can't buy." - Dan Bricklin
Slide 15: What is Lotus 1-2-3?  Lotus 1-2-3     made it easier to use spreadsheets and it added integrated charting, plotting and database capabilities. established spreadsheet software as a major data presentation package as well as a complex calculation tool. was also the first spreadsheet vendor to introduce naming cells, cell ranges and spreadsheet macros. is still one of the all-time best selling application software packages in the world.  In 1985, Lotus Development acquired Software Arts and discontinued the VisiCalc program.
Slide 16: What about Microsoft Excel?  The next milestone was the Microsoft Excel spreadsheet.    Excel was originally written for the 512K Apple Macintosh in 1984-1985. Excel was one of the first spreadsheets to use a graphical interface with pull down menus and a point and click capability using a mouse pointing device. The Excel spreadsheet with a graphical user interface was easier for most people to use than the command line interface of PC-DOS spreadsheet products.
Slide 17: What about Microsoft Excel?    When Microsoft launched the Windows operating system in 1987, Excel was one of the first application products released for it. When Windows finally gained wide acceptance with Version 3.0 in late 1989 Excel was Microsoft's flagship product. For nearly 3 years, Excel remained the only Windows spreadsheet program and it has only received competition from other spreadsheet products since the summer of 1992.  By the late 1980s many companies had introduced spreadsheet products.
Slide 18: Citation  Power, D. J., "A Brief History of Spreadsheets", DSSResources.COM, World Wide Web, http://dssresources.com/history/sshistory.html, version 3.4, 06/06/2003.
Slide 19: What’s the Big Deal?  An electronic spreadsheet organizes information into software defined columns and rows.  The data can then be "added up" by a formula to give a total or sum.  The spreadsheet program summarizes information from many paper sources in one place and presents the information in a format to help a decision maker see the financial "big picture" for the company.
Slide 20: What’s the Big Deal?  The big deal about spreadsheets is that they will do calculations for you.   Suppose you have a series of columns, and each column has entries down through 20 rows. In row 22 of each column, you want to enter the sum of the entries in the preceding rows. In a ledger, you would go down the column and add one number to the next, either in your head or with a calculator, and then enter the sum in row 22. Let's assume you do it correctly, with no error. You have to do that process separately for each column, no matter how many columns there are.
Slide 21: What’s the Big Deal?  Now suppose you discover that there needs to be a new datum added for every column, and it needs to be placed between rows 13 and 14.   In a hand ledger you're stuck-- there's no room between rows 13 and 14, so you have the choice of re-writing everything on a new ledger sheet, or placing the new data in an open row that's not located where it should be. And oh yeah, don't forget to go back to those sum values and add the newly included value in each row to the sum, after you've erased or whited out the original entry.
Slide 22: What’s the Big Deal?  In a spreadsheet you would type in the values in the appropriate cells, just like entering them in a ledger.    But then, instead of doing the calculation yourself and entering the sum in row 22, you would enter a formula in row 22 of the first column that said to determine the sum of the entries in rows 1 through 20 in the same column, and to display the sum in the cell in which you typed the formula instead of showing the formula (you could also tell it to show you the formula instead of the calculated value). Then for the other columns, you just copy the formula for the first column into the row 22 cell for each column. The spreadsheet does the calculations for each column.
Slide 23: What’s the Big Deal?    When you discover that you need to add a new value between rows 13 and 14, you tell the spreadsheet to create a new row there. Everything below that point moves down one row, and the formula is automatically changed to indicate that the sum should be determined for the entries down through row 21 now, instead of row 20 as before. Now just enter your new values for each column in the new row 14, and the sum values in what is now row 23 will update automatically to include the new data.
Slide 24: Microsoft Excel  Microsoft Excel clearly dominates the spreadsheet market.   Not too long ago, Lotus 1-2-3 was considered the "standard" spreadsheet. Excel now holds that distinction, with an estimated 90% market share.
Slide 25: History of Excel Version Released Comments 1 2 1985 Version 1, for the Macintosh was released. 1987 The first Windows version was labeled "2" to correspond to the Mac version. This included a run-time version of Windows. 3 1990 Included toolbars, drawing capabilities, outlining, add-in support, 3D charts, and many more new features.
Slide 26: History of Excel Version Released Comments 4 5 1992 The first "popular" version. Included lots of usability features. multi-sheet workbooks and support for 1993 A major upgrade. Included VBA. 7* 1995 Known as Excel 95. The first major 32-bit version of Excel. Feature-wise, it's very similar to Excel 5. There is no Excel 6. Beginning with Excel 7, the version numbering was changed so all of the Microsoft Office applications would have the same version number.
Slide 27: History of Excel Version Released Comments 8 1997 Known as Excel 97. A new interface for VBA developers, UserForms, data validation, and lots more. 9 1999 Known as Excel 2000. Can use HTML as a native file format, "self-repair" capability, enhanced clipboard, pivot charts, modeless user forms.
Slide 28: History of Excel Version Released Comments 10 2001 Known It as Excel 2002, this is part of Office XP. has a long list of new features, but most of them will probably be of little value to the majority of users. Perhaps the most significant feature is the ability to recover your work when Excel crashes. This version features product activation technology (i.e., copy protection).
Slide 29: History of Excel Version Released Comments 11 2003 Microsoft New Office Excel 2003. features in this version : improved support for XML a new "list range" feature Smart Tag enhancements corrected statistical functions.
Slide 30: Tutorial of Spreadsheets  Let’s take a look at a general tutorial on spreadsheets that Brad James did for the University of South Dakota.