bussiness info. system

Latrobe Valley Product Gallery: Analysing Sales and Customer Relations Introduction Latrobe Valley Product Gallery (LVPG) 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. LVPG 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. LVPG 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 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.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 • 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 LVPG to customers (i.e. exhibitors) for their value-added services are: Assignment School Engineering and Information Technology ITECH1005: Business Information Systems 3 Value Added Service Fee ($A) Exhibitor stand organised 2000 Exhibitor travel arrangements 250 Exhibitor accommodation arrangements 100 Exhibitor marketing material organised 800 Exhibitor hospitality organised 150 The data worksheet “Fairs” contains collected information of the Trade Fairs over the past decade. These details included: • Trade Fair • Year • space available for LVPG to sell to exhibitors (sq.m.) • Visitor tickets sold by LVPG • Total Profit for LVPG LVPG receives a fee ($4) 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. 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 $130 per square meter 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. 20 New Exhibitor (small) < 20 sq.m. 15 Returning Exhibitor (large) >= 20 sq.m. 10 Returning Exhibitor (small) < 20 sq.m. 5 LVPG then pays commissions to the Sales Representatives for selling the space to those exhibiting companies. Each representative also receives $15 for each hour of their service. The following tables describe how these commissions are calculated: Commission Paid Space Sold Rate (% of commission received) New Exhibitor (large) >= 20 sq.m. 10 New Exhibitor (small) < 20 sq.m. 8 Returning Exhibitor (large) >= 20 sq.m. 5 Returning Exhibitor (small) < 20 sq.m. 2 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. Assignment School Engineering and Information Technology ITECH1005: Business Information Systems 4 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 Rose requires you to make observations to help her with the following questions. Observations (All students): f) Who are LVPG’s five best and five worst customers by total sales? Use an appropriate table and graph/chart to summarise these observations. g) What are LVPG’s best and worst value-added services by total profit? Use an appropriate table and graph/chart to summarise these observations. h) What are LVPG’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 45%? Use an appropriate table and graph/chart to summarise these calculations.  l) Should LVPG 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 LVPG? o) What are your recommendation regarding record-keeping to improve the quality of data collection and management? 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. Create a new worksheet in the assignment workbook called “Calculations” to provide Ms Rose with these calculations and summations. 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 Assignment School Engineering and Information Technology ITECH1005: Business Information Systems 5 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 analysing (with formulas) and visualising (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, 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). The Excel workbook contains all the necessary data for your assignment. However, 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 analyses 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. You might check the space available and compare it to the space sold this year.