INSTITUTE OF FINANCE MANAGEMENT

ITU07205: BUSINESS COMPUTER APPLICATIONS

LAB PROJECT 2-5: MICROSOFT EXCEL- WORKING WITH

FORMATTING, FORMULAS & FUNCTIONS

Practical #1: Determination Of The Density Of H2o

1. Change the name of “workSheet 1” to “RawData” and of “WorkSheet 2” to

“Results” by right-clicking on the tabs at the bottom of the Workbook, clicking on
rename, and then typing in the new name.
2. In cell A1, Type “Determination of the Density of H2O: Raw Data”.
3. In cells A2 - A10, B2-B10, and C2-C10 enter the following data and format the data
as shown below.
m(cyl+H2O), g m(cyl), g Vol H2O, mL

35.912 31.099 5

35.381 30.155 5

36.043 31.12 5

36.026 31.12 5

36.379 31.151 5

36.306 31.11 5

36.037 31.098 5

36.019 31.119 5

4. Format the first and third columns to include 2 decimal places and the middle column
to 3 decimal places.
5. Click on the “Results” tab and enter the title “Determination of the Density of H2O:
Results” in cell A1.
6. Type the headings: “m(H2O), g” and “d(H2O), g/mL” in cells A2 and B2,
respectively.
7. In A3, type a formula that will subtract the value in B3 of “RawData” from the value
in A3 also of “RawData”. Copy the formula from A3 in the “Results” worksheet to

Page 1 of 6
 cells A4 to A10 in this worksheet. This gives you the mass of water for each set of
measurements in the “RawData” worksheet. (Check answers for correctness.)
8. In cell B3, type a formula that calculates the density of water from the mass of H 2O
on this sheet and volume, which appears in cells C3 through C10 on the other
Worksheet. Copy and paste this formula to cells B4 through B10.
9. In A11, type the heading: “Mean =” and right justify it.
10. In B11, type a formula to compute the average of the values in column B.
11. In A12, type the heading: “Std dev =” and right justify it.
12. In B12, type a formula to calculate the standard deviation of the same range of cells.
13. Express all numbers on this page to the correct number of significant figures.

ADDITIONAL QUESTIONS

1. How could you improve the layout of the spreadsheet with borders?
2. If your data was on Row 6 of the Spreadsheet, how far are you from the average
density of water as shown in this experiment? How far off is the class mean value
from the accepted value for density of water (1.000 g/ml)?
3. Is this data accurate, precise, or both? Why? Where do any measurement uncertainties
come from?

Practical #2: College Budget

1. Assume that, you are trying to show your parents where all the money is going to be
spent during the semester II at IFM from April to July in hopes of getting them to be a
little more generous and understanding when you call home for money. Add a new
Excel worksheet and name it College_Budget 2024 (followed by your names).
In the following cells, type in;
2. A1, (Your name)'s IFM Expense Spreadsheet for Semester 2 2024
3. A2, The Budget for Semester II 2024
4. A3, Created by (Your name/s), this day at (insert current date)
5. A4, Income, A10, Total Monthly Income, A12: Expenses, & B4, Month
6. B5, April, please use the drag-fill handle to get the rest of the months through July.
7. F4, Monthly Total

Fill out the Income area as follows:

A. Assume you make (Job) $200.00 a month working ten hours per week in food
service.
B. You have received $3000.00 (not fixed) as Financial Aid this semester. (April.)
C. Your parents give you $75 for April towards rent and other expenses (The amount
of money is not fixed).
D. You get $100 from HESLB monthly for meals and accommodation
** The amounts and variables provided in A-D are only for the sample; add the same by
estimating the amount of money you spend each month.**

Page 2 of 6
 Fill out the Expenses are as follows:

A. You have 17 semester weeks so your Rent was $27.00 in (April.), add the rest
with your figures
B. Stationary and Printing cost $100.00 in (April)
C. You have more than just rent and stationary & printing. We have given you A and
B to get you started. Come up with at least three to four other monthly expenses
and estimate how much those expenses will be. Hint, you may come up with an
estimate for food and drinks, Transport and communication, and so on. If you
have no idea, connect your thinking with colleagues. Be brave and creative!

Calculations

