80.2K

Verified Solution

Link Copied!

Objective

The purpose of this exercise is to better understand the core business of the Trust. This will involve learning how to drill-down into the Trusts revenue data to gain a deeper understanding of the revenue earned and how this information could be used for decision-making.

Background

The Arawa Village Centre is administered by the Aotearoa Charitable Trust which was established as a joint venture by the Church, who owns the land and buildings, the local council and the local community. The Village Centres sole source of income is from the hire of its facilities. There are three distinct groups of customers who are charged at different rates for each of the facilities. The data file has been created by exporting an Inventory Item Summary from Xero and then cleaning the data.

Data Dictionary for Data Set

Item Code: This field contains the item code for this item in Xero

Room-type: The facilities which are available for hire include:

• Church,
• Hall,
• Lounge,
• Commercial
• Kitchen,
• Meeting Room
• Foyer.

Customer-type: The Customer groups include:

• Commercial:
• Community: organizations providing services to the community for personal benefit
• Not for profit: organizations providing services to the community.

Unit Rate: The facilities are available for hire on either a daily or hourly basis.

Hours: This field contains the number of hours that the facility has been hired during the past year. All daily hireage has a default use of 6 hours.

Revenue: This field contains the total revenue earned by the room-type

Requirements

1. Display the total revenue and usage for each Room-type in a table and a chart. Sort the table by revenue earned. Which Room-type earned the greatest revenue? Why does the foyer have the highest usage but only the eighth highest level of revenue?
2. Display the total revenue and usage for each Customer-type in a table and a chart. Sort the table by revenue earned. Which Customer-type earned the greatest revenue? Why do the not-for-profit organizations have the second highest level of usage and yet the lowest level of revenue?
3. Display the total revenue for each Room-type and Customer-type in a table and a chart.
4. Which hires (hourly, daily or half daily) were the most popular by room type with customers? Display the results in a table and a chart.
5. Which hires (hourly, daily or half daily) were the most popular by customer-type? Display the results in a table and a chart. Chart

Item Code	Room Type	Cust Type	Units	Unit Rate	Hours	Revenue
C1	Church	Commercial	Daily	$400.00	144.0	$5,600.00
C2	Church	Community	Hourly	$65.00	8.5	$482.50
C3	Church	NFP	Hourly	$42.00	24.0	$609.00
C4	Church	Parish	Hourly	$30.00	282.0	$8,460.00
C5	Church & Kitchen	NFP	Hourly	$50.00	2.0	$100.00
F1	Foyer	Community	Daily	$5.00	432.0	$270.00
F2	Foyer	Commercial	Daily	$10.00	152.0	$190.00
H1	Hall	Commercial	Daily	$250.00	48.0	$1,500.00
H2	Hall	Community	Daily	$175.00	40.0	$875.00
H3	Hall	Commercial	Hourly	$50.00	15.0	$742.00
H4	Hall	Community	Hourly	$30.00	382.5	$10,956.24
H5	Hall	NFP	Hourly	$24.00	9.5	$228.00
HK1	Hall & Kitchen	Community	Daily	$280.00	64.0	$280.00
HK2	Hall & Kitchen	NFP	Daily	$210.00	8.0	$210.00
HK3	Hall & Kitchen	Community	Hourly	$45.00	6.0	$270.00
HK4	Hall & Kitchen	NFP	Hourly	$35.00	4.0	$140.00
HL1	Hall & Lounge	Community	Daily	$220.00	144.0	$3,960.00
HL2	Hall & Lounge	Commercial	Hourly	$52.00	14.0	$728.00
HL3	Hall & Lounge	Community	Hourly	$32.00	40.8	$1,278.40
HL4	Hall & Lounge	NFP	Hourly	$28.00	5.0	$140.00
H6	Hall Exhibition	Commercial	Hourly	$30.00	16.0	$480.00
K1	Kitchen	Community	Hourly	$21.00	8.5	$178.50
K2	Kitchen	NFP	Hourly	$17.00	1.5	$25.50
K3	Kitchen	Commercial	Hourly	$9.00	10.0	$90.00
K4	Kitchen	Community	Hourly	$7.00	33.5	$233.45
K5	Kitchen	NFP	Hourly	$5.00	6.0	$30.00
L1	Lounge	Community	Daily	$75.00	8.0	$75.00
L2	Lounge	NFP	Daily	$50.00	8.0	$50.00
L3	Lounge	Commercial	Hourly	$25.00	32.3	$738.25
L4	Lounge	Community	Hourly	$14.00	123.5	$1,684.90
L5	Lounge	NFP	Hourly	$10.00	391.3	$4,134.48
M1	Meeting Room	Commercial	Hourly	$25.00	1.0	$25.00
M2	Meeting Room	Community	Hourly	$14.00	5.0	$70.00
M3	Meeting Room	NFP	Hourly	$10.00	3.0	$30.00
M4	Centre	Parish	Hourly	$60.00	4.0	$240.00
W1	Weddings	Community	Half Day	$175.00	16.0	$700.00

Please do not provide a code for this, make a table for the 5 requirements

Answer & Explanation Solved by verified expert

See Answer