Assignment 3: Economic Order Quantity (EOQ, and stochastic), EMV, Time Series,Simulation and Queuing, etc. - using Microsoft Excel Solver

This assignment is worth 25% of your final mark (subject to the hurdles described in the FIT3158 Moodle preview [or Unit Guide] and links therein). Among other things (see below), note the need to hit the `Submit’ button (and the possible requirement of a video and/or an interview).

Due Date: Tuesday 31st October 2023, 11:55 pm

Method of submission: Your submission should consist of 2 files:

1. A Microsoft Excel spreadsheet named as:

FamilyName-StudentId-2ndSem2023FIT3158Asst3.xlsx

2. A text-based .pdf file named as: FamilyName-StudentId-2ndSem2023FIT3158Asst3.pdf

Both the files must be uploaded on the FIT3158 Moodle site by the due date and time.

The text-based .pdf file will undergo a similarity check by Turnitin at the time you submit to Moodle. Please read submission instructions here and elsewhere carefully regarding the use of Moodle.

Total available marks: 100 marks.

Note 1: Please recall the Academic Integrity rules (and, as per Moodle week 0, you might also wish to confer with https://www.monash.edu/student-academic-success). This is an individual assignment. Recall various resources from FIT3158 Moodle week 0.In submitting this assignment, you acknowledge both that you are familiar with the relevant policies, rules and regulations regarding Academic Integrity (including, e.g., doing your own work, not sharing your work, not using ChatGPT in particular, not using generative AI at all) and also that you are familiar with the consequences of being deemed to be in contravention of these policies.

Note 2: And a reminder not to post even part of a proposed partial solution to a forum or other public location. This includes when you are seeking clarification of a question. If you seek clarification on an Assignment question then – bearing in mind the above – word your question very carefully and/or (if necessary) send private e-mail (being in mind instructions in week 1 Introductory Lecture). If you are seeking to understand a concept better, then try to word your question so that it is a long way removed from the Assignment. One of the easiest ways to do this is to ask a question about a class (seminar/lecture, Applied class / tute) exercise, and to post it in an Ed Discussion section which does not pertain to any currently open assessment items. You are reminded that Monash University takes academic integrity very seriously.

Note 3: As previously advised, it is your responsibility to be familiar with the special consideration policies and special consideration process – as well as academic integrity. Please see the relevant links within FIT3158 Moodle (week 0 and perhaps also elsewhere). Students should be familiar with the special consideration policies and the process for applying. Students should also understand that special consideration decisions are not made by the teaching team in this subject – and hence the need to follow the given process.

Note 4: As a general rule, don’t just give a number or an answer like `Yes’ or `No’ without at least some clear and sufficient explanation - or, otherwise, you risk being awarded 0 marks for the relevant exercise. Make it easy for the person/people marking your work to follow your reasoning. Your .pdf should typically cross-reference the corresponding answer in your spreadsheet. For each and every question, sub-question and exercise, provide a clearly labelled separate spreadsheet tab with clear content, accompanied with clearly cross-referenced clear .pdf explanation. Without clear cross-reference between .pdf and spreadsheet tab – and without a separate spreadsheet tab for each sub-question - there is the possibility that any such exercise will be awarded 0 marks. Re-iterating a point above, for each and every question, sub-question and exercise, clearly explain your answer and clearly show any working.

Note 5: As a general rule, if there is an elegant way of answering a question without unnecessarily re-running the Solver, try to do it that way. (Recall, e.g., sensitivity report and some notions from Week 3 and thereabouts.) More generally, more elegant solutions are preferable - and will at least sometimes be given more marks (possibly many more marks). Among other things, if a problem is a linear programming (LP) problem, then it would be more elegant to solve it using the linear simplex model. In a similar vein, a linking constraint (where appropriate) will be far preferable to a seemingly equivalent use of the IF() function.

Note 6: All of your submitted work should be in machine readable form, and none of your submitted work should be hand-written.

Note 7: If you wish for your work to be marked and not to accrue (possibly considerable) late penalties, then make sure to upload the correct files and (not to leave your files as Draft). You then need to determine whether you have all files uploaded and that you are ready to hit `Submit’. Once you hit `Submit’, you give consent for us to begin marking your work. If you hit `Submit’ without all files uploaded then you will probably be deemed not to have followed the instructions from the Notes above. If you leave your work as Draft and have not hit `Submit’ then we have not received it, and it can accrue late penalties once the deadline passes. In short, make sure to hit ‘Submit’ at the appropriate time to make sure that your work is submitted. Late penalties will be as per Monash University Faculty of IT and Monash University policies. Within the requirements of these policies just mentioned, late penalties will accrue at a rate of at least 5% per calendar day – where 5% refers to a proportion of the total marks available. It is expected that any work submitted at least 10 calendar days after the deadline will automatically be given a mark of 0.

Note 8: The notation 1E-12 corresponds to 1 x 10-12, or 0.000000000001. If you see a figure of approximately this magnitude or comparable magnitude, then consider whether or not it might be round-off error for something else.

Note 9: Save your file regularly. Most of the time, we expect that the Solver will run quickly. But for problems with many variables and many constraints – especially involving integers – please be mindful that if you are not careful to do some of the things mentioned in class to help your program finish more quickly, then there is a risk that your program might possibly go through at least tens or hundreds of thousands of subproblems and become very slow (as you wait and wait and …). If you save your file before starting a run that could be long and slow, then you can safely stop the program – if it becomes very slow – with reduced risk of losing your edit changes.

Note 10: As a general rule for solving a problem using MicroSoft Excel Solver, please consider carefully whether the various solver (settings or) Options (which you might be able to access after clicking on `Options’, which might be on the right about two-thirds of the way down after you click on `Solver’) might affect the results provided by the solver. (As an example, if dealing with integers then give some thought to `Integer Optimality’.) Put another way, rather than just use the default settings, make sure to check the solver settings and be willing to appropriately modify them if and as required.

