Reference no: EM132170893
Background
The Zone is an international sporting goods company that provides sports equipment, apparel, and footwear to thousands of retail locations primarily in the United States and Canada. Its corporate headquarters are located in Fort Collins, Colorado. The company is noted for its innovative product designs and quality. TheZone employs approximately 15,000 people working on three continents to provide the highest quality products and services possible.
TheZone has paid particular attention to managing its brand image with an emphasis on associating the company;s products with an athletic lifetsyle that pushes individuals to be their best, while having fun.
The Case
TheZone's sales group has determined that no matter what the marketing expenses, the unit product mix among the four styles of swimsuits remains relatively constant. The men's racers sales are about equal to the men's trunks sales. The women's one-piece sales are approximately 50% of the men's trunks sales. The women's two-piece sales are approximately 70% of the men's trunks sales. By determining the probability of different sales volumes for the men's trunks, you can determine estimates of the sales of the other swimsuit styles.
In addition, TheZone has decided that its general policy will be to set the selling price of the men's racers $7 over the selling price of the other three types of suits. TheZone's marketing staff has conducted market research to determine the relative probability of various sales levels for the men's trunks.
Richard wants to evaluate the profitability of future price increases for the swimsuits line via a simulation. He has started a workbook named Sim that will contain the simulation and its results. Richard has gathered all the data needed for the Assumptions section of the worksheet and used an income statement format to model the profitability results. He needs your help to set up and run the simulation. Complete the following:
Open the workbook named Sim.xlsx, and save the file with the name Swimsuits Simulation.xlsx.
In the Swimsuits worksheet, to the right of the cells that contain the individual probabilities for the various men's racers style sales figures, create a cumulative probability distribution table for the unit sales estimates. The cumulative probabilities must be in the first column of the table so the lookup function you create in Step 4 works properly. Add informative headings and basic formatting to the distribution table so it has a more professional appearance (this step has already been completed for you).
In a cell close to the distribution tables, use the RAND function to add a randomly generated value to the worksheet.
Write a VLOOKUP function in cell C8 that looks up the random number you generated with the RAND function in Step 3 in the cumulative probability distribution lookup table you created in Step 2 and returns the corresponding unit sales figure.
Create the structure for a two-variable data table that will run a simulation to calculate the income before taxes given various units sold and men's trunks selling prices of $39.95 to $59.95 by increments of $5.00. The default value of 1,000 iterations of the simulation is sufficient. (Hint: Remember that when using a two-variable data table to run a simulation, one set of input values in the data table's structure must be a sequence of numbers that represents the number of iterations of the simulation. Because the number of units sold is determined via the RAND and VLOOKUP functions, the range of selling prices is the only “real” set of input values included in the data table’s structure. For this reason, your column input value can be any empty cell such as E30.)
Instruct Excel to complete the data table. (Hint: Remember that when using a data table to run a simulation, the real set of input values is directed to the input section of the worksheet, and the iteration values are directed to any empty cell in the worksheet.) Add informative headings and basic formatting to the data table so it has a more professional appearance.
Use the AVERAGE, STDEV.S, MAX, and MIN functions to create a range in the worksheet that summarizes the simulation's profitability results for each sales price. Add informative headings and basic formatting to the summary range so it has a more professional appearance.
Based on your simulation, what conclusions or recommendations can you make for Richard? Provide a brief written analysis or notes somewhere within your Excel workbook (same sheet or a new sheet is fine, whatever you prefer).