LOG307 Revenue Forecasting for 2025: Simulation Analysis Using Excel and @RISK, Singapore

Question 1

The director of Harris’s Home Goods Store is attempting to forecast the revenue generated by each key department in the store for the year 2025. The director has estimated the lowest and highest possible growth rates for revenue in each department. The director believes that any growth rate between the minimum and maximum values is equally likely to happen. The estimates are summarised in Table 2:

Table 2: Growth Estimated for Key Departments

a) Create a spreadsheet to simulate the total revenues that could occur in the year 2025, using in-built Excel functions. Identify input variables, random variable(s), and output variables in the spreadsheet. Show all the necessary calculations. Use 1,000 iterations for the simulation. Compute the summary statistics of the total revenue for the year 2025. (15 marks)

b) Instead of in-built Excel functions, run the simulation model with @RISK functions to determine the total revenues that could occur in the year 2025. Use the same simulation setting as part (a). Compute the summary statistics of the total revenue for the year 2025. Show the output chart. (10 marks)

c) Construct a 95% confidence interval for the average level of revenues the manager could expect for the year 2025. Use @RISK functions. (5 marks)

d) If the growth rate follows an exponential distribution instead, simulate the total revenues that could occur in the year 2025. Use @RISK functions to develop the simulation model. Make any necessary assumptions. (10 marks)

e) Compare, based on models in 2(b) and 2(d), the chances that total revenues in 2025 will be more than 5% larger than those in 2024. Comment on how the probability distribution of growth rate impacts the chances of revenue larger than 5%. (10 marks)