IntelliAuto is an automobile parts manufacturer with 5000 employees. Specifically, the two automobile parts, namely XK-1010 and XP-1020, have evolved to be the top products in the marketplace. XK-1010 is marketed as a smart buy for middle-class customers withtight budget and it generates profit of $3500 for IntelliAuto. On the other hand, XP-1020 is marketed as a privilege of affluence,targeting upper-middle class customers and it generates a healthy profit of $5500 for the organisation.
Assama Gupta, the head of manufacturing department at IntelliAuto, is currently deciding the production schedule for the upcomingmonth. To meet one of the department’s key performance indicators (KPIs), Assama needs to decide how many XK-1010 and XP-1020 to produce so that the profit of IntelliAuto could be maximised.
Considering the resources, he understands the department has a capacity of 50,000 labour hours during the upcoming month. Inaddition, he also learns that it takes about 6 labour hours to complete a XK-1010 and 8.5 labour hours to complete a XP-1020. Thereare certain materials required to manufacture the automobile parts. Specifically, aluminium is the key material to produce both XK-1010 and XP-1020. The aluminium supplier has informed Assama that he can only receive a maximum of 25000 tonnes for theupcoming month as the supplier could not meet its production schedule for next month. This was due to the identification of severalpositive COVID-19 cases among the production staffat the supplier and therefore a forced shutdown for two weeks. Aluminiumrequirement to complete a XK-1010 is 3 tonnes and aluminium requirement to complete a XP-1020 is 2 tonnes.
As far as he knows, the demand for a variety of automobile parts has gradually increased over the past few months. Not until recentlythat Assama has been provided a forecast of the monthly demands for different automobile parts. It has been suggested from theforecast report that the demand for XP-1020 has increased from 2500 to its maximum, which is 3800 units. However, there is nolimitation for the demand of XK-1010.
Assama needs to determine the following issues
A. How many XK-1010 and XP-1020 should be produced to achieve the optimal profit and how much is the optimal profit? Assamaneeds to know how much labour hours and aluminium used and/or unused.
B. The marketing department at IntelliAuto has informed Assama that a targeted $400,000 advertising campaign will possibly raisethe demand for XP-1020 from 3800 to 4200. Together with the head of marketing department, Assama would like to know if themarketing campaign should be pursued and if it should, they would like to know how much additional profit could be generated.
C. IntelliAuto production can produce two other automobile parts called XL-1060 and XF-1090. XL-1060 consumes 7 units of labourand 10 units of aluminium and generates a profit of $4800. XF-1090 consumes 6.9 units of labour and 2 units of aluminium andgenerates a profit of $3900. Assama wants to know if it is profitable to produce XL-1060 and XF-1090 in addition to XK-1010 andXP-1020?
As a forward thinker, Assama has convinced the executive team on his plan to investigate and seek advice from a consulting firm tosolve his issues. You have been engaged as a Business Analytics consultant to produce a report which includesthe recommendation(s) to Assama.
Some guidelines for completing Part A – Case Study are provided below.
Part 1 – Prescriptive Analytics
To complete this assessment task, you are expected to demonstrate the use of Excel Solver to solve the problem(s) described in thecase study. Besides the discussion on the business problem(s) and objective(s), you need to present the problem formulation in yourreport and clearly explain the mathematical model for every question. To demonstrate your problem solving process, you could2022/5/31 06:52 Assessment 3: Prescriptive Analytics and Refl ective Journal 30% https://rmit.instructure.com/courses/94023/assignments/685077 3/6
discuss how you have implemented the prescriptive model(s) in Excel. Discuss your solution(s) and/or sensitivity analysis result(s) ofyour prescriptive model(s) before you suggest any actions to the business client under recommendations.
Part 2 – Report
Word Limit: 1200 words (+/- 10%), excluding Table of Content, Appendix, References, figures and tables. Note that anything beyondword limit will not be marked.
Font Size: 11pt or 12pt
Font Style: Calibri or Times New Roman
Document Margins: 2.5cm for top, bottom, left and right
Spacing: Single or 1.5 spacing
Some notes and guidelines:
The report should include Introduction, Problem Formulation, Discussions, Recommendation(s) and Conclusion(s).
The report covers a wider audience, including management and business users as well as data analysts.
Use headings for each section and subsection (i.e. 1.0 Introduction, 1.1. Business Background, 1.2. Objectives, 2.0 ProblemFormulation, etc.).
Label the figures and tables in the report properly.
Produce a Table of Content.
Use Appendix as you see fit.
Use References as you see fit. See next section about referencing guidelines.
Part B – Reflective Journal
The reflective journal requires you to reflect on the development of your ideas and insights about lesson you have gained from thiscourse. You also need to consider how the lesson learned could be integrated in your current personal development and futureprofessional practice. As a brief guide, you may want to look at the following but not limited to:
Consider key concepts, theories, skills, insights, practices, exercises, mistakes and conversations with peers that may havebenefited your current personal development.
Consider how your course learning experiences (e.g. group work, peer learning, etc.) might enhance your future professionalpractice.
Consider what capabilities you have developed from this course that may provide you with an advantage in your future professionor career.