Homework answers / question archive / 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

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

MS Excel

Share With

---

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:

• there are 122 days from June to September
• Neil currently has 5 combine harvesters for hire
• Each ‘hirer’ (farmer) must order a machine for a particular start day and collect, if available, at 10am
• Each hirer must return the machine before 9am on the third day (i.e. a 2-day hire)
• If no machine is available to hire then the customer’s order cannot be satisfied
• Orders fluctuate considerably from day to day, as the weather plays a large part in whether the grass is dry enough to be cut

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 (Y%) 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%.

Modelling Tasks c

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 season² 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 rate!’ (%) 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 a//the orders that day could not be satisfied?

vii. What is the average utilisation rate* 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 additiona/ 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 /to vif 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 /, //, ii, 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].

Deliverable

¥ Upload to moodle an Excel file that operates successfully using Excel 2019. Deadline is 3™¢ May

v The model will be assessed on a University-specification PC, running Excel 2019°

¥ 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

> the 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%

*the average % of machines hired out. Again, present your percentage to 1 decimal place.

> goto https://mylab.lancaster.ac.uk/ if you need to test your model on a virtual PC running Excel 2019