We have at least one fictitious story (by way of motivation) which you can safely skip and bypass – and then proceed straight to the questions. Motivation could include examples given at the start of Assignment 1 and/or Assignment 2. Shortest path is an example of transhipment, which in turn is relevant to (e.g.) evacuation emergencies, etc.

The design of braille for vision-impaired people could possibly have been seen as an optimisation problem, whether or not as a linear programming (LP) problem.

Throughout this Assignment, recall all notes and instructions - including but not limited to note 6.

----

Qu 1 (10 + 5 + 2 + 7 + 1 = 25 marks)

Consider the following one of many fictitious examples. (Hint: This fictitious example concerns a variation on EOQ.)

Production occurs at a rate of 2,000,000 every three months, or every quarter. Consumption - or demand - occurs at a rate of 250,000 every three months, or every quarter.

There is a start-up cost of $2000, a selling cost (or value) of $10 and a quarterly (or three monthly) holding cost of 3%.

Plan 1 has 1 production cycle per year, with the cycle re-starting after 1 year.

Plan 2 has the production phase of the cycle going non-stop for 1 year before production stops.

Plan 3 has a production amount of 100,000 - so, in each cycle, production stops after 100,000 items are produced, and then production resumes after the inventory drops to 0.

1(a)

Which would be cheaper out of plan 1, plan 2 and plan 3?

Suppose we change the amount of production to the optimal amount.

How much would this reduce cost by when compared with your answer from Qu 1a)?

Show all working.

1(c)

Consider again Plan 1.

But suppose now that the standard deviation on the rate of demand is 5000 every quarter (or 5000 every season, or 5000 every 3 months). This is because of some very slight variations and fluctuations in demand.

Suppose we are willing to tolerate a stock-out probability of 2% for the end of the first year.

Should the duration of the production (the amount of time during the cycle that production occurs) decrease, stay the same or increase? And why?

1(d)

We continue on from part (c).

More specifically, how long should production be for to have the desired stock-out probability during the first year?

In other words, what should be the duration of production in order to have the desired stock out probability during the first year?

Show all working.

1(e)

Consider again Plan 1 with deterministic demand, so the standard deviation in the demand is 0.

But suppose now that the standard deviation on the rate of production is 5000 every quarter (or 5000 every season, or 5000 every 3 months). This is because of some very slight uncertainties in the production mechanism.

Suppose we are willing to tolerate a stock-out probability of 2% for the end of the first year.

Should the duration of the production (the amount of time during the cycle that production occurs) decrease, stay the same or increase? And why?

As always, throughout the question, show all working.

----

Qu 2 (13 + 4 + 4 + 4 = 25 marks)

This is another fictitious example. (Hint: It is in the general direction of EMV material. You will find at least one example in class material which has at least some vague similarities.)

We assume a population size of N. For the sake of argument, N = 5,000,000.

A medical condition exists which affects 2% of the population at any given time.

The cost of this condition is estimated to be $25,000 for any/each person who has it. Two tests exist for people with regard to this medical condition. They are called Test1 and Test2.

We will sometimes write +ve as an abbreviation for positive, and -ve as an abbreviation for negative.

If someone has the medical condition then the probability that Test1 gives a positive (+ve) result is 90%.

If someone does not have the medical condition then the probability that Test1 gives a positive (+ve) result is 10%.

We now use Bayes’s theorem to do the following calculations:

Pr(Condition and +ve on Test1) = 0.02 x 0.9 = 0.018,

Pr(Condition and -ve on Test1) = 0.02 x 0.1 = 0.002,

Pr(notCondition and +ve on Test1) = 0.98 x 0.1 = 0.098,

Pr(notCondition and -ve on Test1) = 0.98 x 0.9 = 0.882.

So, Pr(Condition | +ve on Test1) = (0.02 x 0.9)/(0.02 x 0.9 + 0.98 x 0.1) = 0.018 / (0.018 + 0.098) = 0.018 / 0.116 ~ 0.1551724137 and Pr(Condition | -ve on Test1) = (0.02 x 0.1)/(0.02 x 0.1 + 0.98 x 0.9) = 0.002 / (0.002 + 0.882) = 0.002 / 0.884 ~ 0.0022624434

