Delivery in day(s): 4
ITECH1005-5005 Business Information Systems Assignment Brief
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 “Ballarat Event Management Company” in the first six months of 2016.
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.
Ballarat Event Management Company (BEMC) specialises in promoting and selling both (i) stand space at trade fairs and (ii) tickets for visitors to trade fairs in Ballarat region. The company currently promotes and sells two different sized stand spaces (small and large), to new or returning exhibitors, for a number of Trade Fairs locally and overseas. BEMC also provides 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 trade fair. BEMC 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 Diamantina White, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.
Ms White has recently hired you as her business implications 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 White has provided you with the half-yearly sales information of this year (2016) in the “BEMC_sales_first_half_2016.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 BEMC’s operations in the first six months of 2016. These details included:
- Date (of customer order),
- Trade Fair
- Customer (i.e. exhibiting company)
- Area of space rented (sq. m.)
- Sales Representative
- Time spent by Sales Representative to complete sale (minutes)
- Returning Exhibitor?
- Number of visitor tickets sold
- Exhibitor stand organised?
- Exhibitor travel organised?
- Exhibitor accommodation organised?
- Exhibitor marketing material organised?
- Exhibitor hospitality organised?
Task 1: Calculations
General calculations (All Students):
1. Perform the following calculations using formula:
- Service fee for each value added services
- Cost for organising each value added services
- Total revenue from ticket sales
- Commission received from space rent
- Sales representative’s wage
- Sales representative’s commission
- Total income
- Total expenses
- Total profit
2. 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.
3. 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.
Additional calculations (ITECH5005 Students Only)
4. Total income, 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.
5. 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.
Task 2: Observations
Ms White requires you to make observations to help her with the following questions.
General Observations (All students):
- Who are BEMC’s five best and five worst customers by total profit? Use an appropriate table and graph/chart to summarise these observations.
- What are BEMC’s best and worst Trade Fairs by total profit? Use an appropriate table and graph/chart to summarise these observations.
- Who are BEMC’s best and worst sales representatives by total income? Use an appropriate table and graph/chart to summarise these observations.
Task 3: Recommendations
Ms White also requires you to make recommendations concerning the following business-level questions.
General recommendations (All Students):
- Should BEMC focus on any particular (a) customer(s) and (b) Trade Fair(s), in future, and why?
- What are your recommendation regarding what other data should be collected to improve decision making for BEMC?
Additional recommendations (ITECH5005 Students Only):
- What are your recommendation regarding record-keeping to improve the quality of data collection and management?
- What changes should BEMC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why?