Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

INFS 5096 Customer Analytics in Large Organisations

Assignment 2 part 2 – Optimisation

This is the second part of the Assignment 2. It is worth 30 points of your final grade. Your task is to formulate the problem, prepare the data, run an analysis, answer research questions, and write a brief report with your findings. Be sure that your results and interpretations are meaningful for the business environment assumed in the problems.

1. Linear Programming for a caravan park management (45 marks)

In this case study you work as a manager of a small caravan park. The total area of the caravan park is 1800 sq.m. Your caravan park accepts caravans and tents. For a safety reasons, there are standards for allocated spaces: 200 sq.m. per caravan and 90 sq.m. per tent. Also, you are not allowed to have more than 6 caravans in your caravan park. There are also sanitary restrictions stating that there can be 4 people in the caravan and only 3 people in the tent. Total caravan park occupancy should not exceed 48 people.

You charge clients per night and prices are $30 for caravan and $15 for tent. What is the highest possible profit you can get per night? Use Excel Solver to solve this problem. From your Answer Report, describe the optimal solution.

What changes you need to implement to your solution and what would be the maximal profit per night if you decide to have: (a) as many caravans as possible; or (b) as many tents as possible?

Use Sensitivity Report from your original (optimal solution) to consider the following scenarios (each one independently) and their effects on your solution and caravan park management:

1. You decide to increase price for tent by 50%.

2. You plan to buy an adjacent block of land of 100 sq.m. and add it to the caravan park.

3. Government implements COVID-restrictions and does not allow more than 24 people in the caravan park.

2. Go bananas with optimisation (30 marks)

You are a fresh produce manager in the supermarket. Use historical data provided in the Excel-file “Q2_Bananas.xlsx” to choose optimal solutions for buying bananas for the supermarket for the two scenarios below:

1. You are a greedy manager, and you want to earn as much money as possible.

2. You are an environmentally minded manager, and you want to reduce the bananas wastage to 1% per year.

The Excel file has three columns:

1. Date of sales.

2. Quantity, which you are going to treat as a potential demand for bananas. So, if demand is 400 kg, while your stock is 300 kg only, then only 75% the demand is satisfied; if stock is 500 kg, then full demand is satisfied but 100 kg of bananas are wasted. We assume, that bananas cannot be stored and sold on the next day. Unsold bananas go to waste.

3. Price is a selling price of bananas on a given day. The supermarket sells bananas with 20% margin. So, if the listed selling price is $1.20 then your cost of buying bananas on that day is $1 and your profit is 20 cents ($0.2) per one kilogram of bananas.

As you know, demand has a negative relationship with the price – if price goes up, demand goes down, people don’t buy that much if the price is too high. Hence, as a smart manager, you might be interested in a “flexible” solution where that depends on the price.

3. Being Elon Mask optimisation (25 marks)

Let’s assume that you are Elon Mask, and you plan to introduce two new models of cars: small sedan Tesla A and family sedan Tesla W. Suggested retail price for Tesla A is $33,900 and for Tesla W is $39,900. Manufacturing cost for these cars is $19,500 for Tesla A and $24,500 for Tesla W, plus additional fixed cost of $40,000,000 per year.

In the competitive market the number of sales will affect the sales price. It is estimated that for each model, the sales price drops by one dollar for each additional car sold. Also, sales in one model will affect sales of another model. It is estimated that the price of Tesla A will go down by additional 50 cents for each Tesla W sold; and price of Tesla W price will go down by 60 cents for each Tesla A sold.

Demand for cars is subject to some randomness and it is expected to follow a binomial distribution. Use the following formulas in Excel to get demand numbers:

· Tesla A: =BINOM.INV(10000,0.3,RAND())

· Tesla W: =BINOM.INV(10000,0.5,RAND())

Overall factory capacity is limited by 8,000 car per year. As, you can see from Excel formulas, market capacity is expected to be 8,000 cars too. Please estimate what is the optimal numbers of cars for Tesla A and Tesla W production?

Submission

Write and submit a report that answers all the questions above.  The guideline in the Course Outline is 1500 words.  Those words should be arranged into sentences and paragraphs that explain and justify the answers, although there are also a few algebraic expressions to be reported, particularly in the linear programming part.

Your report should be brief but have all attributes of a proper business report. As there are three independent and absolutely disconnected problems, you don’t need one global introduction and then a conclusion. You will have three separate mini-reports, each report will have its own introduction, problem formulation, discussion and conclusion.

You must submit: (1) your report in MS Word or PDF format, and (2) three Excel files – one file per the problem. Each Excel file includes all your Excel spread sheets relevant to the problem. Submit only meaningful spread sheets – don’t include your tests and “work in progress”. Take care about meaningful names for all spread sheets. It should be easy for me to see what is what.

If you have any questions – feel free to ask on the forum. You can discuss this exercise with me and other students. You are encouraged to share ideas but not solutions. Remember about academic integrity.