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.
1. Optimal manufacturing
You are a manager on a fully automated factory building rocket engines. Each rocket engine requires multiple parts: 1 unit of part A, 2 units of part B and 4 units of part C. The cost of materials for each part is following: $20 for part A, $15 for part B and $10 for part C. You sell your rocket engines for $300 each.
| Part | Quantity Required | Cost |
|---|---|---|
| A | 1 | $20 |
| B | 2 | $15 |
| C | 4 | $10 |
You have two machines working for you 24 hours per day, 7 days a week. These machines can do any parts; however, they are not equally good in everything. Machine 1 takes 3 hours to make part A, 2 hours to make part B, and only 30 minutes to make part C. Machine 2 takes 2 hours to make part A, 1 hour to make part B, and 1 hour to make part C.
| A | B | C | |
|---|---|---|---|
| Machine 1 | 3h | 2h | 0.5h |
| Machine 2 | 2h | 1h | 1h |
Question
As any manager you want to maximise your profit. How would you do that and how high it can go?
2. Linear Programming for a caravan park management.
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: 160 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.
| Type | space(sq.m) | Max Count | Max Individuals per Type | Charge ($) |
|---|---|---|---|---|
| Caravans | 160 | 6 | 4 | 30 |
| Tents | 90 | null | 3 | 15 |
| Attributes | Value |
|---|---|
| Total Area (sq.m) | 1800 |
| Park Max People Limit | 48 |
You charge clients per night and prices are $30 for caravan and $15 for tent.
Question
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.
- Use Sensitivity Report from your optimal solution to consider the following scenarios (each one independently) and their effects on your solution and caravan park management:
- You got greedy and decided to increase price for caravan by 100%.
- You got very greedy and decided to increase price for tent by 100%.
- Government relaxed restrictions for the number of caravans and allowed to have 50% more caravans.
- Government implemented COVID-restrictions and did not allow more than 30 people in the caravan park.
3. Go bananas with optimisation
You are a fresh produce manager in the supermarket. Use historical data provided in the Excel-file “Q3_Bananas.xlsx” to choose optimal solutions for buying bananas for the supermarket for the two scenarios below:
- You are a greedy manager, and you want to earn as much money as possible.
- You are an environmentally minded manager, and you want to reduce the bananas wastage less than 1% per year of your total amount of bought bananas.
The Excel file has three columns:
- Date of sales. It is not important for this problem.
- Demand for bananas. This is a random variable, and you don’t know it when you make your decision about buying banana stock for your supermarket. Later you use it to calculate your profit or loss. For example, if demand is 400 kg, while your stock is 300 kg only, then you are able to sell all bananas, and part of the demand is not satisfied; however, if stock is 500 kg, then you sell 400 kg, the full demand is satisfied, but 100 kg of bananas are wasted. We assume, that bananas cannot be stored and should be sold on the next day. Unsold bananas go to waste.
- Price is a selling price of bananas in the supermarket on a given day. The supermarket does not set their own prices. The supermarket follows the wholesale market and sells bananas with a fixed 25% margin. That, if the listed selling price for today is $1.25 then the cost of buying bananas on that day is $1, and the supermarket profit is 25 cents ($0.25) per one kilogram of bananas. Beware, cost is always 25% less than selling price.
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 are interested in a “flexible” solution where the quantity you buy varies from day to day and depends on the price.
4. Optimisation for a life of crime
Let’s assume that you decide to earn money by tickets scalping, that is, buying tickets for some events and then selling them at higher price.
Disclaimer
Ticket scalping is illegal; don’t try that at home; this is an optimisation exercise only.
You can buy tickets at official price of $150. Then you try sell them at any price you want. However, if your price is too high then there will be less people willing to buy tickets from you. Also, the number of buyers is a random variable, so you cannot be sure how many tickets you will be able to sell.
The number of tickets you can sell (demand) follows lognormal distribution with mean µ and standard deviation = 0.5.
Note
The number of tickets sold will be not an integer, which does not make sense. It is OK for calculations as we talk about expectations, but it is not OK for reporting. Use common sense.
Value µ depends on your selected price and follows the following formula: µ=6-YourPrice/70
You can return all tickets you don’t sell, but you will get only one third of their official price.
Question
- How many tickets should you buy and what should be your selling price?
- What is a probability to make a profit in the case of the optimal solution?
- Is it worth to “do business”?
Hint
To get random numbers from some distribution in Excel, you need to use inverse functions. For example, for lognormal distribution the formula is =LOGNORM.INV(RAND(), mean, st.dev)
Submission
Write and submit a report with solutions for all problems above. You should use not more than 3 pages per problem. No micro-fonts, mini-image, nano-tables. It should be easy to read the document and to see all details.
Your report should be brief but have all attributes of a proper business report. As there are four independent and absolutely disconnected problems, you don’t need one global introduction and then a conclusion. You will have four separate mini reports, each report will have its own introduction, problem formulation, discussion and conclusion.
All problems are equally weighted. That is, every problem is worth 25 points. You should provide correct results and correct interpretations.
You must submit your report in MS Word or PDF format and Excel workbook including all your Excel spread sheets. 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. It is OK to submit separate Excel file for each question.
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.
Important note
As this is the final piece of assessment, there is no provision for late submission. It is like an exam - if you are late, then the system closes, and you get zero for the assessment.
Please don’t send me anything by email. I cannot accept any submission by email. All submissions should go through LearnOnline.
Start early – finish early, be safe.
Report Requirements From Tim
Report should include:
- Introduction, where you introduce the business problem overall and method you plan to use.
Please skip generic blah-blah about "importance of optimisation for the world peace". You have a very particular problem, you have to provide a solution for this problem. - Proper formulation of the optimisation problem.
- Reporting results with meaningful interpretations.
- Brief conclusion to summarise three sections above.
Again, skip irrelevant discussions about optimisation in general. Be focused on your problem. Conclusion is a mix of introduction and results. You cannot have a conclusion without reporting actual numbers. If there are no numbers - there is no conclusion.