ITECH1005-5005 | business Information Systems Assignment Question | Business

Aims

To analyze a set of data (in Microsoft Excel), and write a ITECH1005-5005 business information systems assignment question, identifying and explaining your insights into the operation of Trade Fair organized by “Latrobe Valley Product Gallery”.

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, analyze it and visualize 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

Latrobe Valley Product Gallery (LVPG) specializes 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 (small and large), to new or returning exhibitors, for a number of Trade Fairs locally and overseas. LVPG also provides a number of additional value-added services to exhibitors for fees such as organizing 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. LVPG also organize 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 Diamantine Rose, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.

Ms Rose 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 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 Rose has provided you with last year’s sales information in the “ITECH1005_assignment_data_S1_2016.xlsx” file. The data needs to be analyzed and visualized to help observations related to the business operation and its shortcomings.
The data worksheet “Sales” contains collected information of the LVPG’s operations in the year 2015. 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 (minutes)
• Returning Exhibitor?
• Visitor tickets sold
• Exhibitor stand organized?
• Exhibitor travel organized?
• Exhibitor accommodation organized?
• Exhibitor marketing material organized?
• Exhibitor hospitality organized?
• The fees charged by LVPG to customers (i.e. exhibitors) for their value-added services are as follows:

Value Added Service	Fee (AUD)
Exhibitor stand organized	600
Exhibitor travel arrangements	300
Exhibitor accommodation arrangements	280
Exhibitor marketing material organized	500
Exhibitor hospitality organized	380

• Table 1: Rates of value added services
• The company receives different rates of ‘commissions’ (i.e. fees) from the various Trade Fairs for selling space to companies exhibiting at the various fairs. The Trade Fair managers charge AUD 80 per square meter (sq.m.) for floor space at all Trade Fairs. LVPG receive commission according to the following table.

Commission Received	Space Sold	Rate (% of sale amount)
New Exhibitor (large)	>= 20 sq.m.	30
New Exhibitor (small)	< 20 sq.m.	25
Returning Exhibitor (large)	>= 20 sq.m.	20
Returning Exhibitor (small)	< 20 sq.m.	15

• Table 2: Rates of commission on space sold
• LVPG receives a fee of AUD 2 for each Trade Fair visitor ticket sold through its Sales Representatives.
• Each representative receives AUD 50 for each hour of their service. LVPG pays commissions to the Sales Representatives for selling the space to those exhibiting companies. The following table describes how these commissions are calculated:

Commission Paid	Space Sold	Rate (% of commission received)
New Exhibitor (large)	>= 20 sq.m.	25
New Exhibitor (small)	< 20 sq.m.	20
Returning Exhibitor (large)	>= 20 sq.m.	15
Returning Exhibitor (small)	< 20 sq.m.	10

• Table 3: Rates of representative commission (% of the commission received by LVPG according to Table 2)

Calculations

• Perform the following calculations using formula:

1. Service fee for each value added services
2. Total revenue from ticket sales
3. Commission received from space sold
4. Sales representative’s wage
5. Sales representative’s commission
6. Total income
7. Total expenses
8. Total profit

• Total profit, sub-totaled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summaries these calculations.
• Total hours worked, sub-totaled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summaries these calculations.
• Total sales, sub-totaled by each (a) Trade Fair, (b) Sales Representative, and (c) Income type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summaries these calculations.
• Total expenses, sub-totaled by each (a) Trade Fair, (b) Sales Representative, and (c) Expense type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summaries these calculations.

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

Observations

• Who are LVPG’s five best and five worst customers by total sales? Use an appropriate table and graph/chart to summaries these observations.
• What are LVPG’s best and worst Trade Fairs by total profit? Use an appropriate table and graph/chart to summaries these observations.
• Who are LVPG’s best and worst sales representatives by total profit? Use an appropriate table and graph/chart to summaries these observations.
• What are the best Sales Representative’s best- and worst-selling services (by total profit)? Use an appropriate table and graph/chart to summaries these calculations.
• 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 summaries these calculations.

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

Recommendations (All Students)

1. Should LVPG focus on any particular (a) customer(s) and (b) Trade Fairs, in future, and why?
2. What are your recommendation regarding what other data should be collected to improve decision making for LVPG?

Recommendations (ITECH5005 Students Only):

• What are your recommendation regarding record-keeping to improve the quality of data collection and management?
• What changes should LVPG make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why?

Ms Rose 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.

As a paid consultant, your submission to Ms Rose must be professionally presented; all analyses, calculations and summaries in the Excel file must have 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).

Some Hints

The business options are linked to a number of questions posed by Ms Rose. 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.

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 analyzing (with formulas) and visualizing (with charts) your data, and your ability to link your observations to business issues.

There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning your workbook, you may wish to review the lab exercises 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).

The Excel workbook contains all the necessary data for your assignment. Columns headings required for calculations (I) are provided in the Excel workbook. You may have to add (or insert) more columns (for some intermediate calculations) and/or worksheets for the rest of the calculations and observations. Also, 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 very likely need to analyze beyond the assessable tasks to gain sufficient insight into the business operations to provide recommendations to Ms Rose. Include any additional analyses or summaries on the calculations worksheet.

Make sure you review the marking guide (attached at the end of this assignment specification), to ensure you complete all required tasks before submission.

Assignment Assessment

The questions posed to you in this ITECH1005-5005 business information systems assignment question range from easy, through medium to challenging. However, it is expected that all students will succeed in all the tasks. Some of the tasks are very similar to those covered in tutorials and the associated exercises. Some of the challenging tasks may rely on insight that requires self-directed study (e.g. using help facilities in Excel to explore in depth some of the topics covered in the tutorials).