question archive Coursework 2021 MSCI 342: Advanced Spreadsheet Modelling Witt’s Agriculture Since 1982 Neil Witt has built up a thriving business in Garstang and the surrounding area hiring out expensive agricultural machinery to local farmers
Subject:MS ExcelPrice:25.99 Bought3
Coursework 2021 MSCI 342: Advanced Spreadsheet Modelling
Since 1982 Neil Witt has built up a thriving business in Garstang and the surrounding area hiring out expensive agricultural machinery to local farmers. The machinery includes combine harvesters, tractors, trailers, and digging equipment. The local farmers prefer to hire this machinery rather than buy it themselves because it is very expensive to buy, and is often only required for short periods during the year, for example during harvest time.
One piece of machinery that Neil is interested in modelling is his fleet of combine harvesters, of which he currently has five. A combine harvester is used to cut grass for silage during the summer season. Neil hires out the combine harvesters between June and September. The conditions for the hiring are as follows:
The logic Neil uses to model his situation is:
no. of machines available on day x = no. of machines available on day x-1
- no. of machines hired on day x-1
+ no. of machines returned on day x
Data is available for last year’s season. However, Neil has not really embraced spreadsheets and so the data is just simple numbers on a worksheet. No formulas.
In particular, Neil is concerned that the seasonal satisfaction rate' is too low and is considering purchasing or leasing more machines to improve performance, and increase revenue naturally.
Ideally he wants a satisfaction rate around 90% or above. However, on the other hand, he doesn’t want the extra harvesters to be sitting around unused.
To answer this question Neil has asked you to build a model of the harvest season, to better understand the performance of his fleet, and its sensitivity to demand fluctuations.
The satisfaction rate (%) for a season is the total number of machines hired + total number of orders, expressed as a percentage to 1 decimal place, e.g. 90.2%.
Coursework 2021 MSCI 342: Advanced Spreadsheet Modelling
1. Design an interactive, flexible model which enables Neil to investigate the variability of orders and investigate the various options open to him.
Include a ‘user’ sheet for Neil which clearly and simply presents all the answers and analysis to the questions below.
High marks will go to models that are well-designed and structured, have clarity, flexibility and are simple and intuitive to use. Be clear about any key assumptions you make.
2. For last season2 present the following key performance indicators:
i, How many orders were satisfied during the season?
ii, Wow many orders were not satisfied?
iii. What was the satisfaction rate1 (%) for the season?
iv. How does the satisfaction rate change as the season progresses? Show this graphically.
v. How many days of the season had orders which could not be satisfied?
vi. How many days were there when all the orders that day could not be satisfied?
vii. What is the average utilisation rate4 for the season?
vill. What would the satisfaction rate have been if Neil had 6 harvesters for the whole season?
ix, What would the satisfaction rate have been if Neil had 7 harvesters for the whole season?
X. What does the orders distribution look like for each month?
3. The weather. Neil is also quite interested in the weather, as it clearly affects the number of orders; the hotter and drier it is, the quicker the grass grows, and thus more cutting will be done by the farmers. Neil feels that August tends to be the hottest and driest month and thus demand for machines in August is the highest. He is considering leasing two additional harvesters to satisfy this extra demand, for the whole of August. In other words, increasing the fleet to 7 harvesters.
Is there evidence to support Neil’s view on the hotter weather/increased activity in August? How does running two extra harvesters in August influence the key performance indicators? (Results i to vii above)
4. Variability of demand. Neil also feels that the variability of orders is a key factor to his ability to satisfy the customers. If demand is consistently high over consecutive days — either just by chance or due to a period of hot, dry weather — then it’s more likely that orders will not be satisfied.
Using last year’s orders as a basis, include the facility to simulate demand, and present and store the results for questions i, ii, iii, v, and vi, for a number of ‘simulated seasons’.
How bad can it get, in terms of satisfying the customers? [Assume 5 harvesters for this task].
Upload to moodle an Excel file that operates successfully using Excel 2019. Deadline is 3™¢ May
The model will be assessed on a University-specification PC, running Excel 20195
Only correct answers will receive full marks
x The model should not be password protected, or contain external links, or have any hidden sheets
x Any significant alterations to the data requires prior consent from the tutor
x Failure to comply with these rules may result in reduced marks
2The season is 122 days. Base all your results on this period.
3 where the satisfaction rate for any given day is the satisfaction rate up to that point in the season, i.e. up to and including that day of the season. Present your percentage to 1 decimal place, e.g. 65.8%
4the average % of machines hired out. Again, present your percentage to 1 decimal place.
5goto https://mylab.lancaster.ac.uk/ if you need to test your model on a virtual PC running Excel 2019
Purchased 3 times