Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 1 of 8 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 "Ballarat Event Management Company" in the first six months of 2016. 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. Due date: Refer to the course description Submit the individual work via Moodle before the due date. Cutoff date: One week after the due date. Any submission after the due date will receive a deduction of 10% per day, this includes weekends. Marks: This assignment is worth 30% of the total assessment. Extensions: An extension will only be considered with supporting documentation from a health professional and if the problem/illness occurred within the week prior to the due date. If an extension is granted the extension will then equal the number of days specified on the doctor's certificate, with a maximum limit of one week. Authorship: This assignment is an individual assignment and it shall be completed by the individual student only. The final submission must be identifiably the work of the individual. Breaches of this requirement will result in an assignment not being accepted for assessment and may result in the offending student or students being required to present before the Disciplinary Committee.Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 2 of 8 Ballarat Event Management Company: Analysing Sales and Customer Relations Introduction 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 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 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?Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 3 of 8 The fees charged by BEMC to customers (i.e. exhibitors) and their cost for organizing value-added services are as follows: Value Added Service Fee charged (AUD) Cost (AUD) Exhibitor stand organised 1500 1150 Exhibitor travel arrangements 1000 850 Exhibitor accommodation arrangements 800 650 Exhibitor marketing material organised 500 250 Exhibitor hospitality organised 600 500 Table 1: Fees charged and cost of value added services The company receives different rates of 'commissions' (i.e. fees) from the various Trade Fairs for renting 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. BEMC receive commission according to the following table. Commission Received New Exhibitor Area of Space Rate (% of total rent) New Exhibitor (large) Yes >= 20 sq. m. 25 New Exhibitor (small) Yes < 20 sq. m. 30 Returning Exhibitor (large) No >= 20 sq. m. 20 Returning Exhibitor (small) No < 20 sq. m. 25 Table 2: Rates of commission on space rent Note that exhibiting company pays the space rent directly to the trade fair manager and BEMC receives their commission from the trade fair manager. BEMC receives a fee of AUD 1.50 for each Trade Fair visitor ticket sold through its Sales Representatives. Each representative receives AUD 60 for each hour of their service. BEMC 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 New Exhibitor Area of Space Rate (% of commission received by BEMC) New Exhibitor (large) Yes >= 20 sq.m. 30 New Exhibitor (small) Yes < 20 sq.m. 37 Returning Exhibitor (large) No >= 20 sq.m. 27 Returning Exhibitor (small) No < 20 sq.m. 30 Table 3: Rates of representative commission (% of the commission received by BEMC according to Table 2) Task 1: Calculations General calculations (All Students): I. Perform the following calculations using formula:Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 4 of 8 a. Service fee for each value added services b. Cost for organising each value added services c. Total revenue from ticket sales d. Commission received from space rent e. Sales representative's wage f. Sales representative's commission g. Total income h. Total expenses i. Total profit II. 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. III. 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): IV. 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. V. 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): I. Who are BEMC's five best and five worst customers by total profit? Use an appropriate table and graph/chart to summarise these observations. II. What are BEMC's best and worst Trade Fairs by total profit? Use an appropriate table and graph/chart to summarise these observations. III. Who are BEMC's best and worst sales representatives by total income? Use an appropriate table and graph/chart to summarise these observations. Additional observations (ITECH5005 Students Only): IV. What are the best Sales Representative's best- and worst-selling customers (by total profit)? Use an appropriate table and graph/chart to summarise these calculations.Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 5 of 8 V. 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/or graph/chart to summarise these calculations. Task 3: Recommendations Ms White also requires you to make recommendations concerning the following business-level questions. General recommendations (All Students): I. Should BEMC focus on any particular (a) customer(s) and (b) Trade Fair(s), in future, and why? II. What are your recommendation regarding what other data should be collected to improve decision making for BEMC? Additional recommendations (ITECH5005 Students Only): III. What are your recommendation regarding record-keeping to improve the quality of data collection and management? IV. What changes should BEMC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why? Assignment requirements Ms White has asked you to provide her with the details of how you have accomplished these tasks. Therefore, you must include the functions and the formula you've used in your analyses, and not simply report the answers. As a paid consultant, your submission to Ms White must be professionally presented; all analyses, calculations and summaries 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 observations and recommendations must be discussed in your report with justifications from calculations done in excel (e.g. include charts 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 White. 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.Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 6 of 8 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, 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 using Pivot tables 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 analyse beyond the assessable tasks to gain sufficient insight into the business operations to provide recommendations to Ms White. Include any additional analyses or summaries on the calculation worksheets. 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 assignment 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 of the topics covered in the tutorials). This assignment is worth 30% of total marks for this course and will be marked on:  your demonstrated understanding of the problems;  completeness of the tasks;  exploration of the features in Microsoft Excel and Office; and  quality of the submission (Excel workbook & Word document). Submission All students are required to submit two (2) files – (a) Excel workbook with all calculations, graphs and charts, and (b) well-formatted report on word, for this assignment, via Moodle, prior to the due date and time.Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 7 of 8 Marking Guide ITECH1005 ⃝ ITECH5005 ⃝ Student Name: _________________________________ Student ID: ______________________ Marker: _______________________________________ Tasks Marks Calculations (All Students): I. Calculations: a. Service fee for each value added services b. Cost of each value added services c. Total from ticket sales d. Commission received from space rented e. Sales representative's wage f. Sales representative's commission g. Total income h. Total expenses i. Total profit II. Total profit, sub-totalled by each (a) Trade Fair, and (b) Sales Representative III. Total hours worked, sub-totalled by each (a) Trade Fair, and (b) Sales Representative 20 2 2 1 2 2 2 1 1 1 3 3 Additional Calculations (ITECH5005 Students Only): IV. Total income, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) Income type V. Total expenses, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) Expense type 10 5 5 Observations (All students): I. Who are BEMC's five best and five worst customers by total profit? II. What are BEMC's best and worst Trade Fairs by total profit III. Who are BEMC's best and worst sales representatives by total income? 10 5 2 3 Additional Observations (ITECH5005 Students Only): IV. What are the best Sales Representative's best- and worst-selling customers (by total profit)? V. What would happen to profits if the rate of pay to Sales Representatives for hours worked on sales was increased by 40%? 5 3 2 Recommendations (All Students): I. Should BEMC focus on any particular (a) customer(s) and (b) Trade Fair(s), in future, and why? II. What are your recommendation regarding what other data should be collected to improve decision making for BEMC? 5 3 2 Additional Recommendations (ITECH5005 Students Only): III. What are your recommendation regarding record-keeping to improve the quality of data collection and management? IV. What changes should BEMC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why? 5 2 3 Report Presentation: Report is well-written and presented professionally, containing:  Cover letter  Title page  Executive Summary (outlining the scope of report, key findings and recommendations)  Table of Contents  Appropriate use of headings within report  Formula used for key calculations (e.g., Commission, income, expenses, profit) 15 2 1 2 1 1 2Assignment School Engineering and Information Technology ITECH1005-5005: Business Information Systems 8 of 8  Appropriate use of figures (i.e. graphs, summary tables) and reference to calculations and summaries to justify all observations and recommendations  Overall structure, presentation and formatting. Note that the report has to be presented formally. It must include discussion of calculations, observations and recommendations with graphs and/or tables. It can't be just a collection of excel tables and/or graphs. 2 4 Deductions:  Late submission (deduction - 10% per day)  MS Excel workbook and Word document not named correctly (deduction - 0.5 marks each)  Formulas not used in calculations as required (deduction – up to 5 marks)  Pivot Tables and Graphs/Charts not used in analyses as required (deduction – up to 5 marks)  Analyses and summaries in the report do not have headings and discussions (deduction – up to 5 marks) Total marks ITECH1005 students: 50 Total marksTECH5005 students: 70 Contribution to total marks (out of 30) Comments: