Using Excel Spreadsheets

Document Sample
Using Excel Spreadsheets Powered By Docstoc
					                                    COMP 4, Summer 2005 – Session II

                             Lab-05B: Using Excel Spreadsheets
………………………………………………………………………………………………………………………

                                         Total Points Possible: 40

                   Due Date: Friday, 08 July 2005 at 1:15 PM (start of class)
………………………………………………………………………………………………………………………
After attending class(es) and completing this assignment, you should be a little more familiar with
spreadsheet basics.

ATTACHED
        Sample spread sheet for Section II

BOOKS NEEDED
        Shelly Cashman Office 2003 workbook

FILES NEEDED (only for additional practice)
        The student data files used for the extra “Apply Your Knowledge” and “In the Lab” projects that
         come at the end of each Workbook chapter. These are for optional additional practice. You may
         download and install the self-extracting archive from our Web page.

ATTENTION
   be sure Caps lock (on the keyboard) is turned OFF
………………………………………………………………………………………………………………………

     SECTION I: Spreadsheet Parts and Excel Basics (For PRACTICE)
………………………………………………………………………………………………………………………

A. Introduction to Spreadsheets
It’s always a good idea to read through an entire assignment before you tackle it, especially for this and subsequent
assignments. Often, many of the questions you have (such as “what should the number of locations be? Mine is different
from the sample”) will be answered within the context of the assignment.

B. Workbook: Microsoft Office 2003
Please pencil the following notes, optionals, and skips in your Office Workbook:
PAGES           STEPS                              WRITE THIS:
EX 50 – 53      Correcting Errors                  NOTE: This is very useful, basic information on editing cell contents.
                                                   Play around with all of the methods shown, and select what you like.
EX 91 – 102 Read All                               Work any that are of interest.
EX 103 – 105 Conditional Formatting                Skip
EX 111 – 112 Spell Checking                        OPTIONAL (but very useful)

1.   Work through the rest of Project 1: first do pages EX 38 through EX 41: Creating a Chart. Now continue with the
     rest of Project 1, starting on EX 46 and working through EX 55, noting the changes in the table shown above.

2.   Work through Project 2 and do everything that is not WEB based, starting on page EX 65 (Formulas, Functions,
     Formatting, and Web Queries), STOP on page EX 120. Again, watch the table above.
                                                                                                                   2


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

          SECTION II: Working with Spreadsheets (FOR POINTS)
………………………………………………………………………………………………………………………
Some advice: Read completely through this section before sitting at the computer. Get a sheet of paper and write down all
the figures, ideas, and whatever else you might need to produce the spreadsheet. This will save you time while at the
computer, especially, if you’re in one of the campus labs.

Budgeting your startup expenses
Your new business venture has taken off! You are projecting to soon be making enormous amounts of money, but
initially have to spend a lot. It is time to create an expense report form to keep track of your startup costs. We have
attached a sample (also on the course web page) that you can refer to as you work through the requirements. Please do not
use our data. Create your own for your unique business.

