Lab 4 Writeup

Be obsessive—IT people attend to details because in IT every little thing matters.

In this lab exercise, we'll review basic Excel spreadsheets and then work with Excel's charting/graphing capabilities.

Formatting a Worksheet

For this lab exercise, download this example spreadsheet: lab4-checkbook.xlsx. We'll try to make it look like this sample spreadsheet:

1. Add Checkbook Entries. Add at least 8 entries to the checkbook in the appropriate places. You don't need to use the entries given here; most any entries will work so long as you can categorize them easily.
2. Format the Header. Select all the cells in the header, choose "Format"-"Cells" and then set the "alignment", "font" and "patterns" as you see fit.
3. Format the Dates/Values. Select all the date cells, choose "Format"-"Cells", and format them as you see in the sample. Then do the same with the currency values, selecting the "currency" category on the "Number" tab.
4. Write a Balance Formula. Write a balance formula for cell F3 that takes the opening balance from F2 and subtracts the withdrawals and adds the deposits. When you've got the formula, then copy it down the column.
5. Add an Expense Type Column. Add a "Type" column to the table, format its header as you did the other headers, and then enter appropriate values for each checkbook entry.
6. Create an Expense Type Table. Create a new expense type distribution table as shown on the bottom of the sample. It should have a row for each expense type and a formula that computes the sum of the expenses of that type.
7. Write the SUMIF Formula. Enter in the expense-type category names by hand and then write a formula to compute the sum of the withdrawals for each type. Use the function wizard to do this by highlighting the expense type cell (C17 in the sample), choosing "Insert"-"Function", searching for the function SUMIF, clicking "OK", and then letting the function wizard walk you through the process. Your formula should look something like this:

   SUMIF(checkbook type names, 
         type name condition,
         checkbook values to be summed)
   

   where checkbook type names is a range from the checkbook itself, type name condition is a single cell from the category column in the expense table, and checkbook values to be summed is another range from the checkbook. (Hint: the two ranges from the checkbook should start and end on the same rows.) When your function is working, copy it down the column for each type category. Remember to put commas between your 3 arguments and to use absolute and relative references appropriately.

Creating a Simple Pie-Chart

Now, create a pie chart that shows the distribution of your expenses as shown in this sample pie chart.

To create this pie chart:

1. Select the cell range to chart. Select the cell range of the chart, B16:C21 in the example.
2. Create the chart. Choose the "Insert" tab on the ribbon and select the "pie" chart type from the options.
3. Specify the chart settings. Work through the chart options in the Office Ribbon (and some clever right-clicks on the chart itself) trying to specify settings that will make the chart look as much like the sample as possible. You may want to try it several times to experiment with different setting values.

Choosing an Appropriate Chart

It is important to know how to choose an appropriate chart (or charts) for expressing the message you want to deliver. As discussed in class, Excel provides a number of chart/graph types, including pie charts, bar/column charts, line graphs and scatter plots. Always choose the best one for your data and message.

For this lab exercise, consider the data shown in lab4-costs.xlsx and answer the following questions. Do not build any of the charts, just indicate which kind of chart you would build.

• Which Chart #1. You want to help prospective students decide whether they should live on or off campus by showing them the relative costs of the three options shown in the example table. What chart would you use to do this?
• Which Chart #2. You want to show current students how their total tuition cost is divided among the various expense categories shown in the example table. What chart type would you use for this?

Jot down your written answers, and type them into the "Comments" field of your KV submission.

Extra Credit

As an additional challenge, try to figure out a way to present both of these perspectives in a single chart.

Clearly label your charts.

A Challenge: Creating a Line Chart

For additional extra credit, you can go back to your checkbook spreadsheet and create a line chart that shows the balance of your account over time, as shown in this sample line chart. Create this chart as follows:

1. Select the Data to Plot. Select the cells containing the data and the dates you want to plot. Because these cell ranges are on different parts of the chart, you must first select the range of cells with the dates (B3:B13 in the example), and then press and hold the Ctrl button to select the range of cells with the balance value (F3:F13 in the example). This will result in a more complex cell range specification (B3:B13,F3:F13 in the example).
2. Create the chart. Choose "Insert" tab and choose a "line" chart, and then follow the wizard through the process of creating the chart (as you did with the pie chart above).

Submitting your Work

Submit your budget spreadsheet as an attachment to your Lab 4 KnightVision submission. Type your answers to the charting questions into the "Comments" field. If you did the college-costs extra credit, attach your college costs spreadsheet as well, and make note of it in the "Comments" field. If you did the line-graph extra credit, make a note of that, too, in the "Comments" field.