Making a Spreadsheet Using Excel
module last modified: January 6, 2002

PLATFORM
This is a Macintosh or PC module.

PURPOSE OF THIS MODULE
Spreadsheets were one of the first kinds of computer applications that really made it big, and made people want to buy computers: they allow for doing all kinds of calculations using numbers, so that you can do financial predictions, lab reports, and grading charts among other things. In this module you will learn how to:

NOTE
This module has 2 parts. You must turn in both parts in order to receive full credit for this module.

Part 1
First, you will open a spreadsheet that has some information in it; then, you will add information to it, and make the spreadsheet do calculations for you.

  1. Open Microsoft Excel. On the PC it is in the Start menu>programs>spreadsheets folder. On the Mac you can use Excel 98 which is in the Apple menu>local applications>Microsoft Excel 98.
    When Excel is open, you will see a window with a grid in it: this is a basic spreadsheet worksheet.
  2. Things to notice & remember before you start:


Opening up an existing spreadsheet
Now you will open a spreadsheet that contains some information about how the CCLI is funded (it is years old and not accurate for today, but it still works for this project). You will add information to this spreadsheet, in order to see if the CCLI's budget is balanced.

  1. Open the spreadsheet named, "working_spreadsheet.xls".
    This spreadsheet is in the References folder, inside the HU2644_Spring 2002 class folder, on the Groups drive.
  2. Once the spreadsheet is open, choose "Save As" from the File menu, and save a copy of the spreadsheet to your Home Directory. Rename the file to "CCLI Budget," followed by your last name (so that I will know whose work this spreadsheet is when you turn it in).

    (If you do not do this "Save As"step, you lose any changes you make to the spreadsheet if you try to save it to the Groups drive.)

    If you see a dialogue box that says you can't save with this title, click on your home directory icon and look for a strange string of numbers and letters. That's your document. Click once on the string and change the name to "CCLI Budget". Then double click on the file and it will open back into Excel.

  3. Use the scroll bars at the bottom and right of the spreadsheet window to scroll through the spreadsheet, and to see what it contains.

    This spreadsheet contains the bare bones of a budget for the CCLI. The top section has some information about the income of the CCLI, that is, the money that comes into the CCLI; the bottom of the spreadsheet shows how that money is spent, on salaries, hardware, software, and furniture. What has been entered into the spreadsheet are the basic figures; you will be making the spreadsheet do the necessary addition, multiplication, and division of these figures in order to see if the lab is taking in enough money to support itself.

Entering information the CCLI spreadsheet
First, you will complete the INCOME section of the spreadsheet. You will simply enter some figures, but then you will also get the spreadsheet to do some basic math for you.

  1. Notice that, in the INCOME section, there are only some figures entered for the yearly number of majors & non-majors who pay lab fees (and the lab fees they pay), as well as some figures about how many people take computer-intensive classes over an average year.

    Notice that there is nothing entered for "Other (e.g., summer consultant $, equip. sales)". This is what you will enter first, and since these two figures are simply lump sums, you can enter them without having the computer do any calculations on them.

  2. Click in cell E10, three cells over from "Other"
  3. Type in "3500."

    Don't worry about typing in the "$"sign or decimal points: you'll get the computer to handle that for you automatically in a moment.

    But do notice that, as you type, what you type appears at the top of the screen, in the white box above the title of the window you are working in; the words also appear in the cell where you clicked. You can edit text either in this white box or in the cell (double click the cell will let you do it).

  4. When you have finished typing, press the "Enter" key on your keyboard.
    (Pressing "Enter"is how you tell the computer that you have finished entering the information for a cell.)
    (You can also simply click in another cell to tell the computer that you have finished entering information.)

Getting the computer to do some calculations for you
Now you will get the computer to do some simple addition for you, by adding up the numbers you just entered.

  1. Click in cell E11: this is where you want the CCLI's total income for a year to appear.
  2. Type the "="sign (just the equal sign; you do not need the quotation marks).
    This tells the computer that you want it to do some math for you.

    Then click individually in the cells where you just entered numbers (cell E10). Then enter a "+" and click in the next cell. As you click, notice that the computer is creating a formula for you, in cell E11 and in the white box where you can edit what is in a cell. (Notice that each cell you click will get a thin gray dotted line around it when you click it.)

    Notice how the computer writes the formula for doing the math: it's simple: "=E9+ E11". You can make this spreadsheet application do much more complicated things for you, but this gives you a good idea of how the application refers to and manipulates numbers.

  3. Press "Enter" and the computer will add the two figures for you. You can also type in or the formula manually in the dialogue box at the top of the screen by typing "=SUM(E9:E10)".


Getting the computer to do some slightly more complex calculations for you:
You may have noticed that so far I have had you add up only a small part of the CCLI's income. Now you will get the computer to figure out just how much money comes into the CCLI from lab and computer-intensive class fees, and then you will include those figures into the total income figure as well, so you are going to get the computer to multiply the number of people paying lab fees by the amount of fee they pay in order to see how much money lab fees generate for the lab.

  1. Click in cell E5, and type the "=" sign, and then click in cell C5, enter a "+" and then in cell D5. Then press Enter.
    You have just gotten the computer to add the values of C5 and D5, but this isn't correct: the value that goes into E5 should be that of C5 multiplied by D5.

  2. Click in cell E5, and then, in the white box at the top of the window where the formula for E5 appears, change the "+"sign to the "*" sign. (This is an asterisk, the character you get when you type shift-8. The asterisk is the sign most computer applications use to signify multiplication.)

  3. Press Enter.
    Notice that the value in cell E5 now changes to reflect the change you made in its formula.

Getting the computer to repeat similar calculations
You could now repeat steps 1 to 3 from above for cells E6 and E7, but there is an easier way to have the computer do similar calculations in cells that touch each other.

  1. Click the mouse in cell E5, and, while holding down the mouse button, drag-select cells E6, E7, and E8. The cursor should turn into cross icon for this step.
    When you release the mouse button, cells E5, E6, E7 and E8 should be outlined, and the last 2 cells should be highlighted.
  2. In the Edit menu, pull the mouse down to the "Fill" option.
    A submenu will open out to the right. (Anytime you see a small, black, right-pointing diamond next to a menu option, it means that that option has a submenu attached to it.)
  3. Choose "Down" from the submenu.
    When you release the mouse, the computer will apply the formula you entered in the first cell you had selected to the cells under it; the computer will understand that you want the cells below C5 and D5 to be multiplied together just as C5 and D5 are.

Modifying an earlier formula
Now you want to include the calculations you just did into the figure for the total income for the CCLI.

  1. Click in cell E11, where you had the computer do some simple addition for you earlier.
  2. In the white box at the top of the window where you edit the content of cells, type a "+" sign behind the formula that's already there, and then click once in cells E5, E6, E7 and E8, putting a "+" between each one.
  3. Press Enter.
    The new and complete total income for the CCLI should appear in cell E11.


Formatting cells to show different kinds of values
Now, in several quick steps, you will make the cells that contain dollar values be marked with dollar signs and decimal points.

  1. Drag select cells E5 to E11, so that they all have a heavy line around them.
  2. Choose "Cells·"from the Format menu.
    A dialog box will open. Across the top of the box are different formatting options you can select for changing the look of information in a cell or range of cells.
  3. Click the "Number"category from the folder tabs at the top of the dialog box to access the options for the numeric appearance of cells.
  4. In the list of "Category" options to the left, choose the "Currency" option.
    In the "Currency Symbol" pulldown menu, choose the "$" sign.
    This option will place a dollar sign before any values appearing in the cells you have selected, and will show a decimal point and the cents for those values.
  5. Click "OK."
    The cells you selected should now all show that they are dollar amounts.

OR

  1. Click once in cell E10 to highlight it. Next, click on the dollar sign in the formatting menu bar at the top of the screen. This will add a dollar sign to any number that you put in this cell. Your 3500 should now say $3500.00. If the dollar sign is not visible in the menu bar, go to View>toolbars>formatting.


