This assignment is due by the beginning of your discussion section on Wednesday, October 22.
Summary: You will use Microsoft Excel to build two spreadsheets: one to compile simple baseball statistics (but you don't have to know anything about baseball) and another to produce invoices for a picture framing service. The key to this assignment is reading the specifications very closely and carefully to extract every bit of necessary information.
Introduction to spreadsheets: You will be using the spreadsheet program Excel, part of the Microsoft Office suite of programs.
A spreadsheet program is a visual calculator, laid out in rows and columns like the paper spreadsheets that accountants once used, before they had computers. The first spreadsheet program, in 1979, was called VisiCalc. It ran on the Apple II, and more than any other program it was responsible for the wide use of personal computers today. Before VisiCalc, people used personal computers mainly for word processing and for games; VisiCalc opened up a such a wide range of new uses for computers (in forecasting, finance, planning, record-keeping, and so on) that sales of personal computers soared.
A spreadsheet is a big page filled with boxes. The columns are labeled with letters (A, B, C, and so on) and the rows with numbers (1, 2, 3, ...). The box in the upper left hand corner is called A1, since it is on the intersection of column A and row 1.
Each box on the spreadsheet can contain a number, like 98.7, or a label, like Tax Return. This is called the value of the box. Each box can also contain a formula, such as =A1+B1 (all formulas in Excel start with an equals sign).
Normally you see the box's value when you examine the spreadsheet on the computer, whether or not there's a formula there too. But if a box does contain a formula, the value of that box is calculated from the formula. For instance, if box C3 contains the formula =A1*B1, then the spreadsheet looks up the values in boxes A1 and B1, multiplies them together, and displays the result as the value of C3. If you later change the value of A1 or B1, the spreadsheet automatically figures out (and displays) the new value for C3.
This automatic updating feature is the key to spreadsheets' utility. For instance, you can set up all the formulas for paying taxes and then play around with various numbers, letting the spreadsheet calculate how much tax is due under each scenario. In a similar way, you can set up a spreadsheet to do financial forecasting, keep bowling averages, or model the population of goldfish and dragonflies in your ornamental pond. In the case of our picture framing service, you can set up an invoice, then change the matting material's cost and see immediately its effect on the total amount due.
In fact, spreadsheets are to numbers what word processing programs are to text: Once the spreadsheet is set up, its user can make whatever changes he or she needs without having to respecify the formulas or calculations, or retype any of the other, unchanged data.
First spreadsheet (baseball statistics): You are the new manager of the California Angels baseball team, and you want to keep a spreadsheet to record a player's batting statistics. Completing this assignment shouldn't require any knowledge about baseball beyond what appears in this document, but feel free to ask your TA or send mail to ics10a@uci.edu for clarification of any details.
Part 1: First, set up a spreadsheet that looks like this (with your own name in the appropriate place, of course):
Cell A1 is a label, containing the player's name; Cell A8 is a label containing your name as the manager. Cells A2 through A7 are also labels; they correspond with the values you will enter in B2 through B6. B2 is the number of times the player came up to bat (i.e., took a turn to hit the ball) so far this season. B3 through B6 show how many singles, doubles, triples, and home runs the player made (i.e., how far around the bases the player got each time he hit the ball safely). C1, D1, and D3 are also labels.
B7 and E3 should contain formulas. B7 is the total of B3 through B6, and E3 is the player's average for the year (the total number of hits divided by the number of at-bats).
Now, create a page header to give this spreadsheet the title "Baseball Statistics -- Part 1." (Check the on-line help to find out how to do this.) Save this spreadsheet (showing the player's total hits and batting average) in a file called part1.xls and submit it via Checkmate.
Part 2: Now, you'll modify your spreadsheet to keep track of the player's "slugging percentage." That's like the batting average, except that a double counts for two hits, a triple for three hits, and a home run for four hits. Change your spreadsheet to include the "Total Bases" (that's the weighted total of the hits) and the slugging percentage:
D5 and D6 are labels. E5 should contain a formula that figures out the number of total bases, as described above. E6 should calculate the slugging percentage, which is the number of total bases (from E5) divided by the at-bats (B2).
Now change the page header of your spreadsheet to read "Baseball Statistics -- Part 2", save a copy of this spreadsheet with the name part2.xls, and submit it via Checkmate.
Part 3: If the player has a hitting streak and hits a lot of triples, the slugging percentage will go up. Increase the number of triples until the slugging percentage is over 2.00. How many triples do you have to hit to get to a slugging percentage of 2.00? Save the spreadsheet that shows this number as a file with the name part3.xls and submit it via Checkmate.
Part 4 (optional): After you produce your spreadsheets to turn in, you might try to experiment with these items:
* To make your spreadsheet more readable, vary the widths of the columns, move the cells around, change the type styles or other formatting (always remembering that the soul of good design is restraint--just because a dozen different type styles are available doesn't mean that the document is improved by using all of them together), or add graphics.
* Try to rearrange all the player's information onto one horizontal line; then add further lines for other players, and add statistical totals for each column.
* Modify the page header to include your name, the date, and a page number.
Save the spreadsheet reflecting all these optional modifications with the name part4.xls and submit it via Checkmate. You'll get a little extra credit for this, though it won't substitute for skipping required parts of the assignment.
Second spreadsheet (picture framing): The Framous Amos picture framing service has asked you to design a spreadsheet to computerize its charging and billing. Picture framing charges depend on a number of factors:
* The size of the art itself
* The size of the frame (which may be somewhat larger than the art, to allow for some empty space to set off the art itself)
* The cost of the framing material (plain wood is more expensive than plexiglass, for example, and lacquered wood is more expensive than plain)
* The cost of the matting material, if any (matting, if it's used, surrounds the art inside the frame, taking up the blank space between the art itself and the frame)
* The cost of the face material, if any (glass, plexiglass, or UV-screening plexiglass)
* Any discount given to the customer
* Sales tax (which we will assume to be fixed at 8.5% for this exercise)
Your spreadsheet will have cells for the user to specify each of the following items (we have also included some example values the user might enter):
Client's name Space Gallery
Client's street address 6016 Santa Monica Blvd.
Client's city, state, and Zip code Los Angeles, CA 90038
Title of the artwork Akira Kurosaki, Thumbs (1976)
Height of the artwork in inches 7.25
Width of the artwork in inches 9.5
Height of the frame in inches 11.75
Width of the frame in inches 14
Type of framing material black lacquer frame
Cost per linear inch of framing 2.40
Type of facing material plexiglass face
Cost per square inch of facing 0.75
Type of matting material black silk mat
Cost per square inch of mat 0.12
Percentage discount (zero if none) 15
Reason for discount gallery discount
The cost of the framing material is given per linear inch (an 8-by-10 inch frame is 36 linear inches), the facing cost is given per square inch, and the matting cost is given per square inch (note that the matting area is the difference between the artwork area and the framed area).
(There really is a print by Akira Kurosaki called Thumbs. It's in David Kay's office; feel free to stop by for a look.)
You will use Excel to build a spreadsheet that models the Framous Amos invoice. It should look essentially like the example shown below:
Note particularly how this spreadsheet is organized: The user enters the data in the area above the thick horizontal line (we've used the data shown in the example above) and the invoice itself appears below the line. The spreadsheet user only needs to type above the line, entering just the bold-faced information. The spreadsheet automatically calculates everything below the line from the values entered above. Besides helping to make the data entry task clearer, this organization helps avoid errors; the user is less likely to type over a cell containing a formula if his or her typing is in a distant part of the spreadsheet. (In fact, Excel has a feature that allows the spreadsheet designer to protect and lock selected cells, so the user can't type anything there by mistake.)
Part 1: Create this spreadsheet as described above. Put your own name into a page header. Save two copies of the spreadsheet, with different data, and submit them via Checkmate.
Part 2: Look closely at the subtotal figure in the example spreadsheet. It isn't the correct sum of the three lines above it! Why not? Write a two-sentence explanation of what caused the problem, including an example of some other set of three numbers that would cause the same kind of error. Save your explanation and example in a file (either Word or plain text) and submit it via Checkmate.
(This is not simply some mistake on the part of the spreadsheet designer, nor is it a bug in the Excel software. To investigate this further, select all the cells in the totals column and reformat the numbers (choose Cells from the Format menu, click the Number tab, and select the "General" format from the top of the list of choices). Do the numbers you now see add up? This kind of problem is common in computing; it's called "roundoff error," and it comes from a discrepancy between the data and the way you choose to represent it when it's displayed.)
Feel free, within the time available, to make the screen display as clear as possible for the user, taking advantage of changing the column widths, row heights, typefaces, graphics, and so on.
Want to see more? For a full-blown, professional web-based framing service, check out the Metropolitan Picture Framing site, http://www.metroframe.com.
Your score will depend on completeness (do your spreadsheets do everything the specification requires; do you answer all the questions; and so on), correctness (do they produce the correct results), and quality and clarity of the spreadsheets' format.
Written by David G. Kay (1985)
Modified for use in ICS 21 by David G. Kay, Fall 1990
Minor revisions by Norman Jacobson, Winter 1991
Revised by Norman Jacobson, Fall 1991
Minor revisions by David G. Kay, Winter 1992
Modified for use in ICS 1A by David G. Kay, Summer 1992, Fall 1993, Fall 1994, Fall 1995.
Revised for ICS 10A and Microsoft Excel by David G. Kay, Fall 1999; revised again Fall 2000, Fall 2001, and Fall 2003.