Instructions

•    This is a compulsory individual assignment. The assignment will count 15 marks towards your final grade but will be marked out of 100.  There are 2 questions, with marks shown against each question.  Answer all questions. Any breakdown of marks within a question is shown at the question.

•    You must write all of your work in Cadmus, with the exception of images of your Excel model and @RISK simulation output. which should be created externally, saved locally to your computer as a JPEG file and inserted into Cadmus at the appropriate place using the insert image icon.

•    Answers must be easy to read with clear, succinct writing (bullet points are acceptable) and the proper use of grammar and punctuation. Where appropriate use numbers to support your reasoning and arguments.

•    Numbers only need to be given to maximum of 2 decimal places if it makes sense, otherwise rounded to the nearest dollar.

•    A rubric, available in the resources section sets out grading expectations for a variety of tasks required to complete the assignment.

•    Failure to appropriately attribute the work of others including the use of AI like ChatGPT will result in penalties. This may include the deduction of marks, failure in the   unit,    and/or   referral    to    the   University    Discipline    Committee.    Such acknowledgement should be included in the reference section at the end of the report.

Submission 凸

There are two parts to your submission:

1.   The report (which you write and submit in Cadmus)

o  You must write all of your words in Cadmus.

o  Appropriate images of your excel model and @RISK simulation output are to be saved locally to your computer as a JPEG file and inserted into Cadmus at

the appropriate place using the insert image icon.

2.   An Excel spreadsheet showing your model and @RISK modelling.

o This is submitted in Canvas here

o  The Excel file should be set out to match the report.

o  Students who fail to submit an appropriate excel file will be penalised in accordance to the rubric.

o  The Excel file will not be marked separated but is provided for backup, which is why it is essential to include appropriate screenshots of your model and       @RISK output in the report itself.

Late assignments will be penalised at a rate of 5% per day. Assignment submitted after 11:59pm on Sunday 16 April 2023 will receive a zero mark.

You experiment with different recipes for each type of tea and find that the cost of making different teas vary because of the different ingredients used and because of variations in the preparation process. Your best estimate is that the cost per tea (as a percentage of its selling price) is about 30%, but can vary between 28% and 35% and can be modelled using a triangular distribution (Table 2)

Table 2

Product Cost Triangular Distribution

(as a % of selling price)

Min              29%

Mode           32%

Max             36%

After talking with your friends, you both came up with the following assumptions about other costs (Table 3).

Table 3

Other expenses & costs

Store Build and Equipment [$/year]   30,000.00

Rent [$/year]                                  78,000.00

Salaries [$/year]                             262,800.00

Utilities [$/year]                              14,400.00

Others (Legal, Marketing, etc.)         15,000.00

Note: In your modelling ignore all other costs, taxes and charges as well as the time value of money.

TASK

Using @Risk, run a Monte Carlo simulation with at least 1000 iterations to calculate the distribution for yearly revenue, cost and profit/(loss). You have to simulate the monthly demand of each tea (from January to December). Since we are considering an yearly average cost, you only need to simulate the average cost of each tea for the entire year (and not per    month). We are only interested in the first year of operations, so only simulate one year of    demand.

Question 1

Explain your @Risk model mentioning your inputs and how the outputs are calculated. Include a screenshot of your model.

1.1 Model Description (20 marks - 250 words)

Describe the model output and resulting distribution curves (e.g. talk about the chance of incurring profit/losses, assessing profit as a % of revenue, etc.) and any business implications that arise. Share screenshots of relevant @Risk output

1.2 Model Results (15 marks - 250 words)

Using only the information that was given (i.e., without making any other assumption), would you open the shop? Why?

1.3 Your decision (5 marks - 150 words)

When making the decision to open the shop or not, only consider one year, but think carefully about how you as the owner might be involved in daily operations and what impact that might have on the potential return.

Question 2

You want to assess how resilient your business would be to a lock down or other unforeseen demand disruption, like witnessed during COVID. Suppose that for every month there is a   5% chance that the neighbourhood that your shop is located goes into a lock down for the    entire month, which drops the average monthly demand of the baseline scenario by 50%.

During lock down you predict that the majority of sales will online, coming from food delivery apps rather than from customers that order from the physical store. While you are uncertain about the online share of total sales you predict there is an equal chance for any value between 50% and 80%.

During lock down months, you are also considering offering free deliveries for online orders, rather than having your customers pay for the delivery. After researching the delivery fees of several different apps in your area, you believe that the monthly average delivery cost per online order will be normally distributed with mean of $4 and $1.25 standard deviation.

2.1 Impact of demand reduction due to lock down on profit (30 marks - 300 words)

At this stage you only need to assess the effect of demand reduction without considering the impact of free delivery for online sales.

2.1.1 Describe the modifications that you made to your model. Share a screenshot of the revised model (10 marks)

2.1.2 How does considering the chance of lock downs impact your profit distribution? Share relevant @Risk output. (15 marks)

2.1.3 Does considering the chance of a COVID lock down change the decision that you made at Question 1? (5 marks)