Question 1

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.

You are required to create appropriate models to analyse the sales data to support decision making.

(a) 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

(b) The CEO is not happy with the outlet that has the worst annual sales. He is considering whether to close down the outlet in the longer term. Using Solver or Goal Seek, create a model to appraise this decision. For an outlet to be retained, it must achieve annual sales of at least 85% of the average outlet sales of the entire chain.

By how much must the annual sales in the worst performing outlet increase for it to be retained? What would the total annual sales for the whole chain?

(c) XYZ operates consistently on a Gross Margin Percentage of 30% of Annual Sales. Its group Annual Operating Expenses, an indirect overhead, is 12% of Sales. The owners of XYZ have so far invested $2 million in the business. Based on the data in table 1,

create a sub-model to review its Net Profit and Return on Investment (ROI) percentage for the entire business. What is the ROI percentage given that its investment in the business is $2 million?

[Hint: Net Profit = Gross Margin – Operating Expenses, ROI % = Net Profit /
Investment]

(d) The owner of a rival business called Lelong Store is planning to emigrate overseas. He is prepared to sell his business for $1.5 million. Lelong Store has 5 outlets and a cost structure that is very similar to XYZ. The owner has projected annual sales over the next 7 years to be $2.3m, $2.4m, $2.5m, $2.55m, $2.6m, $2.62m and $2.65m, respectively.

XYZ has accumulated huge cash reserves over the years and its CEO is interested to expand his business by acquiring Lelong Store. For investment purposes, XYZ uses a time horizon of 7 years and requires the Return on Investment (ROI) of the intended acquisition to be equivalent or better than XYZ’s own business ROI.

Create a model to analyse whether it is viable for XYZ to buy over Lelong Store. State any assumption(s) used by your model.

Use your model to make a recommendation to the CEO of XYZ. Justify your
recommendation quantitatively.
[Hints: Yearly outflows consist of Cost of Goods Sold (COGS) and Operating Expenses, COGS % + Gross Margin % = 100%]

Presentation

Your models for Q1 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation is required.

Question 2

Daniel Jacob has just graduated with a business degree from SUSS. He is considering whether to seek employment or to run his own small business. Based on current job market conditions,

he is confident of getting a job that will pay him at least $3,000 a month.
On the other hand, he is also keen to be his own boss. As a fitness enthusiast, he has been able to source for a digital fitness watch at a cost of only $13. He expects to be able to re-sell the watch profitably.

After some research, he estimates that he will be able to sell 500 such watches per month online at a price of $25 each. His fixed monthly business expenses work out to be $3,500. His income will be based on the net profit of the business.

(a) Employ a model to help Daniel analyse this fitness watch business. Should he attempt to start this business? Why?

(b) Further research indicated that the monthly demand could increase to 1,500 if the price is lowered to $18. Comment on whether your recommendation will change if Daniel now sells the watch at $20 each? Justify your answer quantitatively. (Note: the new price is $20, not $18)

Presentation

Your models for Q2 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels and formatting of numbers. Documentation is required.

Question 3

Answers to the two questions below should not exceed 250 words each.

(a) The four core functions of all businesses are Sales, Finance, Production and Human Resource. State which of these functions is the most important. Explain by drawing upon your experience at your workplace or in your MonsoonSIM game.

(b) Many organisations use a combination of full-time and part-time labour. Compare the two types of labour and relate how you would use both types effectively to benefit your organisation if you were a manager.