Home Paste Clipboard 013 Insert Calibri 1 c Page Layout 11 Font D Formulas Data Review View Help ab ce Wrap Text Merge & Center Tell me what you want to do General .00 .00 Alignment Format as Cell Table v Styles Styles p Format o Number Hotel name AutoSum Clear v Cust. Level Sort & Filter v Editing w Discount % Find & Select v x Discount $ 20069 20070 20071 20072 20073 20074 20075 20076 89 20077 20078 20079 20080 20081 20082 95 20083 20084 20085 20086 20087 100 20088 101 20089 102 20090 103 20091 104 20092 105 20093 106 20094 107 20095 108 20096 109 20097 110 20098 111 20099 112 20100 113 20101 20102 115 20103 116 20104 117 20105 118 20106 119 20107 120 20108 121 20109 122 20110 123 20111 124 20112 Ready 2 4 6 7 8 9 10 11 12 81 82 83 84 85 86 87 88 90 91 92 93 94 96 97 98 99 114 The Global Hospitality Group (GHG) have provided you with a data file which records all hotel bookings throughout August to November. Pricing is calculated as total tariff - less discounts (for loyalty program members). The membership level determines both discounts and loyalty points. You have been asked to complete the revenue summary and provide advice to the executive general manager. You are required to: PART A Complete the Customer Revenue Summary (highlighted in yellow colour) - 5 marks PART B Provide information required by management concerning the data provided (separate tab) - 16 marks PART C Provide information relating to controls concerning other aspects of the business (separate tab) - 9 marks You must use relevant Excel functions for the tasks assigned. Do not calculate the required tasks manually. Use two decimal place where necessary. October Loyalty Ca Pro Poi Customer IC First nami Jesse Melinda Mary Cathy Lori Mario Charles Ricky Herbert Dale Ba rry' Helen David Mary Samuel Kathy Leona Mary Ricky Darlene Edwin Tracy Jason Leona Leslie Betty Arnold Carmen James Gladys James Brandon Angela Marilyn George Wanda Debra Anne Michael William Mark John Mary Glen Last nam Price Lane Frazier Atkins Robertson Rog ers Cooper Davis Atkins Atkins Wagner Kim Atkins Hughes Martin Silva Schwartz Pierce Stewa rt Atkins Tucker Hodges Fox Moore Wade Bell Garcia Roberts Gomez Barnes Hughes Watkins Davis Butler Ingram Cunningham Campbell Blake Fields Miller Howa rd Smith Hansen Atkins City Sydney Brisbane Adelaide Cairns Sydney Perth Syd ney Gladstone Melbourne Melbourne Can be rra Melbourne Sydney Melbourne Sydney Sydney Adelaide Sydney Toowoom ba Geelong Melbourne Melbourne Sydney Mackay Adelaide Melbourne Gold Coast Melbourne Perth Sydney Sydney Brisbane Brisbane Newcastle Brisbane Melbourne Newcastle Brisbane Sydney Melbourne Bundaberg Brisbane Brisbane Sydney State NSW QLD QLD NSW WA NSW NSW VIC ACT VIC NSW VIC NSW NSW NSW QLD VIC VIC VIC NSW QLD QLD VIC WA NSW NSW QLD QLD NSW QLD VIC NSW QLD NSW VIC QLD QLD QLD NSW PART B MembershiF None None Red None None Red Red Silver None None Silver None None None None Gold None Red None Red None Red None Silver None None Gold None None None None Platinum Gold Silver Gold Silver None None None PART C Booking Ref # 6355 9100 3288 3848 2457 7625 5929 7102 5114 9583 7518 4595 5821 8994 7178 1335 7838 3677 5877 4428 2592 5211 3858 7752 1199 1829 7651 4562 7767 7712 6550 5772 9499 7615 3325 8715 8942 6003 5651 8559 6278 4181 2664 5736 6719 1500 4153 6632 1416 3127 9178 9146 5228 5157 6657 4946 4216 7065 3163 4762 4947 1836 9400 9997 5476 2142 5535 1196 2219 6626 7648 5397 4368 8409 4186 5084 6148 1140 1831 6322 9669 4432 9353 8773 4789 4458 8209 8699 7040 8011 4030 5795 9087 9985 7759 2607 9851 1010 4968 1974 1015 3798 5444 1823 8118 3108 3537 3179 4691 5205 5159 1657 6086 2615 6732 1282 5888 4997 1967 7147 1760 7417 3120 3674 4633 8440 2706 4866 7230 5062 1167 3201 Cust# 44468 32510 28220 21560 22925 31965 31317 41458 21304 38021 31589 35824 32396 25171 37135 32468 28287 22089 37061 41504 33744 36513 23007 24218 23530 42241 41464 26326 34237 33379 33973 43823 42297 25067 40056 27825 23252 28846 44158 30521 42475 44199 41291 36624 Conditional Formatting o Rating #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! From 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 2 sep 19 September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September September Nights 4 4 6 2 4 6 6 4 2 7 2 7 7 6 2 2 7 6 6 2 3 7 s 2 6 4 s 4 2 4 4 1 4 4 3 2 6 1 7 Insert s Room Type Standard Standard Suite/Family Standard Standard Standard Standard Premium Suite/Family Premium Standard Premium Premium Suite/Family Suite/Family Premium Suite/Family Standard Suite/Family Suite/Family Standard Suite/Family Premium Standard Premium Suite/Family Standard Standard Standard Standard Suite/Family Standard Standard Standard Premium Standard Standard Standard Standard Standard Suite/Family Standard Premium Standard Cells Full Rate p/n $110 $126 $194 $118 $112 $62 $120 $146 $194 $154 $118 $156 $151 $289 $179 $151 $196 $181 $185 $187 $122 $136 $152 $112 $144 $198 $194 $113 $126 $123 $193 S 74 $118 $117 $149 $121 $122 $120 $62 $115 $178 $121 $152 $177 $ (before discount) Loya Revenue $ Eleven Points by Gardenia Leisure Studios Eleven Points by Gardenia Tripletree Inn Tripletree Inn Thrifty Stay Eleven Points by Gardenia Tripletree Inn Eleven Points by Gardenia Tripletree Inn Leisure Studios Eleven Points by Gardenia Tripletree Inn G Elite Eleven Points by Gardenia Eleven Points by Gardenia Eleven Points by Gardenia Gardenia Deluxe Eleven Points by Gardenia Eleven Points by Gardenia Eleven Points by Gardenia Thrifty Stay Eleven Points by Gardenia Eleven Points by Gardenia Eleven Points by Gardenia Tripletree Inn G Elite Eleven Points by Gardenia Leisure Studios Tripletree Inn Tripletree Inn Thrifty Stay Tripletree Inn Tripletree Inn Eleven Points by Gardenia Tripletree Inn Leisure Studios Tripletree Inn Thrifty Stay Eleven Points by Gardenia Tripletree Inn Tripletree Inn Eleven Points by Gardenia G Elite Questions PART A (k Accessibility: Investigate