QBUS3330 S2 2025 Assignment 2

Brief

This is an individual assignment. It is worth 15% of your final grade. It involves a single business problem consists of two separate but interrelated parts.  Each part is worth different marks. It is due Week 12 on Friday 31 October at 11:59pm and must be submitted through Canvas using Turnitin.

The submission will comprise two separate submissions:

1.   A typed report (PDF please) that addresses all parts and contains images all of relevant tables, charts and @RISK output within the report. The report must function as a standalone document.

2.   An Excel file containing all the original tables, charts and distributions. The Excel file is provided for backup and corroboration purposes.

Failure to submit both files by the due date will result in late penalties being applied. Additional instructions occur after the background.

Background

You want to invest in a new business and decide on a bubble tea shop in a good location in Haymarket. You commission a consultant to conduct some initial market research and come up with a market entry strategy.  Based on the current competition, the consultant suggests the following product mix and prices (table 1)

The consultant also indicates that demand is very seasonal, with monthly peak season demand of 9,000 units being 50% higher than off-season demand (note 2). The co-efficient of variation of demand is 30% irrespective of the season (note 3).  Therefore, the consultant recommends modelling demand using a normal distribution (note 4) with different average monthly sales depending on the season.

Notes:

1.   The product mix remains constant for the whole year.

2.   Peak Season = 4 months (Dec – Mar). Off peak = 8 months (Apr – Nov)

3.   The coefficient of variation (CV) is a standardised measure of dispersion used when the mean varies widely. The CV = standard deviation / average. The normal distribution must be modelled using the standard deviation not the CV.

4.   Demand cannot be negative. Consider how @RISK help model non-negative demand.

You also talked to one of your friends who currently runs a bubble tea shop in Burwood, who gives you the details of reliable suppliers. He also mentioned that the final cost of the bubble tea varies because ingredients might expire or turn bad before use (e.g. milk), and because there are variations in ingredient consumption due to variability in the preparation process. You pass this information onto your consultant who suggests you can model variable costs using a triangular distribution with parameters outlined in table 2.

Your consultant also recommends making the following assumptions about fixed costs.

Note: Ignore all other costs, taxes and charges as well as the time value of money.

Task

You realise you haven t sufficient cash to invest in the project alone, so you decide to enlist some of your analytically savvy university colleagues as potential co-investors. To do this you need to produce a two part business report covering the following.

Part 1

Using @RISK, run a Monte Carlo simulation with at least 10,000 iterations to calculate the distribution for yearly sales, costs and profit/(loss). You should simulate the monthly demand of each tea (from Jan to Dec). However, because we are considering an average yearly cost, you are only required to simulate the average variable cost of each tea for the entire year (not per month). We are only interested in the first year of operations, so only simulate one year of demand.

1.1 Model description (20 marks and about 300 words)

Explain your Excel model including a description of your inputs and outputs and how the outputs are calculated. Include a screenshot of your Excel model in your report.

1.2 Model results (15 marks and about 200 words)

Briefly describe the resulting @RISK distributions (e.g. talk about the chance of incurring profit/losses, assessing profit as a % of revenue, etc.) and any business implications that arise. Include screenshots of relevant @RISK output in your report.

1.3 Your decision (5 marks and about 150 words)

Using only the information that was given (i.e., without making any other assumption), indicate whether it is worthwhile opening the shop and indicate why?

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

Part 2

As you are quite conservative (risk averse), assess how resilient your business would be to a lock down or other unforeseen demand disruption, like what was witnessed during COVID.

Suppose that every month there is a 5% probability that Haymarket goes into lock down for the entire month, which drops the average baseline monthly demand (table 1) by 50%.

During a lock down you predict that the majority of sales will be online, coming from food delivery apps rather than from customers that order from the actual shop. 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 a mean of $4 and a $1.20 standard deviation.

2.1 Impact of lock down demand reduction on profit (30 marks and about 300 words)