The cost of Test1 is $100.

If the patient has the condition but it is undiagnosed then the cost is $4000.

If the patient does not have the condition but is falsely diagnosed as having the condition (false positive) then the cost is $1000.

If the patient is diagnosed correctly then we assume no cost.

If the patient is untested and does not have the condition then we assume no cost.

2(a)

Is it best for someone to undergo Test1 or not to undergo Test1?

It might help to do a decision tree and payoff table.

What are the possible states of the world for a particular person?

What are the possible actions for a particular individual?

So, let us put the original question - about having Test1 or not having Test1 - another equivalent way.

There is an expected return (or Expected Monetary Value, or EMV) of having Test1 and an

expected return (or EMV) of not having Test1.

Which is the least expensive, and what is the cost?

2(b)

If we knew in advance whether or not the person had the medical condition, then what actions would we take in each case?

What is the expected value with perfect information (EVwPI)?

2(c)

Following on from (a) and (b), what is the expected value of perfect information (EVPI)? We now lead into part (d).

People who test positive on Test1 are permitted to do a 2nd test, called Test2.

If a person has the Condition and tests positive to Test1 then the probability that they will test positive to Test2 is 0.98.

If a person does not have the Condition and tests positive to Test1 then the probability that they will test positive to Test2 is 0.02.

Test2 costs $110.

2(d)

If someone tests positive to Test1, does it make economic sense for them to do Test2?

As always, throughout the question, show all working.

----

Qu 3 (5 + 4 + 4 + 4 + 4 + 4 = 25 marks)

This a time series question involving a data-set which is supplied to you.

3(a)

Smooth the data using a centred 6-period moving average.

3(b)

Calculate the seasonal indices.

3(c)

De-seasonalise the data.

3(d)

Fit a least squares regression linear model to the data.

3(e)

Using the multiplicative model, forecast sales for 2023.

3(f)

If it turned out that sales in 2023 turn out to be: 20.5, 19.2, 22.7, 17.1, 21.7 and 19.5, then

calculate the MAE, MAPE and MSE of the forecast.

As always, throughout the question, show all working.

----

Qu 4 (11 + 8 + 6 = 25 marks)

This question involves both queuing and simulation - and possibly knowledge from elsewhere in the syllabus.

News vendor Phyllis Pauley sells newspapers, and each day she must determine how many newspapers to order. Phyllis pays the company 20c for each newspaper and sells the paper for 30c each. Newspapers that are unsold by the end of the day are worthless.

Phyllis knows that each day she/they can sell between 6 and 10 papers, with varying probabilities.

These are listed in the table below.

Demand	Probability
6	0.15
7	0.2
8	0.3
9	0.25
10	0.1

4(a)

State a personalised random number seed which is yours and not known to others, and which is greater than 0 but less than 1.

It can be a variant of your StudentId, but try to make it something which others don’t recognise – and try to make sure that it is different to what other students would use.

If you can, then use this to seed a random number generator.

If you do not know how to do that, then use it as your first random number.

Use this to generate 100 random numbers between 0 and 1.

In the column beside these numbers, generate 100 values (i.e., 100 values of the demand) from the above probability distribution given in the table.

As always, throughout the question, show all working.

4(b)

An M/M/1 queue has arrival rate 20/hour and a service rate of 30/hour.

The cost c_u or cu of under-ordering is given by the expected length of this queue at equilibrium.

The cost c_v or cv of over-ordering is given by the expected length of this queue at equilibrium.

Given c_u and c_v, and given the above probability distribution of demand from the table above, what is the optimal supply?

As always, throughout the question, show all working.

4(c)

Simulate steps of an M/M/1 queue with arrival rate 20/hour and server with a service rate of 30/hour.

We need to simulate the inter-arrival time for arrivals.

Whenever there is something in the queue and the server is not busy, we need to simulate the service time.

Recall your 100 random numbers between 0 and 1 from Qu 4(a).

Use the first 6 of these to show states of the queue.

The first random number will be used to give the time of first arrival.

The second random number will be used to give the service time.

The third random number will be used to give the inter-arrival time after the first arrival – in turn, it will give the time of arrival of the second arrival in the system.

The fourth random number will be used to give the second service time, after the second entry to the queue gets to the front.

The fifth random number will be used to give the inter-arrival time after the second arrival – in turn, it will give the time of arrival of the third arrival in the system.

The sixth random number will be used to give the second service time, after the thirdd entry to the queue gets to the front.

Then, no other arrivals are allowed into the system.

Based on this, for each of the 3 arrivals, show the arrival time in the system, the time it gets to the front of the queue and service begins, the time at which it has been served and leaves the system.

Item no.	Time of arrival in system	Time service begins	Time service is completed
1
2
3

Show all working in .pdf (recalling note 6 and other notes) and also giving your answer in spreadsheet.

Please recall and carefully re-read all notes and instructions at the start of the Assignment.

END OF FIT3158 ASSIGNMENT 3 (Monash University, 2nd semester 2023)