Q1. Historical pricing and demand data for a product is provided below. Assume that it costs $12/unit to make this product with a fixed cost of $24,000. Price Demand (units) 24 4160 28 3841 32 3412 36 3296 a. Assume that the demand is a linear function of price. Using a scatter plot and a trendline find the linear equation that defines the relationship between price and demand. Write down the equation. b. Using your answer from part(a) develop a spreadsheet model for the profit and implement it on the spreadsheet. Assume that the production can meet the demand (units demanded = units produced = units sold). c. Using the model from part(b) construct a one-way data table to estimate the price (to the dollar) for which the profit is maximized. Write down the price and the corresponding profit. d. At what price the profit would be exactly $40,000? Q2. The manager of an extended stay hotel believes that the number of rooms rented during any given week has a uniform distribution between 20 to 36. The weekly price per room is $800 and weekly operating costs follow a normal distribution with mean $20,000 and a standard deviation of $4,000. Run a simulation model to answer the following questions. (Hint: Since the number of rooms should be integer values, it is advisable to use an integer random number generator (with uniform distribution) such as =randbetween(min,max)) a. What is the expected average weekly profit? b. What is the probability that the weekly profit will be positive? c. If the weekly operating cost has a maximum value of $24,000 (with the same normal distribution and same mean and standard deviation), how much does this change your answer for part(a) and part(b) Q1. Historical pricing and demand data for a product is provided below. Assume that it costs $12/unit to make this product with a fixed cost of $24,000. Price Demand (units) 24 4160 28 3841 32 3412 36 3296 a. Assume that the demand is a linear function of price. Using a scatter plot and a trendline find the linear equation that defines the relationship between price and demand. Write down the equation. b. Using your answer from part(a) develop a spreadsheet model for the profit and implement it on the spreadsheet. Assume that the production can meet the demand (units demanded = units produced = units sold). c. Using the model from part(b) construct a one-way data table to estimate the price (to the dollar) for which the profit is maximized. Write down the price and the corresponding profit. d. At what price the profit would be exactly $40,000? Q2. The manager of an extended stay hotel believes that the number of rooms rented during any given week has a uniform distribution between 20 to 36. The weekly price per room is $800 and weekly operating costs follow a normal distribution with mean $20,000 and a standard deviation of $4,000. Run a simulation model to answer the following questions. (Hint: Since the number of rooms should be integer values, it is advisable to use an integer random number generator (with uniform distribution) such as =randbetween(min,max)) a. What is the expected average weekly profit? b. What is the probability that the weekly profit will be positive? c. If the weekly operating cost has a maximum value of $24,000 (with the same normal distribution and same mean and standard deviation), how much does this change your answer for part(a) and part(b)
