2

COMP 4, Summer 2005 – Session II

Lab-06: Advanced Spreadsheets REQ2Pass

………………………………………………………………………………………………………

Total Points Possible: 50

Due Date: Monday, 11 July 2005 at 1:15 PM (start of class)

………………………………………………………………………………………………………

After attending class and completing this assignment, you should be familiar with using mixed references and functions to complete calculations, create a chart and change its features, and sort records correctly in a spreadsheet. Note: a record is usually equivalent to one row of data¾and deals with one specific person or thing. Before printing each worksheet, enter your name in the header. To do this, select View command -> Header and Footer -> Custom Header. Enter your name in Left Section and click OK. (Use print preview to make sure your name appears on top before printing!)

ATTACHED (Download Lab06-Sales-Sample.pdf)

·  Sample spreadsheet

BOOKS NEEDED

·  Shelly Cashman Office 2003 workbook

FILES NEEDED

·  Download from lab web-page: lab06-UnSorted-timings.xls

…………………………………………………………………………………………………………………………

SECTION I: Charting Basics in Excel (FOR PRACTICE)

…………………………………………………………………………………………………………………………

A.  Advanced Spreadsheets

Mixed cell referencing, or referencing that mixes absolute and relative referencing, is an important concept for this assignment and for the exams. You will also learn to use new Excel functions and practice the simple-to-use charting capabilities of Excel. Last, you will learn how to sort data (sorting records) in a spreadsheet.

B.  Workbook

Note: Glance through the sections in the Workbook that are “OPTIONAL” to learn about the possibilities. We invite the more adventurous users to explore those you find interesting or useful.
Pencil in or Post-It™ note these changes now:

PAGES / STEPS / WRITE THIS:
EX 159 / Using Drag and Drop to Move or Copy Cells / OPTIONAL
EX 161 / Inserting Individual Cells or a Range of Cells / OPTIONAL, but handy
EX 183 – 184 / Adding a Drop Shadow / OPTIONAL
EX 193 / Changing the Colors of Pie Slices / OPTIONAL, but handy!
EX 195 – 196 / Rotating and Tilting the 3-D Pie Chart / OPTIONAL
EX 197 – 198 / Renaming and Reordering the Sheets and Coloring their Tabs / OPTIONAL
EX 202 – 203 / To Split a Window into Four Panes / OPTIONAL
EX 206 – 207 / To Goal Seek / OPTIONAL (but handy for Business majors)
EX 197 – 198 / Renaming and Reordering Worksheets / Recommended

C.  PRACTICE PAGES

Read and carefully work through pages EX 146 - 208 in your Office 2003 Workbook in the order given, noting any handwritten corrections as you proceed. PAY ATTENTION to what you are doing and why.

…………………………………………………………………………………………………………………………

SECTION II: A More Sophisticated Spreadsheet (FOR POINTS)

…………………………………………………………………………………………………………………………

A.  Weekly Sales Report

Your business has taken off! Satellite offices are popping up all over the nation, and you’ve started tracking your sales state-by-state to avoid being overwhelmed. This week, you’ve decided to set up a spreadsheet to make a weekly report on all North Carolina sales. The demands are a bit more challenging, but you feel confident that you can handle it.

IMPORTANT TIPS BEFORE STARTING:

§  Pay attention to the file names and read ALL instructions carefully.

§  It’s normally good practice to perform data entry and editing tasks (logical structure) first. Formatting (physical structure) usually comes later. However, we combined the two here to make it easier for you to follow our directions.

§  Let the spreadsheet do all of the calculations. When you develop a difficult formula, always test the answer with a calculator before you fill! Even with simpler formulas, always double-check the calculations.

§  Copy formulas whenever possible. You should not have to enter the same formula more than once! You will be graded on this, so pay attention.

§  Save your spreadsheet often and periodically make a backup copy on the H: drive.

§  Follow the steps in the order given below. Be careful what range you’ve selected when charting, sorting, and performing other functions.

