BA 409 Lab 1 Excel FUNdamentals Spring 2005 In this lab you will modify the file Excel Review which consists of three worksheets that cover

Document Sample
BA 409 Lab 1 Excel FUNdamentals Spring 2005 In this lab you will modify the file Excel Review which consists of three worksheets that cover Powered By Docstoc
					BA 409                     Lab 1: Excel FUNdamentals                                      Spring 2005
In this lab, you will modify the file Excel Review, which consists of three worksheets that cover most of
the basic (and hopefully a few advanced) features of Excel that we will be using the rest of the semester.
The first step is to open the file from the Shares drive:
          s:\parker\BA 409\Labs\Excel Review.xls
You should save the file somewhere that you will have access to it later. I’ve created a workspace for
each of you under my Shares folder – in fact, that would be a good place for you to turn in your lab.
Now you are ready to go! The spreadsheet has the basics, you need to fill in the details and do a little
formatting. Work at your own pace, if you need help ask; if you finish quickly, help others around you
who aren’t as fast.
The final spreadsheet you create should look something like the attached pages. Note: You should not be
manually entering numbers or adding dollar signs – find ways to get Excel to do this for you, a column at
a time.
The first worksheet requires you to develop a simple inventory model. A user of this spreadsheet would
update the on-hand column to determine the order size (needed column) and total order cost (total
cost column). If you are ordering 5 or fewer of an item, your price is discounted 30% of retail; 6 or
more the discount is 40% of retail.
The skills you should master on this worksheet are:
   1. Enter labels and formulas
   2. Edit and delete cell entries
   3. Format cells
   4. Left justify, right justify, center, and center across columns
   5. Change column widths and row heights
   6. Use an “IF” command to determine whether the reorder price is the >5 or <6 price in calculating
         the “Order Cost” column
   7. “Fill” formulas down a column (or across rows) by entering the formula once and then left-clicking
         once to highlight the cell and then dragging from it’s lower right hand corner down (or across)
         the cells you wish to copy the formula to. When you do this, references will automatically be
         updated as well – for example, if you were to enter a formula =a2*b2 in cell c2, then the copied
         formula in cell c3 will be =a3*b3 (this is called relative referencing). If, on the other hand, you
         don’t want references to be updated when you fill a formula, you should enter a formula like
         =a2*b$2$ in cell c2, then the copied formula in cell c3 be =a3*b2 (note that b2) doesn’t
         change – this is called absolute referencing). An easy way to create an absolute reference is to
         position the cursor between the b and the 2 in a formula such as =a2*b2 and hitting F4, which
         will automatically update your formula to =a2*b$2$.
   8. Use a “Sum” command to calculate total cost

The second worksheet, Sales, requires you to create a couple of graphs with data that has already been
entered into the spreadsheet.
Here, you should:
   1. Highlight all the data and then select the graph wizard to create a bar graph
   2. format graphs (resize, add titles) – Most of this can be done while in the graph wizard. After the
        graph has been created, you can left-click on different parts of the graphic (such as title) to select
        them and then right-click to bring up a format menu.
   3. Highlight a single row of data to create a scatter plot
   4. create a Trend Line – once the graph is complete, click inside the graph – a data point is a good
        place – and right-click the mouse. One of the options should be “Add Trend Line” . If you click
        the “Options” tag, you can have Excel print the formula and R2 value on the graph (then click and
        drag it off of the data).
Sheet 1:
The last worksheet (Sheet1, to be retitled Receivable) should expose you to a few commands you haven’t
used. To complete this worksheet you will need to:
    1. use “Sumproduct” to calculate the sum of products. Rather than making the Total Due formula
        b3*c3 + b4*c4 + b5*c5, you can use a built-in function called Sumproduct. It’s form is:
        Sumproduct(range1,range2), so we could do something like: Sumproduct(b3:b5,c3:c5) but this
        gets hard to read after a while… it might be better to use names, so that our calculations make
        sense if we look at them later: Sumproduct(Hours,Labor) is more descriptive (well, at least to
        me). We’ll see a LOT of this command when we get to a topic called Linear Programming. (see
        note below about how the names Hours and Labor were entered)
    2. rename the worksheet – simply right-click on the name tab at the bottom of the worksheet and
        one option that appears is “Rename”

Note: It is possible to name cells (for direct referencing, rather than relative referencing). Rather than
using $a$2 in a formula to ensure that as that formula is copied to other cells, the reference remains to
cell a2, you can also NAME a2. To do this, left click on a cell (for illustration, let’s say b7). Move the
cursor to the Name Box (to the left of the formula box – it will say b7) and left-click. This will highlight
b7. Now type a name for this cell (“stuff” or something) and hit Enter (if you forget this step, you haven’t
yet named the cell!). Now, you can refer to cell b7 as stuff in formulas. In fact, you can name ranges of
cells (look at the Name Box when you highlight cells b3:b5 or c3:c5) to name a range, simply highlight
the range and THEN enter a name in the Name Box as above.

Congrats! you are now an Excel Wizard (or at least well on your way!) If you are done early, see if
anyone around you needs a hand.
BA 409                     Lab 1: Excel FUNdamentals                                     Spring 2005

Inventory worksheet:

                                 Retail Goods - Top Ten Selling Items
                                                                         Wholesale Price For Qty                     Discount for Qty
                                       Retail    Qty to On
Description                                                    Needed      Under 6         Over 5      Order Cost    5 or less 6 or more
                                       Price     Stock Hand
Cast iron doorstop                      $29.99    25      20       5            $20.99        $17.99       $104.97        30%           40%
French purse                            $24.00    20      18       2            $16.80        $14.40        $33.60
Jewelry amoire                         $279.00     3       2       1           $195.30       $167.40       $195.30
Lead crystal votive                     $30.00    30      21       9            $21.00        $18.00       $162.00
Mangel clock                           $135.00    12       6       6            $94.50        $81.00       $486.00
Mickey Mouse pocket watch               $59.95    20      12       8            $41.97        $35.97       $287.76
Mohair throw                            $90.00    40      19      21            $63.00        $54.00     $1,134.00
Needlepoint footstool                   $39.99    15      13       2            $27.99        $23.99        $55.99
Needlepoint pillow                      $24.99    30      22       8            $17.49        $14.99       $119.95
Noah's Ark clock                        $39.99    18       9       9            $27.99        $23.99       $215.95

                                                                                     Total Cost          $2,795.51

              Joan's Accounting Service Billing
                                         Hours           Labor Charge
Aimee's CD O'Rama                                       3               $25
Bill's Bongo Emporium                                   8               $33
Cliff's Useless Factoid Outlet                         15               $27

                                         Total Due:                     $744
Sales worksheet:

                         Gift Shop Sales
                         Jan        Feb           Mar
Weeping Willow Gallery         12000      11000         15000
Live Oak Gifts                 13000      14000         12000
Cherry Street Market           10000      12000         13000
Victorian Tea Room              9000      10000         12000

Description: Calculate Cost of Sales in Excel and Inventory document sample