sensitivity analysis sanjana rsquo s sweet shoppe operates on the boardwalk of a new 691274

Sensitivity Analysis

Sanjana’s Sweet Shoppe operates on the boardwalk of a New England coastal town. The store only opens for the summer season and the business is heavily dependent on the weather and the economy in addition to new competition. Sanjana Sweet, the owner, prepares a budget each year after reading long term weather forecasts and estimates of summer tourism. The budget is a first step in planning whether she will need any loans and whether she needs to consider adjustments to store staffing. Based on expertise and experience, she develops the following:

Sanjana assumes, for simplicity, that the gross margin and the estimated number of customers are independent. Thus, she has nine possible scenarios. In addition to the cost of the products sold, Sanjana estimates staffing costs to be $25,000 plus $2 for every customer in excess of 20,000. The marketing and administrative costs are estimated to be $10,000 plus 3 percent of the gross margin.

Required

Use a spreadsheet to prepare an analysis of the possible operating income for Sanjana similar to that in Exhibit 13.15. What is the range of operating incomes?

Exhibit 13.15 Spreadsheet Analysis of Alternative Budgeting Scenarios

Spreadsheets are extremely helpful in preparing budgets, which require considerable sensitivity, or what if, thinking. Spreadsheets help link the various what if scenarios to changes in financial variables and to financial consequences.

For example, the simple spreadsheet in Exhibit 13.15 shows the nine scenarios reflecting the estimated sales prices and sales quantities just described. Each scenario is associated with estimated changes in cost of goods sold and in marketing and administrative costs. (Row 9 presents the budget used in the text ignoring inventories; assume that the other scenarios were worked out by management and presented to us.) Note that the amount shown for operating profits varies considerably between the worst scenario in row 4 and the best scenario in row 14. This analysis alerts management that Santiago Pants will earn less than half the expected profits under the worst scenario. Given the company’s precarious cash position, this analysis will motivate managers at Santiago Pants to develop contingency plans for obtaining cash to meet operating expenses in the event that the more pessimistic scenarios are realized.

These are only a few of the numerous scenarios that management could develop. Furthermore, managers could develop alternative scenarios for any of the budgets that we have discussed. Large companies usually develop complex financial models to deal with the numerous interactions of the budget and these models involve considerably more than just the sensitivity analysis illustrated in Exhibit 13.15.

For example, a decision support system model has been developed to help managers assess the trade offs of different business approaches. Most budgeting activities involve decisions having more than one strategic objective. A company could have two objectives, to maximize income and minimize labor overtime. As the number of objectives increases and they begin to conflict, the decision making process becomes more complex. An interactive, multiple objective, programming model allows managers to deal with often conflicting objectives by using a straightforward set of equations and constraints. The result is a solution that maximizes each objective.