Problem
XYZ Bargain Store Pte Ltd is a local grocery business that focuses on low-cost as its competitive advantage. It has a chain of 5 retail outlets located in different parts of Singapore. It is now in the process of reviewing its business for the next financial year.
Table 1 below shows the annual sales from all 5 outlets in the previous year.
Table 1. Annual Sales in thousands of dollars.
You are required to create appropriate models to analyse the sales data to support decision-making.
Develop a model to analyse sales data by completing table 1. You need to fill in the shaded cells shown in yellow. Note that these cells can only contain formulae and functions. Hardcoding of numerical answers is not allowed.
Average and Median refer to the average and median sales by month and by outlet, respectively. Highest and Lowest refer to the highest and lowest sales, respectively. Stdev is the standard deviation of sales by month and by outlet. Annual refers to the yearly sales over 12 months.
Extend Table 1 to add 2 dynamic cells that will automatically identify the Shop with the Lowest Monthly Sale in the past 12 months. The first cell should show the lowest monthly sale figure. The second cell should show the name of the Shop.
The values in the 2 cells should update automatically if the sales data in table 1 were to change. You may add other helper cells as necessary.
• Which outlet has the lowest monthly sales in the previous year? State the lowest monthly sales.
• Which outlet has the most consistent sales performance throughout the year? Explain why quantitatively.
• Which outlet has the worst annual sales performance? State its annual sales.