Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Conference Budget Excel Excel Level 1 by pcd13167

VIEWS: 89 PAGES: 20

Conference Budget Excel document sample

More Info
									                                          Excel Level 1
                                                     Updated for Version 2002

                                               Skills for the Electronic Workplace




Table of Contents

Table of Contents...........................................................................................................................................................1
Excel Basics...................................................................................................................................................................2
    Understanding Your Screen.....................................................................................................................................2
        Menu Bar............................................................................................................................................................2
        Toolbars .............................................................................................................................................................3
        Formula Bar .......................................................................................................................................................3
        Office Task Pane ................................................................................................................................................3
    Entering and Editing Information ............................................................................................................................4
    Formatting Cells ......................................................................................................................................................5
    Cell Alignment.........................................................................................................................................................5
    Filling Down a Formula...........................................................................................................................................6
    More Calculations....................................................................................................................................................6
    Inserting and Deleting Rows and Columns..............................................................................................................7
    Copying and Moving Data.......................................................................................................................................8
More Practise with Excel Formulae...............................................................................................................................9
Excel Workbooks ..........................................................................................................................................................9
Linking ........................................................................................................................................................................11
Importing non-Excel Data ...........................................................................................................................................11
More Functions............................................................................................................................................................12
Charts...........................................................................................................................................................................15
Printing ........................................................................................................................................................................18
Summary......................................................................................................................................................................19



Permission to use this document for non-commercial purposes, in original or modified form, is
granted, provided that the original source of the document is acknowledged as Information
Systems and Technology, University of Waterloo.

Microsoft Excel is an electronic spreadsheet program that records, analyzes, and calculates data.
The main reason people use Excel is to eliminate repetitive calculations. Excel can also organize
and chart data.

This hands-on session was created using Excel 2002 (part of the Office XP suite). Most of the
instructions will work no matter what version you are using, but there may be some that are




SEW                                                                                                                                                                          1
different in older versions of Excel. If you are a Macintosh user, please refer to Appendix “A”
for a list of differences.

In this document you will notice instructions written as (Format=>Font). This
means choose “Font” from the “Format” menu.

In this course you will be using Excel to look after the budget for the
“Canadian Cybernetics Conference”. There are several things that
we need to keep track of, and information that we need at our
fingertips. We will end up with several worksheets containing
the following information:

•   the budget, containing revenues and expenses (general expenses,
    registration package, promotional items, and banquet expenses)
•   hotels
•   promotional items


Excel Basics

While we will have a worksheet for each one of these items, we need all of these sheets in one
file, called a workbook. Let’s get started.

Understanding Your Screen

1. Start Excel. You may invoke it from the Start=>Programs menu, from the Office Toolbar
   which may be on the right side of your screen, or from the Quick Launcher.


Menu Bar



All the Excel commands are accessible from the menu bar. Clicking on any menu item will
reveal a “pull-down” menu, containing a list of commands. You click on the command you want
to activate. Notice the “Type a question for help” box. Clicking on it, and typing your question
will provide help.




SEW                                                                                               2
Toolbars




The toolbar is another way to invoke Excel commands. There are several toolbars available.
There are two rows of toolbars listed above, the Standard (top) and Formatting (bottom).

Formula Bar



The formula bar displays the location and data in the cell you are currently clicked on in the
worksheet (the active cell). The contents of the cell can be edited here.

Office Task Pane
The office task pane appears on the right of the Excel window. It contains the “New Workbook”
pane initially.




There are four panes you can choose from, as shown below:




SEW                                                                                              3
The clipboard, search and clip art panes will also automatically appear when you choose those
items from the menus. You may decide that you would rather use all of your screen for the
worksheet. You can close the pane.

Entering and Editing Information
You should be looking at a blank worksheet containing rows and columns of rectangular boxes.
These boxes are called cells. Each cell’s location is identified by a column and row (e.g. C1,
means column C and row 1). If you just start typing, the information will go into the “active”
cell, the cell highlighted with a border around it. You can change the active cell by using the
arrow keys and/or tab key, or clicking on another cell with your mouse. We are going to create a
sheet that looks like the following to keep track of promotional items.

