Operation of ballarat trade fair consultancy

Assignment Help Management Information Sys
Reference no: EM13304891

Sales Tracking and Customer Relations Analyses

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 "Ballarat Trade Fair Consultancy".

Learning 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/2010 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.

Introduction

Ballarat Trade Fair Consultancy (BTFC) specialises in promoting and selling both (i) stand space at trade fairs and (ii) tickets for visitors to trade fairs in Ballarat area. The company currently organises space for exhibitirs (i.e. customers) to display their products or services, for a number of Trade Fairs held locally and overseas. BTFC distinguish between new or returning exhibitors for calculation of their commission fee income rates. BTFC also organises visitor tickets for the trade fairs, which also provide commission income. BTFC also receive income by providing a number of additional value-added services to exhibitors, such as by 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 trade fair. These activities are managed by three BTFC Sales Representatives (Suki, James, Padmila). The company's founder, Ms Eliza Holt, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.
Ms Holt has hired you as a 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 requires you 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 Holt has provided you with last year's sales information in the "itech1005-5005 2014-17 assignment 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 operations in the current year. These details included:
• Date (of customer order)
• Trade Fair
• Customer (i.e. exhibiting company)
• space sold (sq.m.)
• Sales Representative
• time spent by Sales Representative to complete sale
• Exhibitor stand fee income ($A)
• Exhibitor stand expenses ($A)
• Exhibitor travel organised?
• Exhibitor accommodation organised?
• Exhibitor marketing material organised?
• Exhibitor hospitality organised?
• Visitor tickets sold
BTFC organise stands for the exhibitors to display their products or services at the trade fairs - these stands cost BTFC money to organise and this expense if offset by BTFC charging the exhibitor a (variable) fee to organise the stand.
The additional fees charged by BTFC to customers (i.e. exhibitors) for their value-added services (i.e. as fee income) are:
Value Added Service (VAS): VAS Fee Income Received ($A)
Exhibitor travel arrangements 100
Exhibitor accommodation arrangements 100
Exhibitor marketing material organised (large space: >=22 sq.m.) 1200
Exhibitor marketing material organised (small space: < 22sq.m.) 650
Exhibitor hospitality organised 100

The data worksheet "Fairs" on the Excel workbook contains collected information of the Trade Fairs over the past decade. Note: The list of Trade Fairs on this spreadsheet is complete and their names are all correct. These details collected for the previous decade's work include:
• Trade Fair name
• Year
• space available for BTFC to sell to exhibitors (sq.m.)
• space sold by BTFC to exhibitors (sq.m.) - only for 2014 year data
• Visitor tickets sold by BTFC
• Total Profit for BTFC
BTFC also receives income via a fee ($6) for each Trade Fair visitor ticket sold through its Sales Representatives. This historical information is summarised in the "Fairs" worksheet as a summation of all Sales Representative ticket sales.
BTFC receives ‘commission fees' (i.e. as income) from the various Trade Fairs for selling space to companies exhibiting their products or services at those various fairs. The Trade Fairs managers charge $250 sq.m. for floor space at all Trade Fairs, which BTFC charges their customers (i.e. exhibiting companies) and then receives a variable percentage of these floor space fees as income. BTFC also pays commissions to the Sales Representatives for selling the space to those exhibiting companies (i.e. fees as expense). The following tables describe how these commissions are calculated:

Floor Space Commission Fees Received from Exhibitors Space Sold Fee Rate Received (% of sale amount)
New Exhibitor (large) >= 22 sq.m. 15
New Exhibitor (small) < 22 sq.m. 10
Returning Exhibitor any space 6

Floor Space Commission Fees Paid to Sales Representatives Space Sold Fee Rate Paid (% of commission received)
New Exhibitor (large) >= 22 sq.m. 25
New Exhibitor (small) < 22 sq.m. 15
Returning Exhibitor any space 12.5

