tickets for visitors to trade fairs in Ballarat area

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 promotes and sells two different sizedstand spaces, to new or returning exhibitors, for a number of Trade Fairs locally and overseas. BTFC also providesa number of additional value-added services to exhibitors for fees, such as organising exhibitor stands, developingmarketing material for exhibitors to distribute at fairs, arranging travel and/or accommodation for exhibitors, andhospitality/catering for exhibitors to offer their customers attending the trade fair. BTFC also organise visitortickets for the trade fairs, which also provide commission income. All these activities are managed through theefforts of a number of sales representatives. 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 recently hired you as her business analyst and poses some operational-level questions about theperformance of her business. She is also eager to hear your thoughts and ideas on how to improve the businessand requires you to make several recommendations on how to improve the company’s performance, especially inrelation 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-05 assignment data.xlsx”file. The data needs to be analysed and visualised to help observations related to the business operation and itsshortcomings.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.)AssignmentSchool of Science, Information Technology and EngineeringITECH1005-5005: Business Information Systems? 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 soldThe additional fees charged by BTFC to customers (i.e. exhibitors) for their value-added services are:Value Added Service Fee ($A)Exhibitor stand organised 1000Exhibitor travel arrangements 100Exhibitor accommodation arrangements 100Exhibitor marketing material organised 1000Exhibitor hospitality organised 100The data worksheet “Fairs” contains collected information of the Trade Fairs over the past decade. These detailsincluded:? Trade Fair? Year? space available for BTFC to sell to exhibitors (sq.m.)? Visitor tickets sold by BTFC? Total Profit for BTFCBTFC receives a fee ($5) for each Trade Fair visitor ticket sold through its Sales Representatives. This historicalinformation 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 tocompanies exhibiting at the various fairs. The Trade Fair managers charge $120 sq.m. for floor space at all TradeFairs, which rate BTFC charges the exhibiting companies. BTFC then pays commissions to the SalesRepresentatives for selling the space to those exhibiting companies. The following tables describe how thesecommissions are calculated:Commission Received Space Sold Rate (% of sale amount)New Exhibitor (large) >= 20 sq.m. 10New Exhibitor (small)Returning Exhibitor (large) >= 20 sq.m. 7.5Returning Exhibitor (small)Commission Paid Space Sold Rate (% of commissionNew Exhibitor (large) >= 20 sq.m. 20received)AssignmentSchool of Science, Information Technology and EngineeringITECH1005-5005: Business Information SystemsNew Exhibitor (small)Returning Exhibitor (large) >= 20 sq.m. 15Returning Exhibitor (small)BTFC also currently pays each Sales Representative a fee for their time spent working on a sale ($10 per hour).Assessable TasksMs 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) Customer, and (c) income type.b. total profit, sub-totalled by each (a) Trade Fair, and (b) Customer.c. total costs, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) cost type.d. total hours worked, sub-totalled by each (a) Trade Fair, and (b) Customer.e. percentage of current (a) sales, and (b) profits attributed to each Trade Fair.Calculations (ITECH5005 Students Only):f. costs as a percentage of (a) sales, and (b) profits, sub-totalled for each Sales Representative.g. changes to profitability over the past decade, including current year’s figures, sub-totalled by eachMs Holt requires you to make observations to help her with the following operational-level questions.Observations (All students):h. Who are BTFC’s best and worst customers by total profit?i. What are BTFC’s best and worst value-added services by total sales?j. Who are BTFC’s best and worst Sales Representatives by total profit?k. What would happen to profits if the commission rate of pay to Sales Representatives was increasedObservations (ITECH5005 Students Only):l. What are the worst Sales Representative’s best- and worst-selling services (by total profit)?m. Who are the best Sales Representative’s best and worst customers (by total profit)?n. Who are the best Sales Representative’s best and worst Trade Fairs (by total profit)?o. How much additional value-added services are required to be sold at the least profitable Trade FairMs Holt also requires you to make recommendations concerning the following business-level questions.Recommendations (All Students):p. Should BTFC focus on any particular Trade Fair(s) or sales activities now and/or in future, and why?q. What are your recommendations for resolving any data redundancy issues observed in ther. What are your recommendation regarding what other data should be collected to improve decisions. What are your recommendation regarding record-keeping to improve the quality of data collectionRecommendations (ITECH5005 Students Only):Trade Fair.by 50%?(by total profit) to increase the profit by 25%?workbook?making for BTFC?and management?AssignmentSchool of Science, Information Technology and EngineeringITECH1005-5005: Business Information Systemst. What changes should BTFC make regarding any currently employed Sales Representative(s), or to theMs Holt has asked you to provide her with the details of how you have accomplished these tasks. Therefore, youmust 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 Holt with thesecalculations and summations.As a paid consultant, your submission to Ms Holt must be professionally presented; all analyses, calculations andsummaries in the Excel file must have headings and be supported with explanatory notes; and the formalbusiness report must be preceded by a ‘covering letter’ (i.e. incorporate letter as first page of your Worddocument with your report on following pages).Note: All recommendations in your report must be clearly justified (e.g. include charting or refer to specificanalyses/summaries from the Excel spreadsheet).Some HintsThe business options are linked to a number of questions posed by Ms Holt. Your role is to answer these specificquestions 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 yourobservations.There is no ‘ideal’ or ‘best’ way of doing this project. You’ll be assessed on your insight of the data, on your abilityto make observations by analysing (with formulas) and visualising (with charts) your data, and your ability to linkyour observations to business issues.There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning yourworkbook, and especially the observations, you may wish to review the tutorials on the related topics (e.g. Excelfunctions, absolute and relative cell referencing, formatting, chartings and managing large worksheets, as well ason 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 therecords 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 businessoperations to provide recommendations to Ms Holt. Include any additional analyses or summaries on thecalculations worksheet.You might check the space available and compare it to the space sold this year.Make sure you review the marking guide, to ensure you complete all required tasks before submission.Assignment AssessmentThe questions posed to you in this assignment range from easy, through medium to challenging. However, it isexpected that all students will succeed in all the tasks. Some of the tasks are very similar to those covered intutorials 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).This assignment is worth 30% of total marks for this course and will be marked on:fees paid to the Sales Representatives, and why?AssignmentSchool of Science, Information Technology and EngineeringITECH1005-5005: Business Information Systems? your demonstrated understanding of the problems;? completeness of the tasks;? exploration of the features in Microsoft Office; and? quality of the submission (Excel workbook & Word document).Assignment checkpoints: During your lab classes in weeks 7 to 11 you may be asked by your tutors to showevidence of your consistent work on your assignment.SubmissionAll students are required to submit two (2) files for this assignment, via Moodle, prior to the due date and time.Your two submission files will be a (re-named) Microsoft Excel spreadsheet (containing your analyses) and aMicrosoft Word document (containing your covering letter and report).For undergraduate students, each file must be named as follows:? itech1005_2014-05_YourSurname_YourStudentID.doc (or .docx); and? itech1005_2014-05_YourSurname_YourStudentID.xls (or .xlsx)For post-graduate students, each file must be named as follows:? Itech5005_2014-05_YourSurname_YourStudentID.doc (or .docx); and? Itech5005_2014-05_YourSurname_YourStudentID.xls (or .xlsx)AssignmentSchool of Science, Information Technology and EngineeringITECH1005-5005: Business Information SystemsMarking GuideTasks MarksCalculations (All Students):a. total sales, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) income type.b. total profit, sub-totalled by each (a) Trade Fair, and (b) Customer.c. total costs, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) cost type.d. total hours worked, sub-totalled by each (a) Trade Fair, and (b) Customer.e. percentage of current (a) sales, and (b) profits attributed to each Trade Fair.Calculations (ITECH5005 Students Only):f. costs as a percentage of (a) sales, and (b) profits, sub-totalled for each Sales Representative.g. changes to profitability over the past decade, including current year’s figures, sub-totalled by eachTrade Fair.Observations (All students):h. Who are BTFC’s best and worst customers by total profit?i. What are BTFC’s best and worst value-added services by total sales?j. Who are BTFC’s best and worst Sales Representatives by total profit?k. What would happen to profits if the commission rate of pay to Sales Representatives was increasedby 50%?Observations (ITECH5005 Students Only):l. What are the worst Sales Representative’s best- and worst-selling services (by total profit)?m. Who are the best Sales Representative’s best and worst customers (by total profit)?n. Who are the best Sales Representative’s best and worst Trade Fairs (by total profit)?o. How much additional value-added services are required to be sold at the least profitable Trade Fair(by total profit) to increase the profit by 25%?Recommendations (All Students):p. Should BTFC focus on any particular Trade Fair(s) in future, and why?q. What are your recommendations for resolving any data redundancy issues observed in theworkbook?r. What are your recommendation regarding what other data should be collected to improve decisionmaking for BTFC?s. What are your recommendation regarding record-keeping to improve the quality of data collectionand management?Recommendations (ITECH5005 Students Only):t. What changes should BTFC make regarding any currently employed Sales Representative(s), or tothe fees paid to the Sales Representatives, and why?Submission Presentation:u. All analyses and summaries in Excel workbook have clear and meaningful explanatory notesv. ‘Covering letter’ to the owner is well-written (succinct with clear purpose, professional tone)w. Report is well presented and well-written, containing:? Title page? Executive Summary (outlining scope of report, key findings and recommendations)? Table of Contents? Appropriate use of headings within report? Appropriate use of figures (i.e. graphs, summary tables) and reference to calculations andsummaries to justify recommendationsAssignmentSchool of Science, Information Technology and EngineeringITECH1005-5005: Business Information SystemsDeductions:Late submission (deduction – 10% per day)MS Excel workbook and Word document not named correctly (deduction – 0.5 marks)Calculations worksheet not created or named correctly in MS Excel workbook (deduction – 0.5 marks)All analyses and summaries in workbook do not have headings and explanatory notes (deduction – 0.5marks)Report is not prefaced by a ‘covering letter’ (deduction – 0.5 marks)Total marks ITECH1005 students: 30Total marksTECH5005 students: 45Contribution to total marks

Order your essay today and save 30% with the discount code: KIWI20