2.1.1 Describe the modifications that you made to your model. Include a screenshot of the revised Excel model in your report (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 lock down change the decision that you made at Part 1.3? (5 marks)

Note: At this stage only assess the effect of any demand reduction without considering the impact of free delivery for online sales.

2.2 Sensitivity analysis (10 marks and about 200 words)

Given your potential investors may be more risk averse than yourself, you are concerned that the 5% of lock down probability is too conservative given the unpredictability of past disruptions and how the public responded to them. Therefore, you have decided to assess a wider range of lock down probabilities: 1%, 5%, 10%, 20%, 30%, 40%, 50%.

2.2.1 What are your insights regarding how sensitive the business' expected profit/loss is to changing lock down probabilities? Draw a chart to help explain.

2.2.2 Does this assessment affect your decision to open the shop?

2.3 Impact of free delivery (10 marks and about 200 words)

To answer this question please assume a 5% lock down probability as in section 2.1.

2.3.1 Describe the modifications that you made to your model. Share relevant @RISK output.

2.3.2 How is your profit affected by offering free deliveries during lock down months?

2.3.3 Do you think it is a good idea to offer free deliveries?

2.4 Risk Optimizer (10 marks and about 200 words)

Instead of covering the 100% of the delivery cost, you have decided to cover just enough so there is only a 5% chance of incurring a loss greater than $20,000.

2.4.1 What is the maximum average percentage of the delivery cost you should cover for your customers to make this outcome more likely? Share relevant @RISK output.

2.4.2 Thinking about your results in Q2.1 and the trade-off between cost to the business and attractiveness of the offering to the customer, what percentage delivery fee might you charge? Why?

Notes:

1. For the risk optimizer settings use a step of 1% to test different percentages for how much of the delivery fee you should cover.

2. For each month (from Jan to Dec) you should simulate the lock down probability, the percentage of sales from online orders and the average delivery cost.

3. Like demand, delivery costs cannot be negative.

Other Instructions

Word count

1,500 words +/-10% excluding tables, @RISK output and any  references,  if required. Any words beyond 1,650 may not be marked. Submissions below 1,350 words may be penalised. The word count guidelines provided in each section are indicative only.  You may vary each section word count to suit your findings and analysis, provided the overall word count is maintained.

Style

This is a business report not an essay. The report should:

-     Have a suitable cover page.

-     Be divided into two distinct sections.

-    The text should be concise. Using bullet points is acceptable.

-     Be careful with the use of personal pronouns.

-     Be professionally and logically laid out with good grammar and spelling.

-     Marks will be deducted for submissions that do not meet these requirements.

@Risk

If @Risk is not used where required, the best mark possible will be 50% of the available marks.

Rubric

There is a general rubric relating to this assignment in the Assignment 2 Canvas module where this brief is located that provides detailed information regarding the quality expectations of the submission.   Marks will  be  allocated for each question according to the  brief and the quality expectations set out in the rubric.

Late penalties

Reports submitted after the due date will incur a late penalty of 5% per day or part thereof. Reports more than 10 days late will not receive a mark.

Academic integrity

This  is  an  individual  assignment.    Collaboration   is   permitted  and  encouraged  to   help understand the questions otherwise all written work, research and analysis needs to be your own.

This is a Lane 2 assessment. Students are permitted to use generative artificial intelligence (Gen AI) technologies such as ChatGPT to assist in completing the task. It is the student’s responsibility to evaluate, analyse and edit any Gen AI content that is incorporated into their reports. There is an expectation on the part of the markers that the quality of submissions will be higher than if Gen AI was not permitted and reports will be marked accordingly.

Students must clearly indicate when and where AI tools have been used by:

- Including a dedicated section at the end of the report titled "AI Tool Usage Declaration"

- Specifying which sections or analyses were created with AI assistance

- Describing how AI outputs were evaluated, analysed, and edited.

AI generated text cannot be used or represented as original, self-authored work. Failure to declare the use of AI tools will be reported as a major breach of policy for investigation.

In line with the Academic Integrity Procedures (2022), minor breaches of academic integrity (including poor paraphrasing, failure to acknowledge the work of others or incorrect citations) may be subject to a penalty of up to 15% of total available marks at the point of grading. Major breaches of the Policy will be submitted for investigation to the Faculty's Academic Integrity staff.