Minimum requirements
1.   Create a new spreadsheet file. Save the file as: Lab05B-Expenses-LastFirst
2.   Create a title for the spreadsheet in a cell within the first row (title will overflow to other adjacent cells; that’s fine).
     Use the name of your business and indicate that it is a startup-cost expense form (see example). Make the title big
     (but not larger than 24 points), bold, and italic, and in Arial font (or something similar if you don't have it).
3.   Leave a couple of blank rows between the title and where you’ll start to enter everything else. Starting in cell A4,
     enter the following headings (labels) for your data, putting each heading in its own column: Quantity, Description,
     Cost Each, and Total Cost. Resize the columns, as necessary, to fit the column headings (not the spreadsheet title: let
     the title overflow). Make the headings bold and change their alignment to center.
4.   Creatively make up at least eight (8) items that you have had to purchase for your new business, which you will type in
     the appropriate cells. Examples might be one espresso machine at $2,699.00 each, two television sets at $289.00 each,
     etc. Think of items that would make sense for your unique business.
5.   Now enter your made-up data under each appropriate heading (i.e. quantity in column A, description in column B, and
     cost each in column C). Next, you must enter a formula to calculate a Total Cost for each item. Starting with the
     first item in your budget, enter a formula that multiplies the Quantity by the Cost Each. (If you are having trouble, you
     might check out page E 2.09 to refresh your memory about formulas.) You must use a formula, do not calculate the
     total on your own. Type the formula only once and then fill the formula down through the rows using the fill-handle.
     Next, change the width of the columns as you see fit to make it look nice. (Hint: Use auto width for formatting from
     Lab5A).
6.   For the two cost columns, format the numbers in those cells as Currency format (use US dollars with 2 digits after the
     decimal point). You can use the increase decimal button on the toolbar to help you enter decimal places.
7.   At the bottom of the Total Cost column, enter a formula using the SUM function to add together all of the cells in the
     Total Cost column, to give us a grand total. Again, you MUST use a formula with the SUM function here. Type the
     word TOTAL under the last item in the quantity column (see our sample). Make the word TOTAL and the actual total
     cost bold to make them stand out (again, refer to our sample).
8.   Add a border around the cells that contain the column headings and the data. Use both Outline and Inside borders
     (called “all borders”) so each cell is bordered (like the sample). Use a line style of your choice for the border.
     (Remember to SAVE often!) Now Print out what you have so far.
9.   Whoops! Both your coffee grinders broke and you had to purchase two new ones. Add it to your list: two (2) coffee
     grinders at the price of $399.95 each. Insert a new row somewhere in the middle of your existing list of items and
     enter the data appropriately. Be sure to copy and paste or fill down the Total Cost formula in the appropriate cell in
     the newly inserted row. Note: Look at the Total formula where you summed the Total Cost column. Notice that the
     range of cells now INCLUDES your newly inserted cost. Neat, huh? Remember this! It's an important concept about
     spreadsheets that you will be expected to understand and recall.
                                                                                                              3


10. Finally, go a few rows below all the previous entries, and type your name in column A. Save. Print a copy of this
    spreadsheet and save that copy to your Turn In disk.
11. Now, you want to experiment with different startup-costs by changing the quantity and items with which your business
    will be equipped. Use the Save As feature and save the file with the name: Lab05B-Alternate-LastFirst In addition,
    change the title to reflect that this is an alternate expense estimate. Change at least half of the data values (numbers)
    and observe what happens. Notice, how the formula automatically updates the totals when you change the data.
    Change at least two of the item names. Save your file again. Print a copy of this alternate expense report.
12. Good news! Your sister just graduated from Stanford University and wants to start up a satellite office of your
    business out West. In fact, you have a big family, so you want to study how your costs would increase if you opened
    more offices. Open the file Lab05B-Expenses-LastFirst and Save it As: Lab05B-MoreOffices-LastFirst.
13. First, determine which of your expenses would increase if you open satellite officesselect five such expenses (i.e.,
    you may need espresso makers for every location you open, but one only computer system for the home office). It
    does not really have to make business-sense. Second, find a blank cell a couple rows beneath the rest of the
    spreadsheet data, and enter the words: “Number of locations:” (merge two cells so that this text will fit). Enter the
    number 1 in the next cell. (Refer to our example.)
14. Now the harder part. Edit one of the five formulas in the Total Cost column (one of the five that would change due to
    expansion) to multiply the resulting Total Cost by the number of locations. You should use an absolute address for
    the cell where the number of locations is stored, and then copy that formula into the other four cells that would be
    affected by adding new offices. Make the five cells with the new formulas bold, and add a note at the bottom of your
    spreadsheet noting that the Total Costs in bold have been multiplied by the number of locations, as documentation of
    what you did to plan for expansion. Recall that a quick way to make a lot of cells bold is to make one cell’s contents
    bold, then with this bold cell selected, click the Format Painter button on the toolbar (little paintbrush). The next set of
    cells you drag over will then pick up all the formatting the first cell had, including number formatting.
15. Now, here’s the “what-if” part: enter different values for the number of locations cell (example: try 2, try 3, try 5,
    etc.one at a time, of course), and as you enter each value, observe how total cost changes for some items, while
    others stay the same, based on the number of offices you may have. Another important concept to remember!
16. Finally, after you've done some experimenting in #15 above, enter 4 as your final number of locations (row 16); Save
    the file, then print this spreadsheet.
17. Add 3-D Column Chart (charting the total costs) to your spreadsheet, and then print the spreadsheet again.
18. Now you are going to do another printout that displays the formulas. Press the ` key that is left of the number 1 key
    while holding down the <CTRL> key (see also page E2.56 – E.2.57 in the workbook). This will cause the spreadsheet
    to toggle the view to display the formulas (instead of the values) in each cell. (It might also mess up you column
    spacing, but you may resize your columns). Next, go under File/Page Setup. Click on Sheet tab. Make sure you
    select Print Row and Column Headings. (You can also choose to print all the gridlines from this menu, if you’d like.
    Or you may prefer to use the inside/outside borders button on only those areas where you want gridlines visible.)
19. Before you print, please adjust the column widths. First, select columns A through D. You can select a whole column
    by clicking on the letter at the top of that column. To select multiple columns, in this case click on A and hold, move
    the mouse to the right until columns A through D are selected, then release the mouse button. Next, under the Format
    menu, choose Column, then AutoFit Selection to set columns to the appropriate widths. You may want to manually
    decrease the width of column A. Don’t worry that the title up above does not spill over into adjacent empty cellsit
    does not do that when you are showing formulas. Just make sure the formulas are showing in their entirety. Also,
    formats will look different in this view (e.g., numeric values will be left aligned instead of right aligned, etc.).
20. Set to landscape orientation, use Print Preview feature to see how it will look, and finally print out this spreadsheet.
    To change this setting, go to Setup/Page/Landscape.
………………………………………………………………………………………………………………………

            SECTION III: Spreadsheet Functions (FOR POINTS)
………………………………………………………………………………………………………………………
1. In Lab 5-A, you had created a brand new spreadsheet for Section II, called Lab05A-Grades-LastFirst.
                                                                                                               4


2.   Open that spreadsheet file (or recreate it from Section II of Lab 05A if you didn’t do it the first time). Save it as
     Lab05B-GradesNew-LastFirst
     NOW STOP: How many Excel data files are still in memory? Only one! Not both! Only the one that you Saved As.
     The first file that you loaded into memory is no longer in memory. If you wanted to work with it again, you would
     have to load it from the disk back into memory. And, of course, you could load several more data files, if you wanted.
Now, you are ready to do some interesting things with this spreadsheet.
3.   In column E, enter a formula to calculate the total weighted score (cell E4) for the first student, taking into account
     the weights from instruction 3. The total weighted score will require multiplying each score by its weight, and adding
     each of these products together. Format the total weighted score to show one digit after the decimal point (remember
     the handy increase or decrease decimal point button on the tool bar).
4.   Now copy the formula for the other students with the Fill handle or use the Fill command.
5.   Then calculate a simple sum of the weights in row 16 using the appropriate formula and stored it in E16. That sum
     represents the total weight of all exams taken.
6.   FOR EXTRA CREDIT! (5 raw pts). In column F, enter a formula to calculate the first student’s weighted average
     score*. Make sure your formula uses absolute references where appropriate so it can easily be copied with the fill
     handle. Format the weighted average grade to show one digit after the decimal point. Now fill the formula down for
     the other students.
         *Hint: Hmmm, you now have a weighted total score for that first student. What number should you
         divide his/her weighted total score by to get a weighted average score? (Careful! Imagine that some of
         the scores or even some of the weights may change later. Yet, everything should still work without you
         having to change the formulas again!). You have to do all this correctly to get the extra credit points.
7.   Continuation of Required For Points section. Add a section starting in row 18 (similar to the worksheet in Project
     2) where you display the average, maximum, and minimum for each exam. Use the appropriate functions to
     calculate these numbers. Make sure to label each row so you know what the numbers represent. Type your first and
     last name somewhere on your sheet and Save your spreadsheet before printing it.

     Print your spreadsheet twice, making the following changes. The first printout should show the correct values. The
     second printout should show your formulas (see pages EX 118 – 119) and it should be printed using landscape
     orientation. Be sure to auto-fit the column widths and to use Print Preview before printing! Most of the formatting will
     not be shown correctly whenever you change the view to “Print With Formulas Showing.” That’s perfectly fine. The
     whole purpose of printing with formulas showing is to examine the formulas themselves (the logical structure), not the
     formats (the physical structure.)

                                               MAKE BACK-UP COPIES
……………………………………………………………………………………………………………………………

                                                     TURN IN
……………………………………………………………………………………………………………………………
 The six printouts from Section II
 The two printouts (one showing values, one showing formulas) of the spreadsheet from Section III (*)
 The above-named files on blackboard.unc.edu using the assignment tool.
                                                                  (*) with “Pledge” and your signature written on the front.
……………………………………………………………………………………………………………………………

                                       LAB-05B SUMMARY
……………………………………………………………………………………………………………………………

 Section II
                                                                                                      5


  1.    Create a new spreadsheet and save it as Lab05B-Expenses-LastFirst
  2.    Enter a title “…………… Startup Expenses”in the first row: large (up to 24 points), Arial, bold, italic.
  3.    Starting in A4: enter the four labels shown on the sample (Quantity, Description, Cost Each,Total
        Cost); bold and centered; resize as needed.
  4.    Enter items that you need to purchase for your business, their quantity, and their costs each.
  5.    Enter a single formula that will calculate a Total Cost for each item, and fill down. Change column
        alignments as needed.
  6.    Format cost columns as Currency (two digits after decimal).
  7.    Enter appropriate SUM function formula for the grand total. Put a border around column headings
        and data. PRINT NOW.
  8.    Add somewhere within middle of the existing table (not first or last) a new item: a Coffee Grinder
        that costs $399.95 each and of which you have purchased 2.
  9.    Type your name a few rows below all that. PRINT NOW.
  10.   Save AS another file (to experiment with alternate expenses): Lab05B-Alternate-LastFirst.
        Amend title to reflect that this is an alternate expense calculation, and change at least HALF of
        the data values; observe. Now change two item names. PRINT NOW.
  11.   Open the Lab05BExpenses-LastFirst file and save it as Lab05B-MoreOffices-LastFirst.
  12.   Add the “Number of locations” subtitle appropriately (merging two cells for this). Enter ‘1’ in an
        adjacent cell (see example).
  13.   Select FIVE items (lines) that will also be needed in the new offices.
  14.   For ONE of those FIVE lines, edit the formula Total Cost. Multiply the total cost by the number of
        locations (use an absolute address reference for Number of Locations’ cell, then copy the modified
        formula to the four other lines you selected in item #13). Make these five new formulas BOLD.
        Add a notation about this toward bottom of sheet for documentation.
  15.   Enter DIFFERENT values for the number of locations. Just try out different numbers. Observe.
  16.   Enter 4 as final number of locations. PRINT NOW.
  17.   Add 3-D column chart; PRINT NOW.
  18.   PRINT OUT again with FORMULAS showing (be sure to adjust column widths and set to Landscape
        orientation).

Section III
  1.    Open the file Lab05A-Grades-LastFirst from Lab05A. Save it as Lab05B-GradesNew-LastFirst.
  2.    In Column E, enter a formula that calculates the total weighted score (E4) for the first student.
        Format that total weighted score with ONE digit after decimal point. Fill formula for other
        students.
  3.    Calculate a simple sum of the weights, using appropriate function and store it in E16.
  4.    FOR EXTRA CREDIT (5 raw pts): In Column F, enter a formula to calculate the first student’s
        weighted average score (one digit after decimal point), using absolute/relative references
        appropriately! Fill down.
           a.   Remember: scores may change later, weightings may change later; formulas must still work
                without editing.
  5.    Add a section that will display the average, maximum, and minimum, using appropriate functions to
        calculate each. Include labels.
  6.    Type your first and last name.
  7.    PRINT NOW (use autofit).
  8.    PRINT AGAIN showing formulas (in landscape, and use autofit)
6

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:9/15/2012
language:Latin
pages:6