SBA PAPER TWO

SAINT JOHN’S COLLEGE

INFORMATION TECHNOLOGY

SCHOOL BASED ASSESSMENT (SBA)

2014-2015

Written by Ms. Marcelli Young

Paper Two – Spreadsheet

1.      Using data from your database include the GuestID, First and Last Name of each VIP member, Membership Type, and whether or not they are attending on Sheet 1.  Rename the sheet as Guest Info.

a.       Insert a column heading labeled Membership Fee.  Using a lookup function, determine the membership fee for each guest.  The table below is to be inserted in an appropriate area of your spreadsheet.

Membership Type	Membership Fee
Gold	$                 1,600.00
Diamond	$                 2,000.00
Platinum	$                 2,700.00

Format all currency fields where needed.  Place all boarders around active cells; shade cells hosting column headings a color of your choice; bold and center column headings.

b.      Using a built-in function, calculate the total expected income from membership fees based on the members that will be attending the event.  Label appropriately.

c.       Copy Sheet 1 to Sheet 2.  Rename Sheet 2 as Modified.  From this point forward, the Modified sheet is to be used.

2.      Using the Modified Sheet, delete the last two guest information, deleting rows such that formulae remain unaffected.  (These two guests are no longer members)  

a.       Sort the table based on membership type (ascending); then by last name (ascending).

b.      Each VIP member is allowed to bring a party of up to four persons.  Insert a column labeled as Amount in Party.  State the number of guests each VIP has stated when they RSVP, bearing in mind that this number includes the VIP member.  Using a function, show the total number of people expected to attend the event.  Label appropriately.

c.       Extract lists of all members that are attending the event; separate by membership type.  Place at an appropriate area of the modified sheet.  Highlight criteria for each list.

d.      Using a built-in function, count and state the total number of members that will be attending the event, separated by membership type.  Label appropriately.

e.       Prepare a pie chart to compare and indicate the number of VIP members in each Membership Type.  Label the chart appropriately; include a legend.  Move the chart to a new sheet labeled as Chart 1

3.      Your company is catering out the food and drinks.  The quoted cost per person is $125.00 including dinner and drinks.  This also includes the cost of servers for the night.  The rental of the venue is $3,000.00 for the night.  The venue is separated into two rooms; one is designated for VIP members only.  Your company is splitting the cost into the two groups.  Music is estimated at $1,000.00 for the DJ and band for the VIP room only.  Tables and chairs rental is $350.00 for the VIP room.  Each VIP member’s table will be served a bottle of Champagne; each bottle is at the cost of $125.00.  Decorating the VIP room is expected to be $650.00.  Additional entertainment is at the cost of $1,500.00 and miscellaneous expenses are estimated at $2,500.00 to cover the event.

a.       On Sheet 3, prepare a Budget for the event, indicating a profit or loss based on the estimated income from membership fees of those members attending.  The budget should indicate the estimated income and expense, following the template below.  Rename the sheet as Budget.  Ensure to link the modified and budget sheets by using a value in a formula or function.  Use absolute reference to calculate the total estimated cost for Food and Drinks. Insert an appropriate label above the table.

Income	Estimated	Actual
Expected Income from Membership Fees
Total:
Expenses	Estimated	Actual
Venue
Food and Drinks
Music
Tables and chairs rental
Champagne
Decoration
Entertainment
Miscellaneous expenses
Total:
Profit or Loss	Estimated	Actual
Total Income
Total Expenses
PROFIT or LOSS

b.      Adjust the budget table as follows:

                                                              i.      The actual income from membership fees was 12% below estimated.

                                                            ii.      The food and drinks expense was 12% below estimated due to decrease in membership participation.

                                                          iii.      Entertainment expense increased by 10%.

                                                          iv.      Miscellaneous expenses increased by 13%.

c.       Prepare a line graph to compare the estimated and actual budget.  Label the chart and chart axis appropriately; no legend needed; move chart to a new sheet and label as Chart 2.

Arrange Sheets based on order of instructions; charts should follow all three sheets.

When printing, ensure to print formula sheets.  Also, include custom header with your CSEC registration number (right aligned), and custom footer with the sheet name (centered).