Question 2-Linear Programming (15 marks) The junior marketing manager would like you to assist in providing information for a marketing strategy she is proposing at the annual general meeting next month. She is focusing only on a new innovative solver-based paint that can be washed off using water instead of mineral turpentine. The marketing manager wants to know the optimal product mix that should be offered. Below is an estimation of the costs of the five sizes that the paint can be purchased, only two of the five sizes can be manufactured due to constraints of the packaging machine which are unrelated to constraints in the below table. Paint Tin Sizes 500ml 1 litre 4 litres 10 litres 15 litres Selling Price $17.00 $25.00 590.00 $180.00 $270.00 $2.00 $5.00 $54.00 $130.00 $200.00 Costs per tin: Raw materials Direct labour Variable overhead Fixed overhead $4.00 $6.00 $14.00 $16.00 $20.00 $2.00 $2.00 $8.00 $20.00 $36.00 $3.00 $6.00 $8.00 $10.00 $15.00 Each of the five products goes through the same manufacturing process which consists of weighing raw materials, dispersion, pre-mixing, pigment grinding and stability inspection. The time for each batch is in the table below. Collecting Mixing Colour Packaging and weighing pigment added RM Hours required per batch 500ml 1 1 0.25 1 litre 4 litres 2 0.5 4 1 2 1 2 2 0.5 3 3 2 10 litres 3 4 1 1 15 litres 3 5 1 1 Hours Availalbe 1,200 600 400 1,200 Required: 1. Formulate a linear program to determine the expected mix of paint tins, ensuring that the maximum profit is obtained. Discuss the key features of your results taking into consideration the practical limitations of using Solver. (8 marks) 2. Discuss two possible ways all 5 sizes could be manufactured. Provide financial information to support your recommendations and clearly illustrate how your proposals impact the production mix and the firm's contribution margin. (7 marks) Question 3 Simulation (15 marks) Six months ago, Picasso Paints rented an additional shop in the main street of Wodonga to sell paint specifically to DIY customers, whilst trader customer purchase out of the manufacturing plant. Picasso Paints has been unable to adequately hold inventory to satisfy the customer demand. Anna Sasin, who is the store manager has come up with the following probability distribution demand to try and understand customer behaviour and therefore be able to order enough paint. Demand per week Probability Lead time (days) Probability 0 0.05 1 0.50 10 0.20 2 0.45 20 0.40 3 0.05 30 0.20 40 0.15 The shop does not have adequate storage to keep a plentiful supply and rents additional space at the back of the shop at a cost of $1 per tin. The stock out cost is $40 per tin. Anna Sasin usually places and order when stock is down to 20 tins. Required: 1. Current there are 10 tins in inventory. Simulate a 20 week scenario for Anna Sasin. Calculate the weekly stock out and holding cost. (10 marks) 2. In a separate table repeat the simulation assuming the reorder point is 10 tins. (2 marks) Discuss the results (3 marks) Note: The Excel table must include random numbers and Vlookup functions. The Excel spreadsheet is to be submitted with the Word document. Question 4 Strategic Management (Reviewed) (15 marks) The management team would like your opinion on what strategic position Picasso Paints should now follow taking into consideration Case Study Part 1 and Case Study part 2 results. In your response reflect on short-term and long-term strategies the business may adopt. Include in your answer at least 2 options explaining how the strategy would be implemented and the likely outcome. (Maximum 500 words) Question 2-Linear Programming (15 marks) The junior marketing manager would like you to assist in providing information for a marketing strategy she is proposing at the annual general meeting next month. She is focusing only on a new innovative solver-based paint that can be washed off using water instead of mineral turpentine. The marketing manager wants to know the optimal product mix that should be offered. Below is an estimation of the costs of the five sizes that the paint can be purchased, only two of the five sizes can be manufactured due to constraints of the packaging machine which are unrelated to constraints in the below table. Paint Tin Sizes 500ml 1 litre 4 litres 10 litres 15 litres Selling Price $17.00 $25.00 590.00 $180.00 $270.00 $2.00 $5.00 $54.00 $130.00 $200.00 Costs per tin: Raw materials Direct labour Variable overhead Fixed overhead $4.00 $6.00 $14.00 $16.00 $20.00 $2.00 $2.00 $8.00 $20.00 $36.00 $3.00 $6.00 $8.00 $10.00 $15.00 Each of the five products goes through the same manufacturing process which consists of weighing raw materials, dispersion, pre-mixing, pigment grinding and stability inspection. The time for each batch is in the table below. Collecting Mixing Colour Packaging and weighing pigment added RM Hours required per batch 500ml 1 1 0.25 1 litre 4 litres 2 0.5 4 1 2 1 2 2 0.5 3 3 2 10 litres 3 4 1 1 15 litres 3 5 1 1 Hours Availalbe 1,200 600 400 1,200 Required: 1. Formulate a linear program to determine the expected mix of paint tins, ensuring that the maximum profit is obtained. Discuss the key features of your results taking into consideration the practical limitations of using Solver. (8 marks) 2. Discuss two possible ways all 5 sizes could be manufactured. Provide financial information to support your recommendations and clearly illustrate how your proposals impact the production mix and the firm's contribution margin. (7 marks) Question 3 Simulation (15 marks) Six months ago, Picasso Paints rented an additional shop in the main street of Wodonga to sell paint specifically to DIY customers, whilst trader customer purchase out of the manufacturing plant. Picasso Paints has been unable to adequately hold inventory to satisfy the customer demand. Anna Sasin, who is the store manager has come up with the following probability distribution demand to try and understand customer behaviour and therefore be able to order enough paint. Demand per week Probability Lead time (days) Probability 0 0.05 1 0.50 10 0.20 2 0.45 20 0.40 3 0.05 30 0.20 40 0.15 The shop does not have adequate storage to keep a plentiful supply and rents additional space at the back of the shop at a cost of $1 per tin. The stock out cost is $40 per tin. Anna Sasin usually places and order when stock is down to 20 tins. Required: 1. Current there are 10 tins in inventory. Simulate a 20 week scenario for Anna Sasin. Calculate the weekly stock out and holding cost. (10 marks) 2. In a separate table repeat the simulation assuming the reorder point is 10 tins. (2 marks) Discuss the results (3 marks) Note: The Excel table must include random numbers and Vlookup functions. The Excel spreadsheet is to be submitted with the Word document. Question 4 Strategic Management (Reviewed) (15 marks) The management team would like your opinion on what strategic position Picasso Paints should now follow taking into consideration Case Study Part 1 and Case Study part 2 results. In your response reflect on short-term and long-term strategies the business may adopt. Include in your answer at least 2 options explaining how the strategy would be implemented and the likely outcome. (Maximum 500 words)