SEE ATTACHMENT

ELECTRONICDOCUMENT PREPARATION AND MANAGEMENT

MICROSOFT EXCEL TEST

1. Open the following spreadsheet and save it as Purchases (Your Name).
(2 marks)

Serenity Café

Income for March 2018

Breakfast

Lunch

Dessert

Appetizer

Dinner

Total

%age

Tim and Family

60

75

36

30

70

John and Jane

40

54

25

22

45

Tommy

25

23

5

15

20

Mary and Children

55

57

20

33

60

TOTAL

AVERAGE

HIGHEST

LOWEST

2. Rename Sheet1 to Spring Break. Make a copy of Spring Break and rename it

March.
(Delete the

unnecessary worksheets) (3 marks)

3. Using a function, insert today’s date in the appropriate cell
(DATE FORMAT: Month-Day-Year)

(2 marks)

4. Use functions to calculate the following:

· Total income on breakfast, lunch, dessert, appetizer and dinner;

· Average income on breakfast, lunch, dessert, appetizer and dinner;

· Highest income on breakfast, lunch, dessert, appetizer and dinner;

· Lowest income on breakfast, lunch, dessert, appetizer and dinner;

· Total cost for serving Tim and Family, John and Jane, Tommy and Mary and Children;

· The total overall income from serving, and

· The percentage of Purchases from Each Individual in relation to the Overall Total Purchases. (Answer must be to 2 decimal places)

(14 marks)

5. Complete the following formatting operations:

· Center the headings across the columns.

· Use appropriate cell styles for the main, column and row headings

· Insert a suitable graphic. The graphic should be appropriately sized and placed on both sides of the heading
(9 marks)

Create the following charts

6. A PIE CHART to compare the Total Income for serving for Each Individual. Give the chart an

appropriate title. Label the chart with percent and place the legend on the left of the chart. Place

on a new sheet called INDIVIDUAL PURCHASES. (5 marks)

7. A COLUMN CHART to show the Total Income for each item ( e.g. breakfast, lunch, dessert,

appetizer and dinner. Give the chart an appropriate title and label the axes. Place the legend on

the bottom of the chart.

(5 marks)

______________________________________________________________________________

8. Save the changes to the worksheet.
(2 marks)

Sheet1

18

25

5

20

20

60

Sheet2

Sheet3