123456

ISM003 Managing Information System

Excel Assignment 3 Instructions (Office 2007 ed.)

EXCEL ASSIGNMENT 3: ADVANCED OPERATIONS
Your finished Excel worksheet should be similar to the following sample. Follow the instructions in the following pages and complete the Excel worksheet.
Enjoy it!

Page 1 of 9

ISM003 Managing Information System

Excel Assignment 3 Instructions (Office 2007 ed.)

Step 1: Basic controls

Home

Select

You may change the font and font size here.
Use Arial and font 12.
Click this box to select the whole worksheet.
You may scale the view here.

STEP 2: INPUT TEXTS AND DATA

Highlight the cell F2
Select Formulas

Insert Function

Enter the system date in cell F2 using the NOW function.

Page 2 of 9

ISM003 Managing Information System

Excel Assignment 3 Instructions (Office 2007 ed.)

Select the function
NOW
Click OK button

Highlight F2 -> right click mouse select “Format Cells”

Select Category

Select Type

Date

14/03/01

Click OK

Highlight A8:A9
Select Home

Alignment

Increase Indent

Highlight A13:A14
Select Home

Alignment

Increase Indent
Page 3 of 9

ISM003 Managing Information System

Excel Assignment 3 Instructions (Office 2007 ed.)

STEP 3: WRITE FORMULAS
Apply the AutoSum

function to calculate the Total value in cells F4:F6.

The Planned Indirect Expenses in the range B13:B14 are to be prorated 按比例分配 across the Lingnan Event Management Co. as follows:
a) Conference Administrative (cell B8) =

Administrative Expenses * Conference Revenue / Total Revenue
i.e. =$B13*B4/$F4

Why do we add the dollar signs ($) in front of B13 and F4 in the formula? This

is the most advanced and useful part in this lab exercise. (Only the column

reference is fixed. It will not change during a fill or copy, but the row will change.)

b) Conference Marketing (cell B9) =

Marketing Expenses * Conference Revenue / Total Revenue
Please try and write the