MPA102 Master Of Professional Accounting: Information Systems For Accountants Assessment Answer

Assessment Cover Sheet

Faculty of Business, Design & I.T.

CASE BACKGROUND

The Black Mesa Convention Centre (BMCC) is a multi-purpose venue with the flexibility to cater for indoor/outdoor functions conferences, meetings, conventions. BMCC is a subsidiary of the world- famous Black Mesa Research Facility, as such most functions held are for scientific research or occasionally as a training facility for the military college. To ensure a reservation, clients will often book rooms a year or more in advance.

You are Lamarr Crab, a new assistant to Black Mesa. Your boss Dr Kleiner has asked you to help Dr Gordan Freeman, the BMCC Events Coordinator. You will help him schedule meeting rooms, and produce management accounting reports based on the date from the BMCC Reservations MS Access database which is used to track reservations.

BMCC currently has five standard meeting rooms, a conference centre, a boardroom, and an auditorium. A client may book one or all of the rooms. Although the conference centre is primarily used for larger meetings, the centre can be converted into four smaller meeting rooms, thus providing the convention centre with extra meeting rooms. When booking a meeting room, the client may request a particular seating arrangement, such as circular, classroom, lecture, or U- shape. The number of available seats within a given meeting room is dependent upon the seating arrangement.

Convention centre clients are quoted either a standard, advanced, or special rate. The advanced rate applies to clients who book six months or more in advance. Dr Freeman uses the special rate as a negotiating tool to attract special clients, this is especially handy to lure conference organisers away from Black Mesa’s chief competitor Aperture. The standard rate applies all other bookings.

Additional desks, lecterns, audio visual equipment, or other facilities are also available to be booked. These are kept in a “Schedule of Extras’, and this table will include any miscellaneous charges such as cancellation fees, catering, additional security, laboratory equipment for demonstrations, and other costs. Extras are charged per unit per day. So a 5 day event might book 10 sets of Tea and Coffee since a coffee break would be offered in the morning and the afternoon on each day.

Design SpecificationsThe Reservations are made in the Access database, but analysis is often easier and faster in Excel. You’re not sure how to import data from a Microsoft Access database and so you Google it. You discover that importing data into a worksheet is easily accomplished by using either Microsoft Query or Get External Data From Access.

Dr Freeman wants you to retrieve all fields from the Reservation Details table. Once you import the reservation data into the worksheet, you quickly realise you can’t answer Dr Freeman’s questions using only the raw variables. Dr Freeman gestures that you will need to impute several steps to derive your final answer. Dr Freeman signals that you many insert and columns necessary into your worksheet.

Table Structure

The database has the following table structure.

Every BOOKING will contain one or more line items, these are found in the BOOKING MATCHING TABLE. A single line them can include which rooms were books and the number of days they were booked for, or any of the extra services (such as additional whiteboards, tea and coffee, etc) which can be found in the EXTRAs table. Total charge for each booking will be the number of days booked for each room, multiplied by the correct rate, with any additional changes from extra orders.

Requirements

Your first task is to import the data into Excel.

Next you will need to impute several variables. For example, the number of days each room has been booked for will depend on the start date and the end date.

Finally you will need to prepare a spreads sheet for Dr Freeman that answer the following questions. Please put each in a new Tab: Q1, Q2, Q2. Etc. You are required to use pivot tables and charts where necessary, and include an appropriate chart for each of the questions.

Q1: What is the frequency of various rates; standard advanced or special rates. 

Q2: What are the Frequency of Charge out rates by room

Q3: What are the frequency of seating arrangements for each room.

Q4: What is the most lucrative room, and what are the total charges by room.

Q5. Dr Freeman wants to review the booking habits of the convention centre’s clients. For instance, he wants to know how far in advance, on average, the centre’s clients book the meeting rooms

Q6. How much did each client spend? Sort client from most income to least. Which is their best client?

Q7. For each client, what is the average spend for each booking.

Q8. What was the average length of time that a client would book a room?

Q9. What are the total charges for rooms that belong to February 2020, what are the total charges for each client? (do not include extras)

Q10. Overall, what is the average daily room charge?

Q11. Which seating arrangement is most popular across all rooms?

Q12 Based on the total charges for each customer, identify the five customers with the highest total charges.

Q13. What are the total charges for room and the total for extras? What is the ratio between room charges and extra income? Please use a chart

Implementation Concerns

Although you are free to work with the design of your worksheet, it should have a consistent, professional appearance. You should also apply appropriate formatting to the cells and worksheet. For instance, all cells containing dollar values should use a currency format and be formatted to two decimal places. Also, your worksheet should have an appropriate header, as well as appropriate column and row headings.

To prepare your worksheet according to the specifications provided above, you will import external data, insert columns, construct formulas that may include the IF and INDEX(MATCH()) functions, work with dates, and use several tools, such as PivotTable, Filter, Chart, and Microsoft Query. You may find the error checking functions ISBLANK, ISERROR, ISNUMBER to be helpful.

You can use the Get External Data from Access command or Microsoft Query to import reservation data. The Microsoft Query Wizard guides you through the process of retrieving external data from the Reservation Details table in the BMCC Reservations database.