In November, Sue Jones of Sue's Sweats faces the task of committing to specific production quantities for each sportswear item her company will offer in the coming year's line. Commitments are needed immediately in order reserve space in remote production facilities. Unfortunately, actual demand for these products will not become known for at least 6 months. In fact, the demand for the current line is only beginning to become available.

In an initial experiment, Sue has asked 6 of her most knowledgeable employees to forecast demand for the various models of the company's premier voga-pants. Forecasts for one product are given in the table below, along with the average standard deviation for the forecasts.

Experience suggests that the actual standard deviation in demand is roughly twice that of the standard deviation of the forecasts.

Employee 1       900

Employee 2       1000

Employee 3       900

Employee 4       1300

Employee 5       800

Employee 6       1200

AVERAGE:          1017

SD:                      194

Production costs for a particular high-end yoga-pant are about 75% of the wholesale price, which in this case, is $110. Unsold yoga-pants can be sold at salvage for around 8% of the wholesale price.

Sue would like to construct a spreadsheet that will calculate the company's pre-tax profit for any combination of order quantity and actual demand at the end of the selling season. For now, just ignore the forecasts and demand uncertainty. The spreadsheet should use the demand as an input parameter, but should correctly calculate the profit for each possible pair of order quantities and demand quantities.

1.      Develop an Excel spreadsheet model to compute the PRE-TAX profit for any specific values of order quantity and demand. Again, don't try to account for the demand uncertainty.

2.      Draw an influence chart that shows all of the relationships that must be modeled into this spreadsheet.

pur-new-sol

Related Questions