+1-415-670-9189
info@expertsmind.com
Explaining your insights into the operation of trade fair
Course:- Management Information Sys
Reference No.:- EM13841245




Assignment Help
Expertsmind Rated 4.9 / 5 based on 47215 reviews.
Review Site
Assignment Help >> Management Information Sys

Aims

To analyse a set of data (in Microsoft Excel), and write a brief report (in Microsoft Word), identifying and explaining your insights into the operation of Trade Fair organized by "Home and Office Product Exhibition (HOPE)".

Objectives

In the process of this assessment task you will:

plan, schedule and execute project tasks with a view to improving your personal productivity;

gain awareness of some typical issues related to the operation of a small-to-medium size business;

use the functionality of Microsoft Excel 2007/10/13 to manipulate data, analyse it and visualise it in tabular and chart form; and

use the functionality of Microsoft Word to write a brief report of your business observations and recommendations.

Home and Office Product Exhibition : Analysing Sales and Customer Relations

Introduction

Home and Office Product Exhibition (HOPE)specialises in promoting and selling both (i) stand space at trade fairs and (ii) tickets for visitors to trade fairs in Latrobe Valley, Gippsland area. The company currently promotes and sells two different sized stand spaces, to new or returning exhibitors,for a number of Trade Fairs locally and overseas. HOPE alsoprovides a number of additional value-added services to exhibitors for fees, such as organising exhibitor stands, developing marketing material for exhibitors to distribute at fairs, arranging travel and/or accommodation for exhibitors, and hospitality/catering for exhibitors to offer their customers attending the Exhibition. HOPE also organise visitor tickets for the trade fairs, which also provide commission income. All these activities are managed through the efforts of a number of sales representatives. The company's founder, Ms IsabellaHunt, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.

Ms Hunt has recently hired you as her business analyst and poses some operational-level questions about the performance of her business. She is also eager to hear your thoughts and ideas on how to improve the business and requiresyou to make several recommendations on how to improve the company's performance, especially in relation to the following business objectives:

improving the sales strategy;

improving internal efficiencies and effectiveness; and

building strong lasting relationships with its customers.

Ms Hunt has provided you with latest sales information in the"data.xlsx" file. The data needs to be analysed and visualised to help observations related to the business operation and its shortcomings.

The data worksheet "Sales" contains collected information of the consultancy's operationsinthe current year. Thesedetails included:

Date and Day(of customer order),
Trade Fair
Customer (i.e. exhibiting company)
space sold (sq.m.)
Sales Representative
time spent by Sales Representative to complete sale
returning Exhibitor?
Exhibitor stand organised?
Exhibitor travel organised?
Exhibitor accommodation organised?
Exhibitor marketing material organised?
Exhibitor hospitality organised?
Visitor tickets sold

The fees charged by HOPE to customers (i.e. exhibitors) for their value-added services are:

Value Added Service

Fee (AUD)

Exhibitor stand organised

1800

Exhibitor travel arrangements

200

Exhibitor accommodation arrangements

175

Exhibitor marketing material organised

1700

Exhibitor hospitality organised

170

Each sales representative receives 12% of the total value added services for corresponding transactions. The data worksheet "Exhibitions" contains collected information of the Trade Fairs over the past decade. These details included:

  • Exhibition
  • Year
  • space available for HOPE to sell to exhibitors (sq.m.)
  • Visitor tickets sold by HOPE
  • Total Profit for HOPE

HOPE receives a fee ($6) for each Exhibition visitor ticket sold through its Sales Representatives. HOPE pays 20%of the ticket prices to the corresponding sales representative. This historical information is summarised in the "Exhibitions" worksheet as a summation of all Sales Representative ticket sales.

The company receives different rates of 'commissions'(i.e. fees) for selling space to companies exhibiting at the various fairs. The exhibition managers charge $150 per square meter for floor space. HOPE receives commission according to the following table.

Commission Received

Space Sold

Rate (% of sale amount)

New Exhibitor (large)

>= 20 sq.m.

15

New Exhibitor (small)

< 20 sq.m.

12

Returning Exhibitor (large)

>= 20 sq.m.

8

Returning Exhibitor (small)

< 20 sq.m.

5

HOPE thenpays commissions to the Sales Representatives for selling the space to those exhibiting companies. Each representative also receives $20 for each hour of their service on the weekdays and $30 per hour in the weekends. The following tables describe how these commissions are calculated:

Commission Paid