Making the total Income Amount Stand Out a Bit More

  1. Click in cell H11, type the "="sign, and then click on cell E11. Now press the enter key.
    Cell H11 will now show whatever value is reflected in cell E11, which is not a big deal, except now you will format cell H11 to stand out more.
  2. Click in cell H11 again if you have deselected it, and then choose "Cells" from the Format menu.
  3. Click the "Font" option in this dialog box, and then make selections that will help cell H11 stand out.
    You could choose a larger font size, or a bolder typeface, it's up to you.
  4. Ensure that the value in H11 is the same numeric currency format as cells E5 to E11.


Exploring the power of spreadsheets

  1. Click in cell C5, and type in a new value for the number of people who pay lab fees in an average year. Type in any value you want.
  2. Press Enter.
    Notice that the value in cell E5 changes to reflect what you just did, as does the value in cells E11 and H11.

    This should give you a sense of how business people, and others, can use spreadsheets to make estimates about budgets and company growth.

  3. Click in cell C5 again, and reenter the original value of 623.


Finishing the spreadsheet
There's a lot still to do to finish the spreadsheet, but you know now how to do it all.

FOR SALARIES & WAGES

  1. In cell G16, use what you learned above to make the computer calculate how much money is paid out to lab consultants each year>
    You will have to multiply cells C16, D16, E16, and F16 together.
  2. Repeat the steps above to compute the total for G17
  3. In cell G19, get the computer to figure out the total yearly salary expense of the CCLI.
    You will have to add cells G16 and G17.
  4. Change the cell formats of cells G16 to G18 to be the same numeric currency format as cells E5 to E11.
  5. Get cell H19 to repeat the value of cell G19, but more visibly.


FOR HARDWARE EXPENSES, Part 1

  1. Figure out the cost per year of the CCLI's servers and workstations. Each piece of this hardware costs one (large) lump sum, but the CCLI spreads this amount over 3 years, so you will have to do some division here, as outlined in the next steps.
  2. Click in cell F23, and type "=".
  3. Click in cell E22, and then type "/" and "3"; press Enter.
    You have just figured out how to tell the computer to figure out how to divide the cost of an Ultraserver over 3 years.
  4. Again, highlight cell F26, F27, F28, and F29 and use the "Fill Down" command (under the Edit menu) to get the computer to do the same division for cells F26, F27, F28, and F29.


FOR HARDWARE EXPENSES, Part 2


FOR HARDWARE EXPENSES, Part 3

  1. In cells F35, and F36, get the computer to calculate the total cost of the CCLI's Phone and IT charges. You will have to multiply the number of each of these things by their unit cost.
  2. In cell F37 calculate the total of F35 and F36.
  3. Calculate the total costs for cells E41 to E51.
  4. In cells F41 to F44, get the computer to divide the cost of these different devices over 3 years.
  5. In cells F45 to F51 calculate the appropriate totals.
  6. Repeat steps 3 and 4 for cells E54 to E59.


FOR HARDWARE EXPENSES, Part 4

  1. In cell F60, get the computer to add together the total cost of that shown in cells F23 to F59.
  2. Get cell H60 to repeat this value, but more visibly.
  3. Make sure that all the cells where you have just computed dollar values have been formatted to have the numeric currency format you used previously.


SOFTWARE EXPENSES

  1. Cells E63 to E78 and cells F62 to 78 work the same as cells E37 to 59 and cells F37 to 59, respectively. Fill these in with the appropriate equations.
  2. In cell F79, get the computer to calculate the CCLI's total yearly expense for software. Get cell H79 to repeat this value, but more visibly. Make sure that all the cells where you have just computed dollar values have been formatted to have the numeric currency format you used previously.


FOR FURNITURE EXPENSES

  1. You should be able to handle this section on your own now. The only thing to note is that the first item (surge protectors) has its total cost divided over 4 years; all the other furniture has its cost divided over 7 years.
  2. Be sure you calculate the total furniture expense in cell F90, and then repeat it (but more visibly) in cell H90.


