BCOMP1101/BIS1582/BIS1583 â— COMPUTER APPLICATION FOR BUSINESS
1. You are required to come out with a Microsoft Excel application to solve next Exercises
2. Open new workbook in Microsoft Excel and save as â€œGroup Assignmentâ€.
3. You are required to solve all the exercises and each exercise in different sheet.
This group project can have Maximum 3 students.
In this exercise, practice Conditional Formatting
1- Open a new sheet and Rename the sheet as â€œExercise 1â€
2- Type the next data and format the data as a Table.
3- Set up the rule that Excel formats any cell within a range with yellow fill color and dark yellow font color whenever it contains the word â€œDairyâ€.
4- Set up the rule that excel formats any cell within a range with light red fill color whenever it contains the amount value less than ($500).
In this exercise, practice simple and complex formula
1- Add new worksheet and rename as â€œExercise 2â€
2- Type next data in your worksheet 2.
3- Calculate Total cost for all items by using next equation:
(Total cost = Price * Quantity)
4- Insert new column to previous table and rename it as Sale Tax.
5- Calculate Sale Tax by using Absolute references through next equation (sale tax = price * tax) if (Tax = 7%).
1- Add new worksheet and rename as â€œExercise 3â€
2- Type next data in your worksheet3.
3- Calculate year Total and average in the previous table by using next equations:
Year total= Term1 + Term 2 + Term3 + Term 4.
Average % = (year total / 4) * 100.
In this exercise, practice Common Functions.
1- Add new worksheet and rename as â€œExercise 4â€.
2- Type next data in your worksheet 4.
3- Calculate summation and average of tests for each student by using sum and average functions.
4- Find the average, maximum and minimum mark in the previous table.
1- Add new worksheet and rename as â€œExercise 5â€
2- Type next data in your worksheet5.
3- Find prices of products after discount 10% and 15% by using Absolute references.
In this exercise, practice IF Function and Conditional Functions.
1- Add new worksheet and rename as â€œExercise 6â€
2- Type next data in your worksheet 6.
3- Add new column and name it â€œStatusâ€.
4- Add (IF) function in status column and set the condition as following:
If the mark >=60 then return â€œPassâ€ otherwise â€œFailâ€
5- Use AVERAGEIF function to find average of marks that greater than > 50.
6- Use COUNTIF Function to count all the students who have mark more than > 60.
7- Set conditional formatting for marks column to highlight the cells when the value less than 60 in the previous table.
8- Set Conditional Formatting for status column to highlight the cell when the value = â€œfailâ€.
In this exercise, practice Filter.
1- Add new worksheet and rename as â€œExercise 7â€
2- Type next data in your worksheet 7.
3- Use Filter command to display students information who in program (BAF and BBA) only.
4- Use Filter command to display students information who from Libya (country), show the result in worksheet 8
In this exercise, practice Pivot Table.
1- Add new worksheet and rename as â€œExercise 8â€
2- Start with Type next data that contains information about your salespeople.
3- Use Pivot Table to get report about:
a. What are the order amounts for each salesperson? Show the report in worksheet 9 and rename it as â€œReport 1â€.
b. What are the order amounts for salespeople in a specific country? Show the report in worksheet 10 and rename it as â€œReport 2â€.
To test studentsâ€™ capabilities of understanding on how to apply all the features, functions and methods learnt in this module to complete this Microsoft Excel project.
You are required to submit both hardcopy and softcopy together not later than the deadline stated above.
The following table is the marking scheme for this group project:
Exercise 1 5
Exercise 2 5
Exercise 3 5
Exercise 4 5
Exercise 5 5
Exercise 6 5
Exercise 7 5
Exercise 8 5
Expected Documentation Content:
1. LUCT CD Cover Page
2. Hard copy
Expected Softcopy (CD) Content:
1. All Microsoft Excel files especially *.xlsx and *.xltx files.
2. All files must be burned into a CD-R or CD-RW as read-only disc.