Space Sold

Rate (% of commission received)

New Exhibitor (large)

>= 20 sq.m.

35

New Exhibitor (small)

< 20 sq.m.

30

Returning Exhibitor (large)

>= 20 sq.m.

20

Returning Exhibitor (small)

< 20 sq.m.

15

On an average HOPE spends 55% of each sale as management salary, office rent, utilities, transportation and maintenance.

Calculations:

a) Total sales, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) income type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

b) Total profit, sub-totalled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

c) Total expenses, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) expense type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

d) Total hours worked, sub-totalled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

e) Changes to profitability over the past decade, including 2015 figures, sub-totalled by each Trade Fair. Use an appropriate graph/chart to summarise these changes.

Ms Hunt requires you to make observations to help her with the following questions.

Observations:

f) Who are HOPE's five best and five worst customers by total sales? Use an appropriate table and graph/chart to summarise these observations.

g) What are HOPE's best and worst value-added services by total profit? Use an appropriate table and graph/chart to summarise these observations.

h) What are HOPE's best and worst Trade Fairs by total profit? Use an appropriate table and graph/chart to summarise these observations.

i) What would happen to profits if the rate of pay to Sales Representatives for hours worked on sales was increased by 50%? Use an appropriate table and graph/chart to summarise these calculations.

j) What are the best Sales Representative's best- and worst-selling services (by total profit)? Use an appropriate table and graph/chart to summarise these calculations.

k) What would happen to profits if the amount of space required to be sold to change the scale of commission paid was changed from 20sq.m. to (a) 25 sq.m. and (b) 30sq.m. Use an appropriate table and graph/chart to summarise these calculations.

Ms Hunt also requires you to make recommendations concerning the following business-level questions.

Recommendations:

l) Should HOPE focus on any particular (a) customer(s), (b) Trade Fairs, and (c) particular sales activities in future, and why?

m) What are your recommendations for resolving any data redundancy issues observed in the workbook?

n) What are your recommendation regarding what other data should be collected to improve decision making for HOPE?

o) What are your recommendation regarding record-keeping to improve the quality of data collection and management?

p) What changes should HOPE make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why?

Ms Hunt has asked you to provide her with the details of how you have accomplished these tasks. Therefore, you must include the functions and the formulae you've used in your analyses, and not simply report the answers. Create a new worksheet in the assignment workbook called "Calculations" to provide Ms Hunt with these calculations and summations.

As a paid consultant, your submission to Ms Huntmust be professionally presented;all analyses, calculations and summaries in the Excel file musthave headings and be supported with explanatory notes; and the formal business report must be preceded by a 'covering letter' (i.e. incorporate letter as first page of your Word document with your report on following pages).

Note: All recommendations in your report must be clearly justified (e.g. include charting or refer to specific analyses/summaries from the Excel spreadsheet).

Attachment:- data.xlsx

Answered:-

Verified Expert


Preview Container content

Executive Summary :

This study mainly focuses on the Home and Office Product Exhibition (HOPE) strategy for profit maximization. HOPE, a product exhibition company, who are currently providing both stand space at trade fairs and selling tickets for visitors to trade fairs in Latrobe Valley, Gippsland area. Here, they want to analyze the optimal strategy to increase their profits. They also want to know the primary sources that maximizes their profits.




Put your comment
 
Minimize


Ask Question & Get Answers from Experts
Browse some more (Management Information Sys) Materials
Identify three excuses that might be used for not doing an analysis and counter each with a brief description of how technology to help you create it and maintain it with a
Describe the business process reengineering that the organisation adopted for their ERP implementation. Identify and discuss the key problems that occurred during the ERP imp
GTNT has asked your team to provide recommendations for potential technologies they may use to improve their business process. Read the scenario to determine a viable informat
Using the Internet, newspapers, journals, and magazines, identify 5 emergency management incidents where social media was an instrumental tool in response or recovery operat
Do you believe there are universal codes of ethics that are independent of culture or religion? Elaborate. Do you work for an organization that has clearly stated ethical stan
Describe your own personal "Disaster Recovery Plan". How would you deal with loss of personal data contained on your phone, tablet, laptop, etc. Use your favorite internet s
Information Security and Ethics: Protection Policies and Procedures - Explain What are security policies or e-policies? Explain three security policies that you would primari
Prepare an 8- to 10-slide Microsoft PowerPoint presentation that shows the pros and cons your business would experience from using such a service compared to hosting the app