In any cell you can place one of three things:
    • Text (i.e. labels)
    • Numbers
    • An Excel formula




SEW                                                                                             4
2. Starting in cell A1, type in “Item”.

3. Tab to cell B1, and type in “Cost per Item”.

4. You will notice that column “B” is not quite wide enough to accommodate the label. You can
   make it wider by simply double clicking on the divider between columns “B” and “C”.
   Alternatively, you can drag the divider to the right with your left mouse button.

5. Click your mouse in cell C1, and type “Qty Purchased”. Make column “C” wider to
   accommodate the label.

6. Finish typing the remaining labels in Row 1, widening any columns that need more space.


Formatting Cells

7. Select all the items you just typed in Row 1 by dragging your mouse through them while
   holding down the left mouse button. Then click on the bold button, to make the selected
   text bold. You will have to make some of the columns wider again because bolding takes a
   little more space. There are lots of ways to format text.

8. Type in all the information under the titles “Item” (Column A), “Cost per Item” (Column B),
   and “Qty Purchased” (Column C). Don't type the "$" signs.

9. Let’s format the items under “Cost per Item”. Select these items, and click on the button
   for currency style.

10. Click on cell D2. In this cell you want to enter the formula for Excel to calculate subtotals.
    This is simply “Cost per Item” multiplied by “Qty Purchased”. Type in the following
    formula:

   =B2*C2

   and press the Enter key. Rather than just typing “B2*C2”, a better way is to actually click
   your mouse on B2, type an “*”, and then click on cell C2. The less typing the better for most
   people, while others prefer not to take their hands off the keys. An important point to note
   here is that Excel formulae start with “=”. If at any time a cell fills with "#####"s, it means
   the column isn't wide enough to display the data it contains. Simply make the column wider.


Cell Alignment
11. Notice that the information you entered under "Qty Purchased" is right-aligned. To centre
    this information first select cells C2 to C5, then click on the "Center" button.




SEW                                                                                                  5
Filling Down a Formula

12. We could enter similar formulae for cells D3 through D5, but there is an easier way! Select
    cells D2 through D5. From the “Edit” menu, choose “Fill” and then “Down”
    (Edit=>Fill=>Down). That was easy! There is even an easier way to do a “Fill”. All you do
    is click on the cell that you want to fill, and drag the cell from the bottom right corner (where
    you see the small square) to the other cells.


More Calculations

13. Now let’s have Excel calculate the GST. Click on cell E2. Enter the formula

   =D2*.07

   and press the Enter key.

14. Fill down the GST for the other cells in this column.

15. Complete the same procedure for the PST (with the exception, of course, that you will be
    multiplying by .08).

16. Now for the column “Total Expense”- Click on cell G2. Enter the following formula:

   =SUM(D2:F2)

   and press Enter. The D2:F2 is a range, meaning from cell D2 to F2 inclusive. Rather than
   type D2:F2, you could have just selected the cells with your mouse.

17. Fill down the formula (to cell G5).

18. In cells H2 through H5, enter the numbers for “Quantity Sold”.

19. In cells I2 through I5, enter the numbers for “Price Sold”. Format these cells to currency by
    using the Format=>Cells menu.

20. Now for total revenue. Click on the cell J2. Enter the formula:

   =H2*I2

   and press Enter.

21. Fill down the formula (to cell J5).

22. We want to sum the total expenses and revenues. Click on cell G6, and enter the formula




SEW                                                                                               6
                                                           Hint: You could have just selected
   =SUM(G2:G5)                                             cell G6, and then checked the

   and press Enter.                                        Autosum button



   If you click on the arrow beside the autosum button you will see a list of other functions:




23. Do the same to calculate total revenue.

24. Notice that cells G5 and J5 are underlined, or have a bottom border. To do this, click on cell
   G5 and click on the border button.       If you click the “down” arrow, you will see other
   types of borders. Put a bottom border on cell J5 as well.


Inserting and Deleting Rows and Columns

25. Suppose we wanted to add a new promotional item, say a “baseball cap”. Let’s put this new
    item in between “Bags” and “T-shirts”. In the area where the rows are numbered, click on
    row “5”. This should have selected all of row “5”. Now from the “Insert” menu, choose
    “Row” (Insert=>Rows).

26. Let’s say we changed our minds about the baseball caps and now want to delete this new
    row. Click on the same “5” (the row number) and from the “Edit” menu, choose “Delete”.
    (Hint: we could have just chosen “Undo” from the “Edit” menu. (Edit=>Undo).

27. Adding and deleting columns is very similar to rows. Suppose we want to add a new column
    between columns “A” and “B”. Select column “B” by clicking once on the “B” column label.
    This action selects the whole column. From the “Insert” menu, choose “Column”.

28. Can you figure out how to delete this newly created column? (Try using Help to find out).




SEW                                                                                              7
Copying and Moving Data

There are two ways to copy and move data on a worksheet. The first way involves using menus
and/or tools on the standard toolbar. The second way is to use your mouse and drag the cells you
want to move or copy. Let’s practise using the menus and/or toolbars.

29. Select cells A1 through A5.

30. From the “Edit” menu choose “Copy” (Edit=>Copy). (If you wanted to move the data, you
    would choose Cut)

31. Select the cell (any cell) to where you want to move the data.

32. Choose “Paste” from the “Edit” menu (Edit=>Paste).

Rather than use the Edit menu to copy/cut/paste, you could use the buttons
Hint: An alternative method for copying/moving cells is to first select the cells, then drag them
from the border of the selection area with your right mouse button. A window will appear and
you can choose "Copy Here".

You will notice the     graphic appears when you do a paste. This is called a smart tag. Click on
the the clipboard and a down arrow appears. Click on the down arrow and the following list
appears:




This can be useful for formatting cells.

33 Clear the cells you just copied, by first selecting the cells with your mouse and then choosing
   “Clear” and then “All” from the “Edit” menu (Edit=>Clear=>All).

We are finished with this worksheet for now. Save it in your folder on the M-Disk (File=>Save),
and call it “Promotional Items”, as demonstrated by the instructor. Close the worksheet. Please
note that if you are doing this on your machine, you will be saving the files in a folder on your
hard disk.




SEW                                                                                             8
More Practise with Excel Formulae
We are now going to work on a worksheet to keep track of the budget.

34. From the “File” menu, choose “Open” (File=>Open). Open the file called
    “Conference_Budget”. To save you some time, this worksheet has already been started. You
    are going to enter all the formulae.

35. In cell “D5” enter the formula to add cells D3 and D4.

36. In cell “D11” enter the formula to add cells D7 through D10 inclusive.

37. In cell “D13” enter the formula to calculate the total revenue (sum of “D5” and “D11")

38. In cell “C27” enter the formula to calculate the total of “General Expenses”.

39. In cell “C36” enter the formula to calculate the total of “Registration Package”.

40. In cell “C53” enter the formula to calculate the total of the “Banquet Expenses”.

So far this has been pretty straightforward. Next we have to enter the values for the promotional
items. You will recall that this information is in the worksheet we saved earlier, called
“Promotional Items”.

41. Open this worksheet now, without closing the “Conference_Budget” worksheet.


Excel Workbooks
42. Click on the “Window” menu. You will notice your two worksheets at the bottom of the
    window. You can click on the worksheet that you want to work on from the “Window”
    menu. Try this on the two worksheets that you currently have open.

It would make sense to have all your worksheets in one file, called a workbook. This way you
don’t have to remember the names of a lot of files.

43. From the "Window" menu, click on the "Promotional Items" worksheet. From the "Edit"
    menu, choose "Move or Copy Sheet…". The following window will appear:




SEW                                                                                            9
44. We want to copy the "Promotional Items" worksheet to the "Conference_Budget" workbook,
    at the end of the existing worksheets. Change the above window so it looks like the
    following, and then click on the "OK" button:




45 From the “Window” menu, click on the “Conference Budget” worksheet. At the bottom of
   the worksheet you will notice:


On your Excel you may have more, or fewer, sheets listed. This is an option you can set from
Tools=>Options menu. Select the General tab, and you will notice that you can change the
“Sheets in a New Workbook”.

46. Let’s rename Sheet1 (2). With your right mouse button click on Sheet1 (2), and choose
    “Rename”. Now just type “Promotional Items” (without the quotes). You will notice that you
    can also change the tab colour.

47. Do the same to rename Sheet1 to “Conference Budget”. Delete Sheet2.



SEW                                                                                            10
48. You can reorder worksheets, by clicking on the worksheet name with your left mouse button,
    and dragging it in front of, or behind another worksheet. Try it.

We now have a workbook containing two worksheets. Save the workbook, this time calling the
file “Cybernetics Conference”.


Linking
49. Click on cell C39 in “Conference Budget” worksheet. This cell is to contain the cost of Pins
    from our “Promotional Items” worksheet. We could lookup the value, copy and paste it, or
    link it. Linking is best, as the value may change at some point down the road, and linking
    would mean we always have the correct values. Type an equals sign (=), click on the
    worksheet tab for “Promotional Items” and then click on cell G2 and press Enter. Take a look
    at the formula that has been created by clicking on cell C39 again in the sheet “Conference
    Budget”.

50. Repeat this process for the other promotional items in the “Conference Budget”. (Note: You
    could use a Fill Down). Calculate the “Subtotal” for promotional items in cell C43.

51. Try changing a value in the “Promotional Items” Sheet (e.g. Quantity Purchased) and see if
    the linked value in the “Conference Budget” changes correctly.

52. Now type =C27+C36+C43+C53 in cell C56 to calculate “Expense Total”.

53. Now enter the “Net Gain/Net Loss” in cell C58. (=D13-C56)

54. Save your worksheet.


Importing non-Excel Data
It would be useful to have hotel information at our fingertips in our workbook. Perhaps someone
else has this information in a word processing document. In your folder there is a file called
“hotels.doc”. This file is saved as normal text. It has all the information we need, so there is no
need to re-type it into Excel. The information is in columns, separated by tabs (it is tab
delimited). Note that importing data in earlier versions of Excel is different. Use help for
instructions using earlier versions of Excel.

55. From the “File” menu, choose “Open” (File=>Open). A window will open that will allow
    you to select the file you want.

56. Select your folder. You should notice that only Excel files are displayed. We need to make a
    change so that all files are displayed. At the bottom of the window, in the area titled “Files of
    Type”. Click the “down arrow” and choose “All Files (*.*)” as shown below:




SEW                                                                                              11
57. Now the file called “hotels.txt” should be displayed. Double-click on it, and the Text Import
    Wizard will start up and display the following window.




58. Make sure that “Delimited” is clicked, and then choose “Next >”, located at the bottom right
    of the Window.

59. The next window will allow you to indicate how the file is delimited. Choose “tab”. Click on
    “Finish”. Adjust the column widths to accommodate all the data.

60. The file should now be in Excel. Format the headings (bold).

61. Select all the data and copy it to our clipboard.

62. Go back to our workbook called “Cybernetics Conference”. (Hint: use Window menu to get
    there). Click on cell A1 in a blank worksheet and do a paste.

63. Rename this sheet to “Hotels” and save your workbook.

More Functions
There are lots of functions available in Excel.

64. Click on the sheet called “Promotional Items”. Click on any blank cell.




SEW                                                                                           12
65. Click on the “Down Arrow” beside the AutoSum button          . The following list will
    appear:




 Click on “More Functions…”.

66. A window similar to the following will appear:




As you can see, the functions can be listed by category. Browse the categories to see how many
functions are available to you.

67. From this window choose the “Sum” function, click “OK” and you should see the following
    window:




SEW                                                                                          13
Select through some cells (any cells) in the worksheet and click on “OK”. You can clear the
function you just created (Edit=>Clear=>All).

68. Click on cell “K2”. Let’s use an IF function to alert us if we have run out of inventory on any
    of the promotional items. Enter the following function:

   =IF(H2>=C2,"Order More","More in Stock")

   In this function, we first check to see if “Quantity Sold” is greater than or equal to “Quantity
   Purchased”. If it is (i.e. the condition is true), we want the words “Order More” to appear in
   the active cell. If it is false, then we want the words “More in Stock” to appear in the active
   cell.

69. Fill the formula down for the other promotional items.

70. In cell A7, type in the words “Maximum Revenue”. Adjust the column width.

71. In cell A8, type in the words “Minimum Revenue”.

72. In cell B7, try using the “Paste Function” button to calculate the maximum revenue from
    promotional items. The function is the "max" function, =max(j2:j5)

73. In cell B8, try using the “Paste Function” button to calculate the minimum revenue from the
    promotional items. The function is the "min" function, =min(j2:j5)




SEW                                                                                            14
Charts
You can use Excel to produce very high quality charts. While some people use Excel to graph
scientific data, it is more suited to business type charts.

74. Click on the “Conference Budget” sheet. We are going to graph a pie chart of the “General
    Expenses”. The first step is to select the data that we want to graph. Select cells A16
    through A26 with your left mouse button.

75. Hold down the “Ctrl” key, and select cells C16 through C26 inclusive.

76. Now click on the “Chart Wizard” button

77. The following window will appear:




In the “Chart type:” area in this window you will notice all the different types of charts that
Excel can produce. Click on “Pie”. The “Chart sub-type:” window will change to the following:




SEW                                                                                        15
Click on the same type of pie chart that is indicated in the previous window (a pie with a 3-D
Visual Effect). Click on the “Next >” button and you should see a window that looks like the
following:




78. Click on the “Next >” button, which displays the following window. Enter a chart title (e.g.
   General Expenses).




SEW                                                                                           16
79. Click on the “Data Labels” tab. Your window should then change to the following:




Click on “Show percent”, and then click on the “Next >” button. The following window appears:




SEW                                                                                      17
In this window, notice that you can create your chart as a new sheet or as an object in the current
sheet.

80. Click on the radio button “As new sheet” and type in a name called “General Expenses”.
    Click on “Finish”. Notice that the chart becomes another worksheet in your workbook.

This may seem like a lot of steps to get a pie chart, but once you become familiar with the
process it actually goes pretty quickly. To edit a chart, simply click on it with your right mouse
button. A window will appear displaying a variety of options to change the chart (e.g. chart type,
chart options). Save your worksheet now.

81. As practise, try creating a chart of the “Registration Package” data. Choose any type of chart
    you want, and this time have the chart created as an object on the worksheet.

Printing
82. We are now ready to print some of our work. A good place to start is to choose “Page Setup”
    from the “File” menu (File=>Page Setup). You should see the following window:




SEW                                                                                            18
You can change various settings under the tabs “Page”, “Margins”, “Header/Footer” and
“Sheet”. Browse the four tabs to see what options are available. Under "Header/Footer" you can
create custom headers.

83. Print the “Promotional Items” sheet in landscape mode.

84. Print the “Conference Budget” without grid lines and on one page.

85. Sometimes you want to print an area of a worksheet. In the “Conference Budget” sheet,
    select all the “General Expenses”. Now from the “File” menu, choose “Print ” and then click
    on “Selection” from the “Print What” section.

Summary
You have now learned the basics of Excel. This includes the following skills:

•   why use a spreadsheet
•   starting Excel
•   worksheet concepts
•   familiarity with menus and toolbars
•   entering text, numbers and functions (SUM, MAX, MIN, IF...)
•   copying and moving data
•   filling data
•   inserting, deleting rows and columns
•   changing the column width
•   formatting


SEW                                                                                         19
•   creating, editing and formatting charts
•   printing
•   workbooks
•   linking to cells in other worksheets
•   importing non-Excel data.




SEW                                           20

								
To top