MIS118 - Introduction to Computer and Information Systems Excel Assignment
Aisha_
ExcelAssignment.docxMIS118 Excel Assignment Spring 2020
MIS118 - Introduction to Computer and Information Systems
Excel Assignment
Use Microsoft Excel to create the spreadsheet given in the below snapshot, and follow the assignment description to process the data in the spreadsheet according to the instructions given below. The spreadsheet represent the menu items sold by a fast-food restaurant.
1. In a new spreadsheet file, insert the data as demonstrated in the figure 1.
Figure 1 Initial Spreadsheet
2. Use a formula in Column E to calculate the amount payable
3. Format the prices and the payable amounts to KWD currency
4. Format the discount amounts to percentage.
5. In cell A10, use a formula to show the total number of menu items listed in column A.
6. In cell C10, use a formula to show the total price of all items of the menu.
7. In cells B13, use a formula to calculate how many different meals the restaurant serves.
8. Similarly, in B14, B15, and B16 use a formula to indicate the number of sides, drinks, dessert items the restaurant serves.
9. In cells C13 to C16, write a formula to calculate the percentage of the particular item type compared to all items on the list. Note. Divide the number of items with the particular item type (meal, side, drink, dessert respectively) by the total number of items served by the restaurant (the value in A10).
10. Draw a pie chart highlighting the percentage calculated in the previous step.
11. Using conditional formatting, highlight in blue the highest discount
12. In cell A18, write a formula to count how many items have a discount of more than 20%
13. After performing all of the previous steps, your document should look as demonstrated in figure 2.
Figure 2 Final spreadsheet
|
Graded Item |
2 points |
1 point |
No points |
|
Data Entry |
Data entered according to figure 1 |
Data entered with some mistakes |
|
|
Column E (Payable Amount) |
Correct formula |
Incorrect formula |
No formula, column left empty or value entered without formula. |
|
KWD currency formatting |
Done correctly for both Prices and Amount payable. |
Done incorrectly, or done for only one of the columns |
No currency formatting |
|
A10 (number of items) |
Correct formula |
Incorrect formula |
No formula, column left empty or value entered without formula. |
|
C10 (total price) |
Correct formula |
Incorrect formula |
No formula, column left empty or value entered without formula. |
|
B13 to B16 (count of each type) |
Correct formula for all cells |
Some or all incorrect formulas |
No formulas, cells left empty or values entered without formula. |
|
C13 to C16 (percentage of each type) |
Correct formula for all cells |
Some or all incorrect formulas |
No formulas, cells left empty or values entered without formula. |
|
Pie chart |
The chart has a title and it shows the correct percentages and the type represented by each color. |
The chart doesn’t have a title, or it doesn’t show for each color the corresponding item type. |
No chart |
|
D8 (conditional formatting) |
Conditional formatting done correctly so it always highlights the highest discount whenever it changes. |
Incorrect conditional formatting. |
No conditional formatting |
|
A18 |
Correct formula |
Incorrect formula |
No formula, cell left empty or value entered without formula. |
|
TOTAL |
20 points |
10 points |
0 pts. |