Finally (almost finished)

  1. In cell H92, get the computer to calculate the total expenses of the CCLI for a year.
    Does the amount the CCLI brings in cover its expenses?
  2. This is optional, but you might want to go back over and change some of the typefaces in the spreadsheet (in their size and boldness) to make the parts of the spreadsheet more easily discernible.
  3. Before you finish, review all of the monetary entries you have made. Do they all appear in the same format (e.g., dollar signs, two units after the decimal point, etc.). If not, change them to the standard format.
  4. Click in cell A1, and then choose "Rows" from the "Insert" menu.
    This will move all the other cells down (carrying their formulas with them), and give you a new row of cells across the top of the spreadsheet.
  5. Please type your name and the date in the new cell A1.
  6. Print this spreadsheet (finally!).
    You can print it out the same way as you would print anything else, by choosing "Print" from the File menu, and it will just print out onto a regularly-sized sheet, but the spreadsheet will be pretty cramped, and hard to read.
    Instead, print the spreadsheet onto tabloid (11"x17") paper: choose one of the printers (Kruger or Holzer) that can handle tabloid size paper.

    If you are printing to Kruger, then choose tray 3.

    If you are printing from Holzer, just go ahead and print.

    The consultant can help you find tabloid-size paper to feed into the printer, and can help you learn how to hand-feed if you have never done this before. (Just be sure to remember the printer to which you sent the spreadsheet!)

Part 2
Now I want you to use the spreadsheet to figure out some strategies the CCLI can use to balance its budget. First, you will save a copy of the spreadsheet you have just made, and play with the figures in it to see how changing some aspects of the budget change the overall budget; then I want you to type up a page in which you use your figure-playing to argue for the strategy (or strategies) you think the CCLI should use to balance its budget.

  1. Choose "Save As" from the File menu, and save your now-complete spreadsheet under the name "Spreadsheet2", followed by your name.
  2. In cell B1, change the title of the spreadsheet to reflect that this is a "revised" spreadsheet.
  3. Now, change the values & calculations in the spreadsheet to see what it would take to balance the budget. You can work to increase the income, or you can work to decrease the expenses. Here are some things to explore (be sure to keep your original spreadsheet next to you, so that you can return numbers you change back to their original values):

  4. After you have explored the options above, decide what strategies (or mix of strategies) you think make the most sense for the lab (and for your own pocketbook).
  5. Set up this new spreadsheet to reflect the changes you would make.
  6. In Word, type up a one-page (250 word) report. In this report, describe the changes you would make, and argue why you would make the particular changes you recommend.
  7. Please put your name on your report, and print it out. Also print out the revised spreadsheet.


TO TURN IN
You should have two spreadsheets to turn in, the original, with all the appropriate cells completed, and a revised one for balancing the budget. Make sure that both of your excel documents are formatted correctly. On or before the due date for this assignment, please turn in both spreadsheets and your report in hard copy to me or in my mailbox. (Please staple or paper-clip them together.) Be sure your name and the date is on all of them.

POINTS
Turned in on time 10
CCLI spreadsheet 30
Revised spreadsheet 25
Your typed report 10
Instructor's Discretion 15

Bonus task:
Create an excel file for your grades. Be sure your name, userID, and student number are at the top.
Make a row for each module (complete listing available here) with a space each for: On time, Project points, a blank column, Instructor's discretion, then Bonus points, and a column for total points. At the bottom of the spreadsheet have a space for total points. As each grade is entered, this space will provide a running total. Each row should add across to the total points column for the project, and then add down to the total points cell at the bottom. Be sure it works!!

At the bottom, under the total points cell, put in a cell for how many points are needed to get an A. The formula should be: =2300-[the total points cell].

Please do not leave blank lines or columns (except the one I asked for) between the projects. I will use this spreadsheet to track your grades and to send you your grades throughout the semester, and it will be easier to read if the rows and columns are contiguous.

Then you may decorate it anyway you wish--color, graphics, etc.

Save this into the turnin folder under your name also, and call it "bonus.xls".

Bonus points 10
Total points 100

Return to Schedule