A. Label row 10 “Total Income” and the Sum each month in that row.
B. Leave one row blank after your expenses. Label the next row “Total Expenses”
and Sum each month’s expenses in that row.

C. Finally, label the row beneath it “Money Left” or “Net Income” and calculate the
difference between the “Total Income” and “Total Expenses”.
D. Adjust all the columns to make the spreadsheet readable.
E. Apply to format -all borders, merge and center to the Month, Monthly Total, and
Income, wrap text to the Monthly Total, font style-Arial, Bold all column
headings and the total amount for the Income, Expenses, and Money left.
If you are running in the red (-ve) it would show why you have been asking for extra money
each month, if not you should better rethink your strategy!

Page 3 of 6
 Practical #3: Working With the IF Function

Calculations

1. Create the worksheets shown above.

2. Set the column widths appropriately.
3. Find the Total marks of each student, where Total = Test Average + Project.
4. Using the IF Statement, Find the Final Grade of students. If Total is greater than
60, the Final Grade is "Pass", otherwise "Fail".
5. Find the Performance of each student. If the Project mark is less than 6, Performance is
"Poor", otherwise "Good".
6. Calculate the Class Average, Highest Mark, and Lowest Mark, and Count the
7. number of students.
8. Create a Header that includes the date in the left section and Time in the right section.
9. Create a Footer with the ID Number in the left section and Page Number in the center
section.

Practical #4: More IF function-Grade Computation

Page 4 of 6
 A. Add a new Excel “worksheet3” on the same workbook and name it Grade & Status.
B. Compute the grade and status columns using the description indicated below.

Criteria to Find Grade

Percentage and Criteria Grade
80% >= ------------------------- A
60% >= ------------------------ B
50 % >= ------------------------ C
< 50 % ------------------------ F
C. Find out the TOTAL and PERCENTAGE of all sessions using a function or formula.
D. Find out your GRADE using the above-specified criteria of all sessions.
E. Using SESSION_NO and PERCENTAGE create a line chart.
F. Find the average of SS2, SS3, through SS20 for R1, R2 and R3 respectively(see the work
above)
G. Insert 2D line chart of AVG vs Grade, and R1, R2, and R3.

Practical #5: Sales Performance –IF Function

You are a sales manager analyzing your team's performance over a quarter. The data includes
each sales representative's sales for each month and their targets. You need to use Excel
formulas to summarize and analyze the data.

Create a table in Excel with the following columns:

A: Sales Rep; B: January Sales; C: February Sales; D: March Sales; E: Target Sales; F: Total
Sales; G: Met Target; H: Bonus (10% of Total Sales if Target is Met)

Workings:

1) Enter the sample data into the appropriate cells.

2) Apply the formulas step-by-step as described.
Page 5 of 6
 Calculations

Verify your results: add a new column in each of the following (if applicable)

3) Calculate Total Sales for Each Sales Rep

4) Total Sales should show the sum of January, February, and March sales.
5) Using the formula =IF(F4>=E4, "Yes", "No") to determine if each sales rep met
their target

6) Met Target should indicate "Yes" or "No" based on whether the total sales meet or
exceed the target.
7) The bonus should be calculated as 10% of the total sales if the target is met,
otherwise 0.
8) Count the number of sales reps who met their target using COUNTIF.//new column//
9) Sum the total sales for those who met their target using SUMIF. .//new column//
10) Calculate the average sales for those who met their target using AVERAGEIF. .//new
column//
11) Calculate monthly Total sales, Maximum sales, Minimum Sales, and Average Sales.
12) Calculate monthly Total sales above $8000, Count of sales above $6000, and Average
sales above $7000.
13) Calculate the Number of Sales Reps Who Met Their Target
14) Calculate the Total Sales for Those Who Met Their Target
15) Calculate the Average Sales for Those Who Met Their Target
16) What was Alice's highest monthly sales figure?

Ver1-"Excel is not just a tool; it's the bridge between data and decision, turning numbers into
insights and insights into action."

Ver2- "Excel is not just a spreadsheet program; it's a gateway to transforming data into
powerful insights, streamlining workflows, and unlocking endless possibilities for innovation
and efficiency."

Page 6 of 6