§  We’ve attached a sample for the Java n Therapy Cafe, but use as little of our data as possible–create your own for your unique business, where told to do so. We will be most impressed by your originality.

Minimum requirements

  1. Create a new spreadsheet file with the name Lab06-Sales-LastFirst. Use Arial 10 points (all text and numbers should be in this font and size unless specified otherwise). Under the File menu, choose Page Setup, then select the Sheet tab, and check the box to print Gridlines. Click OK. Save your work!
  2. Enter the following labels in the cells noted¾bold and 12 point:

A1: DETAILED SALES REPORT FOR NC (insert your company name here) SATELLITE OFFICES

A16: SUMMARY OF NC (insert your company name here) SATELLITE OFFICES

D3: Chapel Hill

G3: (your hometown, if from NC, or some other NC community)

J3: (your favorite NC vacation spot)

  1. Enter the following labels in the cells noted, making them boldfaced and 9 point Arial:

A14: TOTALS

A18:A21: (see sample spreadsheet for labels to use)

Row 4: (see sample spreadsheet for labels to use)

  1. Create a list of six services (to track across the 3 NC offices) in cells A5:A11; corresponding prices in cells B5:B11, as the sample shows. The services should be left aligned; the prices should be right-aligned. Prices in either Currency or Accounting format. Use prices that make sense for your seven services. DO NOT USE OUR PRICES.
  2. When you set up a spreadsheet, you want to do it in such a way that the formulas you create can be easily copied to other parts of the spreadsheet with little or no modification. To do this correctly, you need to have a solid understanding of how absolute, relative, and mixed referencing work so you can take advantage of them when you create formulas. (At this point, you should review your lecture notes, and do a little practicing at the computer).
  3. Below are directions for setting up the Chapel Hill part of the spreadsheet in cells D5:E14. After you’ve done that, you will copy the formulas from the Chapel Hill section to the other two towns’ sections (G5:H14 and J5:K14). Your formulas MUST work without having to change them in the copied cells! You will be graded on this.
  4. Enter some made-up numbers for Chapel Hill in the QTY column (C5:C11) for each item. You should use numbers between 1 and 250, but do not repeat the same number twice (do not use the numbers from the sample page).
  5. Enter a formula for the first TOTAL column in D5 (which will be copied shortly through D13).

For each row, the formula is PRICE*QTY, which is pretty simple to calculate here using the appropriate cell references. However, take a moment to consider this: how should this formula be written so that it can later be copied easily to the TOTAL columns for the other two towns, without requiring editing? The important things to note are that QTY differs for each town, but all towns use the same PRICE column (Col. B). So, write a formula in D5 that you can copy to D6:D13, but that will also hold up without the need for editing when you copy the Chapel Hill section to the other town sections. When you think you have it, enter it, and then format cell D5 (Currency, number of decimals is your choice), and fill down through cell D13.

v  Why copy through cell D13? That will give us room to add at least two new services later. There will be a bunch of zeroes displayed in rows 12 through 13 in your final report, but that’s fine; we won’t deal with that issue for this assignment. It’s trivial to fix.

Trouble? To copy a formula, the entire cell must be highlighted—not only the text in the formula.

  1. In D14, enter a formula that calculates the total for Chapel Hill. You must use the SUM function . Format it in Currency format.
  2. Now for the PCT column for Chapel Hill: For each row, you will calculate the percentage that each record represents of the total (in this case, Chapel Hill’s total in cell D14). Please use the percent formatting tool. Again, this formula is simple when you first look at it, but remember that you must copy it elsewhere later. You must create a formula in cell E5 that you can copy through cells E6:E13 (and E14, if you like—it should work). But keep in mind that you want this whole column to behave correctly when you copy it to the other town sections (there are two towns, but there could be many more). When you think you’ve got the formula correct for E5, enter it and Format it (percent with two digits after the decimal point) and then copy it to cells E6:E14. If you’ve done it correctly, cell E14 should read 100.00%.
  3. COMPLETE THE SPREADSHEET FOR THE OTHER TWO TOWNS. If you’ve created the formulas correctly, all you need to do is copy-and-paste the Chapel Hill section (D5:E14) to the other two town sections (whose upper left-hand corners are G5 and J5). You may need to fuss with this a bit, fixing original formulas and re-copying if need be. We will expect you to use the correct cell referencing in all your formulas. Next, enter some numbers under QTY for each of the other two towns so columns F and I are different than column C.

