Information Technology Center

Introduction To Microsoft Excel

Excel Lesson Four (Part II)

Every business operation strives to make a profit. Profit is what remains when we subtract all expenses from the income. For a trading company the monies they receive from selling goods is called SALES

DESCRIPTION:

This lesson demonstrates how Excel can be used to track quarterly sales, expenses and profit of a company.

SPECIFIC OBJECTIVES:

At the end of the lesson students should be able to :

  • Construct a sales & expenditure worksheet
  • Copy a formula by draging autofill handle
  • Format numeric data as currency
  • Calculate TAX and PROFIT as percentages

Excel Formulae

All formulae begin with an equal sign =

  1. A formulae must be entered in the cell in which the result is to be displayed
  2. All formulae begin with the equal sign
  3. BODMAS rule applies to all mathematical operations

ABC Trading Company

Exercise 4.3

Enter the data exactly as displayed below on a new sheet.

A B C D E F G
1 ABC Trading
2 Quarterly Sales Report
3
4 Sales Expenditure Profit before Tax Tax(15%) Profit after Tax Profit %
5 January 400000 150000 =B5-C5 =0.15*D5 =D5-E5 =F5/B5
6 February 650000 250000
7 March 550000 260000
8 First Quarter Total =B5+B6+B7
9

Table 4B

The value 250000 will be displayed in cell D5 after you enter the formular =B5-C5 and press the Enter Key.

If 250000 is not displayed after you press Enter then check your data and formula again.

Change Cell Format

You must be on the HOME tab for this exercise

  1. Click on D5 and drag the small autofill handle on the lower right of the cell down to D7
  2. Click on E5 and drag the small autofill handle on the lower right of the cell down to E7
  3. Click on F5 and drag the small autofill handle on the lower right of the cell down to F7
  4. Highlight the range from B5 to G7
  5. Locate the Number Ribbon
  6. Click on the Dropdown List
  7. Choose Currency from the list
  8. Change the Sheet name to ABC
  9. Save the file on your flash drive

Test Yourself

Choose the correct answer
Questions Options Verify
 1)  Table 4B has nine rows and six columns True False
 2)  The value 260000 is located in cell C3 True False
 3)  The value 550000 is located in cell B7 True False
 4)  Range B4 : G4 contains labels. True False
 5)  SALES is always more than Expenses True False

Activity 4

Study the layout of the worksheet displayed above then refer to Log On to IT for CSEC page 317 for further details.

  1. Copy the ABC worksheet to a new sheet
  2. Change the name ABC Trading to Optimum Trading Company
  3. Edit all sales and expenditure figures (Only) to reflect the data shown on page 317
  4. Rename the new worksheet : Optimum
  5. Save the file on your flash drive
  6. Print a copy of this worksheet for your portfolio
  7. Send a copy of your completed worksheet to Tutordam@yahoo.com

...........

End of Lesson Four - A Quarterly Sales Report Worksheet

The Tutor
Do you have a question or a comment?
tutordam@yahoo.com

Site Comments to: Webmaster