Problem 2: Linda's Wedding Cost (60 points)

Scenario

Linda plans to get married in June. Her and her parents have been saving for years and have put aside $25,000 for the wedding. Frank, the wedding planner, tells the family that the estimates come out to $23,000. Linda, having understood her college business analytics course thoroughly, is concerned, however, about the variability of the costs. The florist keeps talking about the seasonality of certain flowers, shipping costs, size of arrangements, etc. and can't quite nail down a firm estimate. "Beverage" cost estimates are always a challenge.

In addition, the gown may or may not require additional fittings and alterations, and while most of the RSVP's have come in, Linda worries that some folks might not show up, while some family members might bring along uninvited guests. What's a future bride and her family to do?

Linda first develops a spreadsheet listing of the line item expenses for her wedding (See Table 1). Frank's $23,000 total estimate was based on his best guess for each expense item. However, Linda knows that some of the expenses are fixed (hall rental, entertainment) and some are variable (gown, flowers, food and beverages). Linda's next step is to gather data on the costs associated with each item. She asks the florist for the raw data (cost for flowers) associated with the last 100 weddings of this size that he has done at this particular church. From this data set Linda obtains descriptive statistics for the Flower cost data (Linda'sWeddingStudent.xls) and develops a histogram. Based on this analysis Linda uses a common distribution to model this uncertain cost. (Hint: you will need to draw a histogram of the flower costs to determine the appropriate distribution)

Using her knowledge of analytics, Linda uses Monte Carlo Analysis with Frontline Analytic Solver to create an input assumption for the variability of the cost of Flowers.

The estimate for her gown is that the price will likely be $5,000.00; if the gown fits perfectly, the cost may be as little as $4,500.00 (optimistic estimate), but if there are substantial alterations, the gown could cost as much as $5,500.00 (pessimistic estimate). All costs in between are also possible. Using her knowledge of analytics, Linda uses Monte Carlo Analysis to create an input assumption for the variability of the costs associated with her gown.

Linda has invited 125 people to the wedding, but either more or less than 125 could attend. Linda knows that if fewer than 125 people show up (her low estimate), her food costs will be $8,800 minimum. If more than 125 people show up (her high estimate), her food costs could be as high as $11,200. Total food costs anywhere between her high and low estimates are also possible.

Estimating "beverage" costs is more difficult. An analysis of the prior history of "beverage" costs at this institution with approximately 125 guests, showed that there was about a 20% likelihood these costs would be $3000, a 30% likelihood they would be $4000, and a 50% likelihood they would be $6000.

The spreadsheet now contains the same items as a static, or deterministic, model (Flowers, Gown, Hall Rental, Food, Beverage, Entertainment), but Linda adds variable estimates for Flowers, Gown, Food and Beverages based on her analysis of the variability of those costs. This gives her a much more sophisticated, probabilistic cost model that will provide a realistic assessment of a great many total cost scenarios that will lead to good cost expectations for her and her parents.

At this point, the Monte Carlo Analysis model has all of the information needed for simulating outcomes (forecast/results values). Linda chooses to simulate 2,000 trial runs; in other words, for each "run", Monte Carlo Analysis randomly selects one value from the distribution for each input variable and sums all 6 input variables to arrive at an output (TOTAL/results) value. When performed 2,000 times, the result is a range of output estimates representing a range of forecast total cost values.

Assignment

Provide an analysis of potential total wedding costs using the Monte Carlo method to answer the following questions. Provide screenshots to support your answers.

a. Which common probability distribution is most appropriate for flower costs? What parameter values did you use in your estimate for flower costs?

b. What is your total cost estimate using statistical means? How does this cost compare to the Mean total costs associated with the 2000 Monte Carlo Trials in your analysis?

c. Estimate the likelihood of costs exceeding 25,000.

d. According to the Monte Carlo analysis what are the minimum and maximum total wedding costs?

e. To keep total costs as low as possible, which item(s) should receive the most attention?

f. Is the $25,000 the family has set aside enough? Will Linda be happy?