Prepare the SUMMARY OF NC (insert your company name here) SATELLITE OFFICES section.

  1. Using the sample as a guide, fill in rows 18 and 19 with appropriate cell references. Do not retype or copy-and-paste the text in Row 18 and the numbers in Row 19! You must choose an appropriate cell reference to display the text in Row 18 and the numbers in Row 19, as shown and discussed in class. You will be graded on this!
  2. Next, write a conditional statement using the IF function. This conditional statement should be written so that the output of the condition indicates whether a satellite office did well, or not, according to their total sales amount; that is, did they meet our goals? Fill in Row 20 with three city goals, which you can make up arbitrarily.

Row 21: Under the Insert menu or on the toolbar, choose function and look through either the complete list or the list of logical functions to find IF. The structure of the function is explained along with the pieces of information needed. We also went over this in class, and you have been given a really useful handout. The result of the IF statement should be to display text: that is, a term synonymous for BOON for stores that had good sales (equaled or surpassed the city goal), and a term synonymous with BUST for sales that did not meet or surpass the city goal amount. Make up your own words for those. Also, play around with the numbers until you have at least one with a different result (that is, a different outcome from the IF statement). Enter the formula in D21, and then in the other two city’s cells. NOW center the results.

  1. PIE CHART (fun and fast).. Make a pie chart corresponding to the sales data in the SUMMARY OF (insert your company name here) SATELLITE OFFICES section. To do this, select the appropriate cells within D18:J19 before you use the Chart button or select Chart from the Insert menu. Select the Pie icon from dialog box, and choose “Exploded pie with a 3-D visual effect”, then continue. Verify that your data range is correct and that Series is set to Row. Enter your chart Title as “NC (insert your company name here) Sales Summary”. Verify that “As object in” is selected. Close the chart toolbar. Make sure the chart is placed as an object in Sheet 1. Move the chart and resize it (note the handles at the corners; you should hold down the shift key to preserve the scale) so that it fits into the spreadsheet in approximately the same place as the one on the sample (doesn’t have to be exact, of course). The easiest way is to place the upper left corner where you want it, then grab the handle on the bottom right corner and resize it appropriately. SPECIAL NOTE: If you want to go back and modify a chart, simply double-click on the chart, and a Chart Options dialog box will appear. Feel free to experiment with some of the OPTIONAL chart features if you wish. They’re easy to use, and very useful to know.
  2. Next, enter the text “For week ending:” into cell A2, then use either the NOW or TODAY function in B2 (format the date as it appears in the sample) to ensure that the system date is used. In cell J23, type in your first and last name, making it bold and italic.
  3. PRINTING. First, make sure that the columns are sized correctly and set it up to print in landscape orientation. Look in Print preview to see that it all fits on one page. Adjust the margins if necessary to get it on one page (you can do this while in Print Preview—click the Margins button), and then print the spreadsheet.
  4. Now do a second printout showing formulas. You must also be sure to print the Row and Column Headings (File/Page Setup/ Sheet Tab) for this printout. Make sure that you adjust the column widths and that the spreadsheet is set up to print in landscape orientation, then print the spreadsheet. Please fit the entire spreadsheet on one page. (Hint: You may reduce the page margins, or you may also print your sheet at less that 100%. Select File, and then Page Setup, where you can experiment with Scaling: try 90%, for example, then click on the Print Preview button right in that same dialog box; continue experimenting until Print Preview shows it fits on one page.)

v  Remember that formatting will look strange when you print showing formulas. That’s ok.