+4474648-84564 BUSN9088 Business Analytics by HND Assignment Help
Assessment 2 in BUSN9088 Business Analytics by HND Assignment Help
| Instructions This assessment consists of two independent problems (see below). For each problem, you need to: Develop a spreadsheet modelDescribe your results in a concise report For each problem, your report should contain a short description of the model solutions, answers to specific questions and your interpretations of the results. The main text of the report should not exceed 1500-2000 words (about four pages total of A4, 11 point, 1.5 spacing, approximately two page per problem). It may well be useful to include appendices with tables, graphs, etc. These must be explained in the main text. The report should be clearly structured, communicate the outcomes of your analysis in an effective way and avoid any technical jargon. You will need to submit an electronic copy of the report along with your Excel file on Moodle by 12:00 (noon) on Wednesday February 25, 2022. N.B. The two problems are deliberately open-ended. You may develop your analysis however you see fit. Initiative (e.g. additional sensitivity analysis, problem insights) will be rewarded. |
Problem 1 – A Nurse Scheduling problem [50 marks]
You have been hired as a business analyst to organize the daily shift of the nurses at Bull Run Hospital. Based on historical data, the hospital has a daily nurse requirement ranging from 120 to 190 nurses, as shown in the table below.
| Day | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
| Staff Need | 180 | 160 | 150 | 160 | 190 | 140 | 120 |
In addition, a labor union requirement must be met that nurses work a five-consecutive-day workweek followed by two days off. Thus, the allowable shifts are Monday through Friday, Tuesday through Saturday, Wednesday through Sunday, and so on. The allowable shifts are displayed in the table below.
BUSN9088 Business Analytics by HND Assignment Help
Each nurse working Monday to Friday (shift 1) earns £500 per week. Nurses working on a shift which includes either Saturday or Sunday (shift 2 and shift 7) earn £600 per week, whereas nurses who work on a shift which include the full weekend (shifts 3-6) earn £700 per week.
- Build and solve an optimisation model for Bull Run Hospital to identify the nurse schedule that meets the daily nurse requirements at minimum cost. How many nurses are needed overall? What is the weekly cost of the schedule?
- Historical data show that nurse requirement during the weekend can fluctuate quite significantly and there are worst case scenarios where the nurse requirement can be as high as 150 and 130 on Saturday and Sunday respectively (rather than 140 and 120). The Bull Run Hospital management would like you to investigate the impact on the optimal schedule and cost of meeting this worst case requirement.
- Bull Run Hospital management is considering increasing the salary of the nurses working on both Saturday and Sunday from £700 to £900 and asked you to identify an optimal schedule with the revised costs (use the initial staff requirement to answer this question). Would you suggest that Bull Run Hospital uses the solution provided by the model if they go ahead with the salary increase? Should they consider other solutions?
Summarise your findings to questions a)-c) in a concise report. The report should include explicit references to the relevant sheets on your Excel file or to figures (e.g., Excel screenshots) in the appendix.
Marking scheme for Problem 1:
Excel model layout and functionality [20] Report structure and clarity [10]
Analysis and insights [20]
Problem 2 – Snowfit Corporation Decision Problem [50 marks]
Snowfit Corporation is a leading manufacturer cum retailer for ski equipment based out of Chicago, USA. Their product portfolio includes skis, boots, poles and bindings. Recently they have come up with a prototype for a new snowboard product, the Glacier Racer. However, from past experience, the management team knows that demand fluctuations heavily impact sales and revenues of their products. Snow products are seasonal items that face demand fluctuation and there is a risk of either over or under producing. Hence, Snowfit wants to do a thorough analysis of the profit potential of this new product before they launch it next winter.
Snowfit is looking at several production options for Glacier Racer. They consider producing either 15000 or 40000 units. Snowfit has one major production facility in the outskirts of Chicago that has the capability to employ either batch or line manufacturing process. The costs associated with each process are provided below:
| Batch | Line | |
| Fixed Cost ($) | 475000 | 900000 |
| Variable Cost ($/unit) | 75 | 60 |
The marketing department has come up with a sales forecast based on two scenarios: if there is heavy snowfall then they would sell 35000 units of Glacier Racer and if there is light snowfall then they would sell 25000 units. They have also estimated that there is a 60% chance of getting heavy snow and 40% chance of getting light snow next winter. The selling price for the Glacier Racer will be $125. Another factor, Snowfit has to keep in mind is that they can employ an outside vendor, GoSki for manufacturing Glacier Racer according to their specifications at a cost of $75 each. However, there is a contract with the labour union which says that an outside vendor can be used only if demand exceeded production. Unsold units of the product at the end of the season can be salvaged at a clearance sale for $50 per unit.
BUSN9088 Business Analytics by HND Assignment Help
Furthermore, Snowfit can also arrange for a detailed survey to estimate the extent of snowfall next winter. The cost for doing such a survey is $20000. Based on past data, it can be concluded that 64% of time the survey will say heavy snowfall will take place and 36% of time they will say light snowfall will occur. Snowfit estimates, there is a 0.844 probability that heavy snowfall will actually happen given the survey also says so and there is only a probability of 0.167 of getting heavy snow if the survey results say light snowfall.
Prepare a decision tree using TreePlan which would help Snowfit analyze the above problem. Your report should contain answers to the following questions:
1. What is the break-even production quantity between batch flow and line flow production?
2. Should Snowfit do a detailed survey based on the data provided above?
3. What would be the optimal set of decisions for Snowfit with regard to Glacier Racer?
4. What is the EMV (Expected Monetary Value) for the optimal set of decisions?
5. Perform a sensitivity analysis with respect to the probability of the survey predicting heavy snow
[Hint: How does the decision of “doing” or “not doing” the survey change with the probability of the survey predicting heavy snow?]Marking scheme for Problem 2:
Excel model layout and functionality [20] Report structure and clarity [10]
