Preliminary plans are under way for the construction of a new stadium for a major league baseball team. City officials have questioned the number and profitability of the luxury corporate boxes planned for the upper deck of the stadium. Corporations and selected individuals may buy the boxes for $100,000 each. The fixed construction cost for the upper deck area is estimated to be $1,500,000 with a variable cost of $50,000 for each box constructed.
1. Use the data table tool in Excel (Data > What-If-Analysis > Data Table) to calculate the profit realized when s = 40 and Qvaries from 0 to 60 in increments of 5, i.e. Q = 0, 5, 10, 15, etc. and then use the graph wizard to graph the profit line as a function of Q. (you can cope your excel table to answer this question.)
A reliable sales forecast has been obtained indicating that City officials would be able to sell 40 boxes. However, the City officials are concerned that this conclusion might change if more accurate estimates were available for the fixed cost of construction, the marginal construction cost, and the unit revenue for each box. Therefore, before a final decision is made, management wants sensitivity analysis done on these cost/revenue estimates. Use the spreadsheet you developed in part c and use the goal seek command and/or data table features in Excel to perform the following sensitivity analysis. Note: Parts (e), (f), and (g) are independent of each other and each part refers to the original data.
2. How large can the fixed cost of construction before this project ceases to be profitable?
3. How large can the marginal construction cost be before this project ceases to be profitable?
4. How small can the unit revenue for each box be before this project ceases to be profitable?