Important: the fees received as income are based on the amount of floor space sold to customers/exhibitors. The fees paid to Sales Representatives as expenses are based on the amount of commission income received from floor space sold.
BTFC also currently pays each Sales Representative a wage (i.e. an expense) for their time spent working with a customer on a sale ($15 per hour).
Assessable Tasks
Ms Holt needs to have a summary report of operations that will include the following information:
Calculations (All Students):
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 2014 figures, sub-totalled by each Trade Fair. Use an appropriate graph/chart to summarise these changes.
Ms Holt requires you to make observations to help her with the following questions.
Observations (All students):
f. Who are BTFC's five best and five worst customers by total sales? Use an appropriate table and graph/chart to summarise these observations.
g. What are BTFC's best and worst value-added services by total profit? Use an appropriate table and graph/chart to summarise these observations.
h. What are BTFC'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 40%? Use an appropriate table and graph/chart to summarise these calculations.
Observations (ITECH5005 Students Only):
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 22 sq.m. to (a) 25 sq.m. and (b) 20 sq.m. Use an appropriate table and graph/chart to summarise these calculations.
Ms Holt also requires you to make recommendations concerning the following business-level questions.
Recommendations (All Students):
l. Should BTFC 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 BTFC?
o. What are your recommendation regarding record-keeping to improve the quality of data collection and management?
Recommendations (ITECH5005 Students Only):
p. What changes should BTFC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why?
Ms Holt 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 calculations and analyses, and not simply report the answers. Create a new worksheet in the assignment workbook for each question, to provide Ms Holt with these calculations and summations.
Note: As a paid consultant, your submission to Ms Holt must be professionally presented; your analyses, calculations and summaries in the Excel file must have headings and be supported with explanatory notes; and the formal business report must be professionally written and presented. All recommendations in your report must be clearly justified (e.g. include charts/graphs or refer to specific analyses/summaries from the Excel spreadsheet).
Some Hints
The business options are linked to a number of questions posed by Ms Holt. Your role is to answer these specific questions and to support your answers with your data analysis as presented in the appropriate tables and charts. Ensure that your recommendations address the company's objectives, and that they are clearly linked to your observations.
Within this company, profit is a simple calculation of total sales less total expenses. There are seven types of income and three types of expenditure identified by Ms Holt and included in the detail above - all must be included in the relevant calculations (e.g. of income, expenses and profits).
There is no ‘ideal' or ‘best' way of doing this project. You'll be assessed on your insight of the data, on your ability to make observations by analysing (with formulae, pivot tables) and visualising (with charts/graphs) your data, and your ability to link your observations to business issues. You must use Pivot Tables and graphs/charts in your work, otherwise penalties apply.
There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning your workbook, and especially the observations, you may wish to review the tutorials on the related topics (e.g. Excel functions, absolute and relative cell referencing, formatting, chartings and managing large worksheets, as well as on the analysis of Excel data and drawing recommendations). You may find the following formulae useful in this assignment: MONTH, COUNTIFS, IF, SUM, SUMIFS. You will also need to know how to nest formulae within formulae (i.e. nest an IF statement inside another IF statement to provide multiple answers when multiple criteria must be applied).
The Excel workbook contains all the necessary data for your assignment and includes the heading columns for you to make the minimal set of calculations to answer the questions. You may need or want to include other calculations - the provided column labels are therefore only the minimum set required for your analyses.
You need to make sure the records included in the worksheets are not faulty or inaccurate (e.g. missing data) to avoid errors in calculations. You will therefore need to ‘cleanse' the dataset before you commence your calculations and analyses.
Make sure you review the marking guide, to ensure you complete all required tasks before submission. Make sure you submit both required files and that both are correctly named, otherwise penalties will apply. Make sure you create enough new worksheets on the provided Excel workbook to answer each question. Make sure you add appropriate headings and explanatory notes of your calculations and analyses on each worksheet to ensure that Ms Holt can interpret your work, otherwise penalties will apply.


Attachment:- Ba.zip

Reference no: EM13304891

Questions Cloud

What is the monthly mortgage payment : Current mortgage rates are 8 percent. Interest is compounded monthly and all payments are due at the end of the month. What is the monthly mortgage payment?
Explain how to estimate the flow rate in the cross section : The river channel itself is 20m wide and flow depth is 5m and the sandy bottom has an n=0.02, the left bank is 40m wide and the flow depth is 0.5m, while the right bank is 100m wide with a flow depth of 1m.
Identify five qualitative financial and economic assessments : Identify five qualitative financial and economic assessments specific to Gant and its industry that you shouldconsider in further analyzing Gant's liquidity.
Develop preliminary wbs for small one-story to be construct : The exterior and interior walls are of concrete block. The roof is constructed of bar joists covered with a steel roof deck, rigid insulation, and built-up roofing.
Operation of ballarat trade fair consultancy : Analyse a set of data, and write a brief report (in Microsoft Word), identifying and explaining your insights into the operation of "Ballarat Trade Fair Consultancy".
Find channel cross section and the required channel slope : A rectangular channel is to carry 200 cfs. The mean velocity must be greater than 2.5 fps. The channel bottom width should be about twice the channel depth. Find the channel cross section and the required channel slope.
What will the value of each bond be : An investor has two bonds in his portfolio that have a face value of $ 1,000 and pay a 10% annual coupon. Bond L matures in 15 years, while Bond S matures in 1 year.
Determine how many different poker hands are there : A standard deck of cards contains 52 different cards. A poker hand consists of five cards, chose randomly. How many different poker hands are there
Compute minimum value of specific energy and critical depth : A rectangular channel carries a flow of 10 cfs/ft of width. Plot a curve of specific energy vs depth. Compute the minimum value of specific energy and the critical depth. What are the alternative depths for Es = 5.0

Reviews

Write a Review

Management Information Sys Questions & Answers

  Importance of a supply chain strategy

Describe what a supply chain is and the importance of a supply chain strategy. Identify at least four key drivers of a supply chain strategy and the important ingredient of each to a supply chain strategy.

  Question about final project

Question about Final project - Abbreviated sample above you will label each section and you will write a very brief summary of how you plan to answer each question.

  What is the debit and credit

The company paid $2,800 cash for the premium on a 12-month insurance policy. Coverage begins on April 11. a. Two-thirds of one month's insurance coverage has expired. What is the debit and credit?

  Sdlc approach versus prototyping

SDLC Approach versus prototyping - Describe the steps of a pure prototyping methodology as an alternative to an SDLC approach.

  Explain important information about new economy

Important information about new economy - Prepare a summary about your findings. What one or two new ways of working

  How will use a relational database to store data

Multidimensional Analysis and Data Versus Information - How will use a relational database to store data, be specific.

  How to improve the business process

How might information technology systems be used in operations management to improve the business process

  Computer applications- clipart

Computer Applications- clipart - Explore the Internet to discover at least two additional sites (other than Microsoft) that offer clipart, either free or for a price.

  Case study on social graphs

Case study on social graphs (the term coined by Mark Zuckerberg of Facebook) - The essay has to basically deal with the relatively new phenomenon of social networking.

  Discuss current and emerging technology

Discussing Current and Emerging Technology - Develop and submit an Organizational Technology Plan paper. Provide a summary statement of key topics of the paper. Provide a summary of key points and examples,

  What characteristics of general computer systems

This post addresses computer systems and related issues - What characteristics of general computer systems are you most familiar with and how familiar are you with them? Which characteristics do you understand best? Which characteristics do you und..

  Differences and similarities between a loop start system

Structured Wiring - The response provides you a structured explanation of differences and similarities between a loop start system and a ground start system . It also gives you the relevant references.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd