Reference no: EM133162270

Assessment details

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 with tight 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 upcoming month. 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. In addition, 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. There are 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 the upcoming month as the supplier could not meet its production schedule for next month. This was due to the identification of several positive COVID-19 cases among the production staff at the supplier and therefore a forced shutdown for two weeks. Aluminium requirement 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 recently that Assama has been provided a forecast of the monthly demands for different automobile parts. It has been suggested from the forecast report that the demand for XP-1020 has increased from 2500 to its maximum, which is 3800 units. However, there is no limitation 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? Assama needs 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 raise the demand for XP-1020 from 3800 to 4200. Together with the head of marketing department, Assama would like to know if the marketing 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 labour and 10 units of aluminium and generates a profit of $4800. XF-1090 consumes 6.9 units of labour and 2 units of aluminium and generates a profit of $3900. Assama wants to know if it is profitable to produce XL-1060 and XF-1090 in addition to XK-1010 and XP-1020?

As a forward thinker, Assama has convinced the executive team on his plan to investigate and seek advice from a consulting firm to solve his issues. You have been engaged as a Business Analytics consultant to produce a report which includes the recommendation(s) to Assama.

Part A - Case Study
You are required to examine a case study, formulate and produce analytical model(s), interpret, draw conclusion(s) and provide recommendation(s) based on your analysis via a report to a business client. The case study is provided in Assessment Details.

This assessment relates to the professional practice of business and/or data analysts, who perform prescriptive modelling technique(s) in response to requests from a business client or internal stakeholder. To complete this assessment task, you are required to demonstrate the ability to apply the prescriptive analytics techniques which you have learned so far in this course. Also, the purpose of this assessment is to demonstrate your ability to communicate the outputs of prescriptive analytics and deliver a report that can be used by others to make responsive and evidence-based business decisions.

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 the case study. Besides the discussion on the business problem(s) and objective(s), you need to present the problem formulation in your report and clearly explain the mathematical model for every question. To demonstrate your problem solving process, you could discuss how you have implemented the prescriptive model(s) in Excel. Discuss your solution(s) and/or sensitivity analysis result(s) of your 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 beyond word limit will not be marked.
Font Size: 11 pt 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 Problem Formulation, 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.