Assessment Task 2: Simulation Activity | My Assignment Tutor

Page 1Assessment Task 2: Simulation Activity (Modelling Project) Aligned subjectlearning outcomes• relate principles of modelling to investigate business issues;• apply spreadsheet modelling skills to create decision models;• employ decision models to examine, analyse and solve business decisionproblems.Aligned professionalstandards/competencies or individualIndividualWeighting30%Due date ASSESSMENT TASK 2: DESCRIPTIONThe aim of this assessment is to apply modelling principles, data analytics and modelling skills to develop adecision model for a given business scenario using the information provided.You are the business analyst of Omega Pty Ltd and have been given information regarding the businessactivities for the Year 2019, 2020 and 2021. The General Manager requests you to develop a decisionmodel to analyse these information and submit a 1000 word summary report on the findings of youranalysis and recommendation for the proposed Maroochydore store.The business problemOmega Pty Ltd, an Australian company selling whitegoods and consumer electronics including WashingMachines, Fridges, Computers and Tablets, Microwaves, TVs, Mobile Phones, Games and Consoles. Salesstaff have been trained to sell all items.Omega has sales outlets in Brisbane, Gold Coast, Townsville, and Cairns and is considering opening a newstore at Maroochydore. The sales volumes and costs from this new store are expected to be similar to theGold Coast, but there is an establishment cost of $1million. The establishment cost are cost of setting upthe new store (buying the building and renovation).Mr Andy William, the financial analysis of Omega Pty Ltd, has worked up several options to financial the $1million establishment cost.Page 2Andy has recommended Option 1 as the best option in financing the new store establishment cost. Theannual loan amount of $244,627 will be used in simulating the income statement Maroochydore store.(For detail analysis of the financing options – see Module 8: Topic 3)The General Manager is uncertain what staffing levels should be set for this new Maroochydore outlet, andrequests you to provide several staffing level options on the feasibility of the Maroochydore store.You are the business analyst of Omega Pty Ltd and have been given information regarding the businessactivities for the Year 2019, 2020 and 2021. The General Manager requests you to develop a decisionmodel to analyse these information.The general manager specifically asks you to develop a decision model using Excel and develop some usefulKEY PERFORMANCE INDICATOR DASHBOARD (KPIs) for analysis. The general manager also requests youto submit a 1000 words summary report to:1. highlight the major findings of your analysis, and2. recommend feasibility options of the proposed Maroochydore store.The summary report will be use to brief the executive management meeting next month.InstructionsIn helping you to prepare the summary report, you MUST do the following to provide evidence to supportyour discussion and recommendation.Part 1: Decision Model (75%)You will be provided with a decision modelling template. The template contains many worksheets that hasbeen designed to assist you to build the decision model for analysis and to build useful performancedashboard.You are required to apply modelling principles, spreadsheet engineering technique and modelling skills tocomplete the template and develop an analytic decision model.You are provided with series of videos in the learning activities to guide you to complete the template.Part 2: Summary Report & Communication (25%)Structure your summary report using headers. Focus the report on the findings of your analysis.Recommendation for Maroochydore store must be supported by your findings and analysis.A sample report structure is provided for your reference.Page 3Summary Report Sample1. Profitability AnalysisOver the last three years, the outlets had contributed an accumulated total of A$xxxxxx profit to OmegaPty Ltd. Brisbane outlet has been the major profit contributor for the past 3 years. Cairns outlet hasoutperformed Brisbane in 201X. Brisbane, Townsville and Cairns outlets show positive growth trends butGold Coast has demonstrated poor performance with a negative growth trend. For product profitability, HiFi Systems are most profitable follow by TVs and Fridges. (Your reference: worksheet)2. Financial Performance Analysis3. Sales Performance Analysis4. Feasibility of Maroochydore storeSubmission1. Log on to you subject site on LearnJCU and select the assessment tab2. Select the link to the assessment task 2 dropbox and upload your (1) decision model and (2) summaryreport.Note:(1) Rename the template to include your name before submission (yourname.xlsx)(2) Include a cover page for the summary report. The cover page must have your name and studentnumber.Page 4Business Activities Information of OmegaCost and revenue figures are available for the financial years ending 30 June 2018, 2019 and 2020. Thestaffing levels at each location for the past three years has been:1. Staffing Levels StaffBrisbaneGold CoastTownsvilleCairnsManager1111Admin staff2211Sales staff10954 2. Labour cost for staff pa/personThe total labour cost (including salary and all salary related overheads) for staff are: Staff Level201920202021Manager75,00078,00080,000Admin staff40,00041,00042,000Sales staff58,00060,00063,000 Apart from labour costs, all other costs are:• Cost of goods sold (COGS)• Fixed admin & operating costs (FAOC) (no increases in the past 3 years)• Advertising and promotional costs (APC)• Variable unit admin & operating costs (VUAOC) which is independent of location; where thetotal annual variable admin & operating cost for a location = VUAOC*Average total inventory atthat location for that year.The average wholesale cost for each item, together with the average price the item was sold for duringthe past 3 years are:3. Average wholesale cost and average sale price ($/item) ItemAv wholesale cost ($/item)Av Sale Price ($/item)Washing Machines635750Fridges550650Computers and Tablets660850Microwaves300400TVs490700Mobile Phones540800Games and Consoles340480 Page 54. Fixed Admin & Operation Cost (FAOC) – (no increase in 3 years) YearBrisbaneGold CoastTownsvilleCairns2019300,000285,000130,000115,0002020300,000285,000130,000115,0002021300,000285,000130,000115,000 5. Advertising and promotional cost (APC) YearBrisbaneGold CoastTownsvilleCairns201995,00090,00060,00040,000202095,00085,00070,00040,0002021100,00085,00075,00042,000 6. Variable unit admin & operating costs (VUAOC)A different VUAOC value has been assigned to each student for this assignment.VUAOC Cost for locations ($ per average item in inventory per year) StudentBrisbaneGold CoastTownsvilleCairnsGroup 13,5753,5902,8452,466Group 23,5983,8663,4452,639Group 33,7423,2022,8362,261Group 43,5073,7353,4032,696Group 54,1083,0643,0702,448Group 64,0623,3502,9012,315Group 74,0553,2283,0292,393Group 83,7723,3953,1552,190Group 93,5273,2203,2302,224Group 103,8083,8093,3362,274Group 114,1603,3952,9392,514Group 123,6463,4382,8262,427Group 134,0753,6533,0712,161Group 144,2493,0363,2482,582Group 153,6293,3572,8912,638Group 164,2043,1263,2222,447Group 173,9683,9743,0102,106Group 183,6463,4823,1992,786Group 193,8573,6263,3322,404Group 204,1993,3933,3572,036Group 213,7623,3023,0332,382Group 223,8523,2543,4602,194 Page 6 Group 233,9493,1652,8262,084Group 244,3723,6793,4422,140Group 253,7093,3333,1632,192Group 263,5883,5803,2672,471Group 273,8923,0253,1912,369Group 283,6783,5703,2172,633Group 294,0033,2253,3792,693Group 304,0833,1053,3732,758Group 314,0093,9743,2502,258Group 324,1553,0753,3562,263Group 334,3203,5133,1162,222Group 344,4413,7412,9962,217Group 353,9593,4462,8752,732Group 364,4383,6392,9122,765Group 374,3053,2833,3842,194Group 384,1593,3823,4372,159Group 394,4983,5543,4752,156Group 404,3733,7202,9442,011Group 414,3113,9023,4292,759Group 423,9793,7153,3302,524Group 434,3643,6032,9542,686Group 443,8673,5453,2582,753Group 454,4263,5603,3172,569Group 464,1663,5793,4372,592Group 473,5613,2633,3362,396Group 484,4563,7163,1562,362Group 494,3733,2003,3212,485Group 503,5233,2133,3242,231Group 514,3203,7543,3582,628Group 523,6903,5832,8152,487Group 534,1183,2643,1522,496Group 543,6803,9723,2462,689Group 554,1403,1573,2742,294Group 563,7613,1343,1722,747Group 574,3663,1643,3932,527Group 584,2303,7473,3012,512Group 594,4423,3162,8372,757Group 603,6883,2273,1012,665 Page 77. Average inventories of each item/product during the past 3 years7.1 Average Inventories (units) For Financial Year Ending June 2019 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines19231815Fridges31261714Computers and Tablets1413109Microwaves31201912TVs1615117Mobile Phones2423147Games and Consoles251798 7. 2 Average Inventories (units) For Financial Year Ending June 2020 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines24251513Fridges30261614Computers and Tablets101298Microwaves24181419TVs14121110Mobile Phones27251315Games and Consoles25211716 7.3 Average Inventories (units) For Financial Year Ending June 2021 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines28202513Fridges35202223Computers and Tablets141965Microwaves32262817TVs27121617Mobile Phones34272316Games and Consoles23142418 Page 88. Actual sales for each item/product for the past 3 years8.1 Actual Sales (unit) For Financial Year Ending June 2019 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines1,8371,316871683Fridges3,3982,7901,5711,246Computers and Tablets884542516640Microwaves2,4871,9681,2551,150TVs1,7121,364714686Mobile Phones2,2381,7091,104972Games and Consoles2,2811,7291,121914 8.2 Actual Sales (unit) For Financial Year Ending June 2020 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines1,9281,5121,1161,073Fridges3,0882,2601,7101,455Computers and Tablets769679589601Microwaves2,7401,9271,3101,066TVs1,6551,057768779Mobile Phones2,3811,5791,2001,123Games and Consoles2,2891,5641,201884 8.3 Actual Sales (unit) For Financial Year Ending June 2021 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines2,3961,2881,5361,249Fridges3,6192,0862,0282,129Computers and Tablets878424467665Microwaves2,6561,6421,7291,360TVs1,8378569981,267Mobile Phones2,9271,4621,5111,578Games and Consoles2,6001,2101,3471,467 Page 99. Sales Target (units) for the past 3 years9.1 Sales Target (unit) For Financial Year Ending June 2019 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines2,0001,8001,100800Fridges3,0002,6001,6001,100Computers and Tablets800700500300Microwaves2,4002,1001,300900TVs1,4001,200800500Mobile Phones2,0001,8001,100800Games and Consoles1,9001,7001,000700 9.2 Sales Target (unit) For Financial Year Ending June 2020 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines2,0001,6001,100900Fridges3,0002,3001,6001,300Computers and Tablets800700500400Microwaves2,4001,9001,3001,000TVs1,4001,100800600Mobile Phones2,0001,6001,100900Games and Consoles1,9001,5001,100800 9.3 Sales Target (unit) For Financial Year Ending June 2021 ItemBrisbaneGold CoastTownsvilleCairnsWashing Machines2,1401,4001,3001,100Fridges3,2102,1001,9001,700Computers and Tablets856600500500Microwaves2,5681,7001,5001,300TVs1,4981,000900800Mobile Phones2,1401,4001,3001,100Games and Consoles2,0331,4001,2001,100 Page 7Project DeliverablesIn this simulation activity, students are to demonstrate their ability to apply modelling principles, data analyticsand modelling skills to develop a decision model using Microsoft Excel for a given business scenario using theinformation provided.Together with the decision model, students must also submit a summary report (word limit: 1000 words notincluding appendices) of their findings and recommendations.Note: Students should keep a back-up copy of their decision model.The assignment is due on 24/05/2020. This project contributes 30% to the overall subject grade.PROJECT ASSESSMENT CRITERIA• Decision model (75%)• Summary Report (25%)(See attached criteria sheet for more details)1. DECISION MODEL (75%) include the followings:a) Black Box Model (Full Model)b) Influence Diagram for Net Profit – (see Model Assumptions)c) Complete data analytics with income statement, “what if” analysis, pivot tables, and interactivePerformance KPI Dashboards with slicers.d) Feasibility analysis of Maroochydore store2. SUMMARY REPORT (25%)In the report, students MUST analyse and discuss the findings for General Managera) Financial profitability analysis by location and by yearb) Product profitability analysis by location by yearc) Sensitivity Analysis (what-if-analysis)d) Analysis and recommendation for Maroochydore store. You must justify your recommendation basedon your analysis and findings.(Note: Recommendation may varies for each students due to different in VUAOC cost)Students will be given a template as a guide to assist in developing the decision model.Page 8Model Assumptions (Formula for Excel Calculations)Profitability & Financial Performance(For Influence Diagram)Net Profit before tax = Total Revenue – Total Operating Costs• Total Revenue = Revenue 2019 + Revenue 2020 + Revenue 2021o Revenue (2019) = Actual Sales (2019) * AV sales price ($/item)o Revenue (2020) = Actual Sales (2020) * AV sales price ($/item)o Revenue (2021) = Actual Sales (2021) * AV sales price ($/item)• Revenue = Actual Sales * Av Sales price ($/item)• Total Operating Cost = Operating Costs 2019 + Operating Costs 2020 + Operating Cost 2021o Operation Costs (2019) = Labour Cost (2019) + FAOC (2019) + VUAOC(2019) +APC (2019) + COGS (2019)o Operation Costs (2020) = Labour Cost (2020) + FAOC (2020) + VUAOC(2020) +APC (2020) + COGS (2020)o Operation Costs (2020) = Labour Cost (2020) + FAOC (2020) + VUAOC(2020) +APC (2021) + COGS (2021)• Labour Cost = Staffing Level * Labour Cost for staff pa/person• FAOC (given)• APC (given)• COGS = Actual Sales * Av wholesale cost ($/item)• VUAOC = VUAOC Cost for Location * Total Av inventories for LocationSales PerformanceSales Performance = (Actual Sales – Sales Target) / Sales TargetRevenue Contribution = Average Revenue / Total Average RevenuePage 9Maroochydore Store AnalysisAssumptions:• The sales volumes and costs of the proposed Maroochydore store are expected to be similar to the GoldCoast.• There is an establishment cost of $1 million. But financing through a 5 year Mbank loan. Annual loanpayment is calculated using Financing Option 1.• The Forecast Income Statement for Maroochydore is similar to the Gold Coast with additional annualestablishment cost (annual loan payment)Scenario AnalysisScenario 1:Setting staff level to 12 staff (1 Manager, 2 Admin Staff, 9 Sale Staff)Construct a forecast income statement for Maroochydore and varying revenue (0% growth, 10% decrease, 10%increase and 20% increase) ANDScenario 2:Setting staff level to 6 staff (1 Manager, 1 Admin Staff, 4 Sale Staff) – 0% growth in revenueScenario 3:Setting staff level to 6 staff (1 Manager, 1 Admin Staff, 4 Sale Staff) – 5% growth in revenue with establishmentcost.Scenario 4:Setting staff level to 6 staff (1 Manager, 1 Admin Staff, 4 Sale Staff) – 10% growth in revenue with establishment(You can use other settings, but the interpretation MUST be based on scenarios of your setting)NOTE:The power, interpretation and analysis of your results /findings are very must depends on the right types ofgraphs, charts, tables, what-if-analysis (goal seek, data tables, scenarios), and PIVOT table created.ASSESSMENT TASK 2: CRITERIA SHEET Modelling Project: grading criteria (30% of assessment)Level of achievement CriteriaExcellent80-100%Good65 to 79%Satisfactory50 to 64%Unsatisfactory0 to 49%GradeDecision Model Building, Application and Analysis (75%)Model BuildingDemonstrate understanding of modellingprinciples and modelling process.Correct use of black box model andinfluence diagram.(SLO K1; CLO K1)Weight 15%Excellent application of modelling process (e.g., wellstructured spreadsheet with meaningful naming,modularise and parameterize with multiplespreadsheet, design for communication. Accurate useof black box model and influence diagram.Good application of modelling process (e.g.,evidence of well-structured spreadsheet withmeaningful naming, modularise and parameterizewith multiple spreadsheet, design forcommunication. Accurate use of black box modeland influence diagram with some errors /incomplete variables.Evident of application of modelling process(e.g., some evidence of well-structuredspreadsheet. Evidence use of black box modeland influence diagram with some errors /incomplete variables.Poor application of modelling process(e.g., no evidence of well-structured.No black box model and influencediagram.ApplicationsApplication of Excel skills to analyserelevant data/information(SLO S1; CLO A1)Weight 30%Demonstrates excellent competence in selection andapplication of correct Excel tools (e.g., graph/chart,what-if-analysis, pivot table) techniques to analysedata. Correct variables selected to address businessproblem. Tables, graphs and information are accurate.Demonstrates sound competence in selection andapplication of appropriate Excel tools (e.g.,graph/chart, what-if-analysis, pivot table) /techniques to analyse data. Some errors in variableselection to address business problem. Someimprovements are required. Minor inconsistencyand inaccuracy in information.Demonstrates some competence in selectionand application of appropriate Excel tools /techniques to analyse data. Some errors invariable selection to address businessproblem. Major improvements are required.Substantial errors and inconsistency ininformation.Demonstrates little or no competencein selection and application ofappropriate Excel tools / techniques.Errors in variable selection to addressbusiness problem. Significantimprovements are required. Majorerrors in informationAnalysisInterpret numerical data and drawconclusions (SLO A1; CLO K2, S1)Weight 30%Findings have been documented, interpreted in theappropriate spreadsheet. Clear connections are madebetween results, theory/concepts and businessproblem.Expected / unexpected findings were summarised.Recommendations and conclusions are clear and withlogical connection between results, business problem.Implications of the results are discussed.As a minimum, evidence of findings aredocumented and interpreted in the appropriatespreadsheet. Some connections to theory / conceptare made but misses one or two importantexplanations.Findings are summarised. Recommendations areevident and conclusions are drawn with someconnection between results, business problem.Some implications are discussed.As a minimum, evidence of findings aredocumented. Major improvements are needed.Some connections to theory / concept aremade but misses one or two importantexplanations.Findings are summarised. Basic connectionbetween results, business problem. Noimplication is discussed.Incomplete and/ or incorrectinterpretation of findings. Indicationof lack of understanding of results andconnection to underlying theory/keyconcepts.No findings and recommendations. Noconnection between the resultsbusiness problem.Reporting and Recommendations (25%)Content DevelopmentLogical flow, critical analysis, clear &relevant outcomes; concise and cohesiveinclude executive summary.(CLO S3)Weight: 10%Demonstrates excellent ability to develop, organisecontent in a logical, concise and cohesive mannerwith sound critical analysis, and outcomes supportedwith clear evidence from the modelling results. Theexecutive summary flows concisely from the report.Demonstrates sound ability to develop, organisecontent in a logical, concise and cohesive mannerwith basic critical analysis, and outcomessupported with some evidence from the modellingresults. The executive summary flows from thereport.Demonstrates basic ability to develop,organise content in a logical, concise andcohesive manner with basic critical analysis,and outcomes supported with some evidencefrom modelling results. The executivesummary has some relevant links to thereport.Demonstrate little or no ability todevelop, organise content in a logical,concise and cohesive manner. Lackscritical analysis and/or outcomessupported with no evidence. Theexecutive summary, if included, hasfew relevant links to the report.Conclusions and RecommendationConcise and logical link to the report;recommendation flows from theconclusions & critical issue addingstakeholder value(CLO S3)Weight: 10%Conclusions are concise and logically linked to thebody of the report. The outcome in the form of arecommendation flows from the conclusion/ criticalissue adding excellent stakeholder value.Conclusions provide good links to the body of thereport. The outcome in the form of arecommendation is linked to the conclusion/criticalissue adding good stakeholder value.Conclusions provide basic links to the body ofthe report. The outcome in the form of arecommendation is loosely link to theconclusion/critical issue adding limitedstakeholder value.Conclusions provide little or no linksto the body of the report. The outcomein the form of a recommendation is notlinked to the conclusion/ critical issueand no added stakeholder value isevident.Control of Syntax and MechanicsSentence construction; grammar;punctuation & spelling.(CLO S3)Weight: 5%Uses appropriate language that conveys meaning toreaders with clarity and no errors.Uses appropriate language that conveys meaning toreaders with clarity and minimal errors.Uses appropriate language that generallyconveys meaning to readers, although writingmay include a few errors.Language used is not clear and /ordoes not convey meaning to readers. Itcontains frequent grammatical errors.TOTAL/100%


Leave a Reply

Your email address will not be published. Required fields are marked *