Download Practice Files and Enter Data

Download the practice file " Excelpractice2 " . (Right Click and choose Save Target As).  Save As "Budget"

Enter data

1.   Select Cell C6 and type "Sep-01" . Excel automatically changes the 01 to the current year, You may change it back by clicking on the cell and editing.

2.  Drag the Fill Handle form C6 to N6:

3. Enter the data in the blue box below under the Sep-01 column.

4. Resize column B by double clicking between the B and C headings

5. Save your work! Scroll back to the left edge of your worksheet.  Your worksheet should look like the picture below:

In column C (Sep-01) enter the following data:

Gross Revenue

·         Sales - 26900

·         Shipping - 5550

Costs of Goods Sold

·         Goods - 17710

·         Freight - 270

·         Markdown - 1240

·         Miscellaneous - 96

Expenses

·         Advertising - 4000

·         Salaries - 4700

·         Rent - 500

·         Utilities - 75

·         Insurance - 45

·         Telephone - 280

·         Office Supplies - 147

·         Training - 100

·         Travel - 200

·         Taxes - 240

• Interest - 800

Creating a Formula by Typing a Function Name and Range of Cells

Activity 1: Creating a Formula by Typing a Function Name and Range of Cells

Create a Formula in Cell C10 to find the sum of the gross revenue items in Cells C8 and C9 by selecting Cell C10 and typing =sum(C8:C9) and pressing Enter. 

Creating a Formula by Using the Insert - Function Menu

Activity 2: Creating a Formula by Using the Insert - Function Menu.      

1. Select Cell C17.  On the Menu Bar Go To Insert  Function. You may type a description of what you want to do, or -
2. In the Function Category box, select Math and Trig
3. In the Function Name box, scroll down and select Sum - Click OK
4. Look at the range of cells that Excel chooses to apply the function to - click OK

Creating a Formula by Typing and Selecting Cells

Activity 3: Creating a Formula by Typing and Selecting Cells

1. Cell C19 will contain your Gross Profit.  You'll enter a formula to calculate the difference between Gross Revenue and Cost of Goods Sold results.  Select Cell C19
2. Type an Equal (=) sign
3.  Select Cell C10
4. Type a Minus ( -) sign
5. Select Cell C17
6. Press Enter

   Formula bar

Creating a Formula by Using the AutoSum Button

Activity 4: Creating a Formula by Using the AutoSum Button

1. Select Cell C33
2. Click the AutoSum tool on the Standard Toolbar. (Microsoft Excel places the SUM function and a proposed cell range in Cell33.  The moving border encloses Cells C22:C32)
3. Click the AutoSum tool again or press Enter to accept the proposed range and enter the formula in the cell.

Excel XP has extended the functionality of the AutoSum button to allow you to choose other functions

Creating a Formula by Typing

Activity 5: Creating a Formula by Typing  

1.  In Cell C35, type =C19-C33
2. Press Enter
3. Save your work

Finding a Sum by using Quick Calculations

Activity 6: Finding a Sum by using Quick Calculations

If you want to display the total value of a range of cells, use the AutoCalculate feature in Microsoft Excel. When you select cells, Excel displays the sum of the range in the status bar, which is the horizontal area in Excel below the worksheet window. If the status bar is not displayed, click Status Bar on the View menu.

Suppose you need to know just the total value of Advertising, Salaries, Rent, and Utilities. Click on Cell C22 and drag down through Cell C25. In the Status bar you see the sum displayed - 9275.

You have now completed this module. Take a few minutes to review and practice what you have learned.