You will have 60 minutes for the exam and you are allowed to use your book and
notes.
Question 1
Formatting: Choose dark grey color for cell shading in row 2 (B2 through D2). Bold the
text in row 2, set its font sie to 2!, and set the font color to white. "erge and center cells
B2 through D2. Create a #order around the entire ta#le (rows 2 through $$, columns B
through D). Create a #ottom #order #elow row 2, row %, and row $2. Create a right
#order to the right of column B&:B$$. 'e(format the dates under )*eminar *tart Date+ so
that the day and month a,,ear. For exam,le, a date $-%-2!!. should look like this:
"onday, /anuary !%, 2!!.. 'e(format the course fees under )Course fee+ to dollars and
cents. For exam,le, a course fee of 0$.1&$%%$$2 should a,,ear as: 30$.1&. ($! ,oints)
Question 2
4he /aco# /a5it con5ention center has created a worksheet to track reser5ations for
rooms, and seeks your hel, in com,leting this worksheet. 6nother worksheet (called
)7ooku,4a#le+) contains additional information that is useful for com,leting this
worksheet. 8n the 9uestion 2 worksheet,
(a) Fill in the meeting room names (column C). :se the ;7<<=:> function. ($! ,oints)
(#) 8f a customer #ooked % days or more, they are offered Free Breakfast. Fill in column 8
to say )?es+ if a customer deser5es free #reakfast, and )@o+ otherwise. :se the 8F
function. ($! ,oints)
(c) 4he daily charge (column F) should #e the )Daily *,ecial rate+ for a s,ecial customer
(whose 'ate Code is *>), and should #e the )Daily *tandard rate+ for all other customers.
*,ecial customers are customers who ha5e enrolled in the Con5ention centerAs loyalty
,rogram. :se the ;7<<=:> and 8F functions. ($! ,oints)
(d) Calculate the 4otal Charges (Column B) which is the ,roduct of the daily charge and
#ooked days. ($! ,oints)
(e) Calculate totals, a5erage, minimum and maximum for 4otal Charges and Booked
Days (see rows 1$ C 1&). ($! ,oints)
Question 3
a. 7ist details of the customer who #ooked the conference center ('oom code: CF) for
more than 2 days (:se filter.) (. ,oints)
#. :se 6d5ance Filter to list the customers whose room code is 6:.( . ,oints)
Question 4
Create a ,ie chart showing the ,ercent of days each customer #ooked the auditorium.
(room code: 6:) ($! ,oints)
Question 5
?ou are gi5en the ,rices of $! ,roducts (>$, >2, D, >$!), and the 9uantities of these
,roducts sold on each day of the week. 8n the s,ace ,ro5ided under )'e5enues+, enter
formulas that will calculate the re5enues for each ,roduct in each day of the week.
'e5enue is ,roduct ,rice multi,lied #y 9uantity sold multi,lied #y the sales tax factor.
?our formulas should contain cell references only (not actual num#ers), and use a#solute
referencing (3 sign) and relati5e referencing as a,,ro,riate.
($! ,oints)
Question 6
Com,lete the ta#le to com,are the ,ayments and total interest on a $.( and %!( year loan
at 5arying interest rates.($! ,oints)
Question !
By a,,lying the >i5ot ta#le, com,are the re5enue generated #y *> code ,eo,le to <'
code ,eo,le in different rooms when they ha5e stayed less than a week.
