Company A Plans to Introduce a New Product Called X. Over the Next 5 Years: Data Analysis with Excel Assignment, SP, Singapore

Question 1

Company A plans to introduce a new product called X. Over the next 5 years, the marketing team estimates that the product can generate annual revenue of $30,000 (Year 1), $50,000 (Year 2), $70,000 (Year 3), $90,000 (Year 4) and $100,000 (Year 5), respectively.

It currently (Year 0) costs $30,000 to manufacture the product at a 3rd party location. Costs are expected to increase by 5% per annum.

It is estimated to cost only $5,000 (Year 0) if the company manufactures the product at its local facility (also with an increase of 5% per annum). But, the company needs to make an initial investment of $60,000. Decommissioning the plant at the end of Year 5 costs $10,000.

Create a well-labelled spreadsheet model to include the following:

1. Tabulate the cost to produce, over the next 5 years, for producing X at a 3rd party facility
2. Tabulate the cost to produce, over the next 5 years, for producing X at the local facility (leaving out the initial investment and cost to decommission)
3. Decide whether the company should produce X at the 3rd party facility or locally, assuming a discount rate of 15%.

Hint: Use the NPV or IRR functions in Excel appropriately.

Question 2

LoseItAll Casino has slot machines that have four (4) wheels. The wheels spin independently of each other when the handle is pulled.

For each wheel, the probabilities are 10% that TWICE will appear, 20% that EXO will appear, 20% that BTS will appear, 25% that an Ace will appear, and 25% that a Seven will appear.

For each pull of the handle, you put a bet of $1. If you get three TWICEs, you win your bet back plus $30. If you get any two TWICEs, you will win your bet back plus $5. If you get one TWICE, you will get your bet back. Otherwise, you lose your bet of $1.

You start with a stake of $100. Create a well-labelled spreadsheet model to simulate a session of 100 spins. You should have 100 rows, one for each spin. You should have columns at least for the spin number (1 through 100), each of the four wheels, the amount you win or lose on the spin, and the amount of money you have at the end of the spin. Each time you press F9 (re-calculate), you should get a different set of spins and results according to the above probabilities.

Compute the Total Winnings and the Balance left. Note that once you have gone bust (that is, ran out of the $100 you started with), you must stop playing. This means the Total Winnings and Balance should be summed over only until that criterion is met (although you simulate 100 spins).

Question 3

Celebrations Bakery is selling three (3) kinds of specialty cakes (Durian Blast, Laksa Deluxe, and Ondeh Allday), and their selling prices are $15, $24.95 and $35 respectively. The ingredients to make the cakes are (A, B, C, D) and the unit of each required to make the cakes are given in the below table. The unit costs of the ingredients are A ($0.50), B ($0.75), C ($1.25), D ($0.50).

The objective is to maximize the revenue for the Bakery.

1. Design a spreadsheet model to show how the production manager can optimize his revenue and profit, separately.
2. List down the input parameters used in Solver.
3. Explain the purpose of each constraint used.
4. What is the optimal number of cakes to produce for each category to attain the best possible profit?

Question 4

On the roll of a fair die, it is equally likely for numbers 1, 2, 3, 4, 5 and 6 to fall. However, when you roll the die multiple times, you may feel like it isn’t so. For fun, try rolling the virtual die six times here: https://www.random.org/dice/?num=1.

Although each number is equally likely to fall, you may observe that out of the 6 times you rolled, there were duplicates. In fact, the chance of getting a duplicate, i.e., at least two rolls resulting in the same number, is ~98.5%! (Explained here: https://www.random.org/faq/#Q2.8) Create a model using Monte Carlo simulations to arrive at the probability of getting a duplicate in 3 rolls (which is ~44.4%). You should have the following in your model:

A column for trial# (perform 50 trials)

3 columns, one each for the outcome of each of the 3 rolls

A result column showing whether a duplicate occurred or not

A clearly labelled cell for capturing the summarized “probability of getting a duplicate” based on the trials

A data table for repeating the simulation (as many times as necessary to reach convergence) Finally, the overall “probability of getting a duplicate” after the full simulation

Hint: A combination of SUMPRODUCT() and COUNTIF() functions might be of use for detecting duplicates.

Question 5

The Downstairs Café manager wants to understand patterns of customer arrivals to his business to better cater to their needs. Over a few days of painstaking effort, he has the following table summarizing the size of the groups his customers arrive in and the frequency.

Create a well-labeled spreadsheet model to estimate the size of the next 10 customer groups consisting of:

1. The corresponding CRF table
2. A model containing

A column for the group number (1 through 10) A column for the rand() output

A final column for the expected size of the customer group