A start-up entrepreneur has deployed a network of vending machines (VM) in Australia. He is looking at the operational analysis of his business to determine the size of the technician team required to support the VM network he has deployed all around Australia. The primary role of the technicians is to carry out unscheduled maintenance, such as attending to an empty coin reservoir whenever it cannot provide sufficient change to the users. You are an analyst designing a spreadsheet model that will allow him to carry out the analysis and decision-making. You are reminded to design your spreadsheet in a user-friendly way to facilitate usage. There must be clear and distinct inputs and outputs to the spreadsheet for analysis purposes.
Question 1 (15 marks)
To begin the analysis, you need to implement the minimum change model to determine the number of coins required for a given change amount. The minimum change model will evaluate the least number of coins required for a given change amount.
You can assume that the coin denomination in Australia comes in $1.00, $0.50, $0.20, and $0.10. For a change amount of $0.70, the minimum change model will recommend the coin combination of 1 x $0.50 and 1 x $0.20 (and not 1 x $0.50 + 2 x $0.10).
Make use of existing Excel functions to implement a model to calculate the minimum coin combination required for a given change amount.
Using your model to generate test cases for change amounts in the range of $0.10, $0.20,
…, $5.00.
Question 2 (30 marks)
With the minimum change model implemented, you are now ready to model the customer flow of the VM network. Based on historical observation, it is noted that the users of the VM arrives at an average interval of 15.2 minutes and they are likely to purchase N different items with the following distribution:
After knowing the amount of money to be paid to the VM, x, a customer is likely to pay for his purchase, y in the following manner:
y = (x + Δx), rounded UP to the nearest dollar,
where Δx is equally likely to be $1, $2 or $3.
For example, if the user has purchased 4 items costing a total of $1.30. It is equally likely that the user will deposit a sum of y = {$3, $4, $5} into the VM.
The relevant amount of change and hence the appropriate coin combination to be disbursed to the user is obtained using the minimum coin combination.
With the model in Question 1, and the information given in Question 2, set up a customer flow model that will allow you to determine the elapsed time when a VM needs to be serviced by a technician due to the coin reservoir running low.
The vending machine needs to be serviced by a technician the moment it is unable to provide a change to the user using the minimum coin combination developed in Question 1.
Using Monte Carlo simulation, what is the average interval between each successive servicing? You are to report your answer to the nearest hours (for example, 2.7 hours is to be reported as 3.0 hours).
Question 3 (15 marks)
You need to evaluate the frequency of coin reservoir top-up for the entire VM network in Australia to determine the size of the technician team.
If a technician can make 6 service trips a day, for a VM network of 100 machines, make use of the models you have developed in Questions 1 and 2 to determine the average size of the technician team required to support the business.
Report your answer to the appropriate integer value.
State clearly any assumptions you have made in your calculations
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
