Main Street Charities (MSC) is a not-for-profit organization that works in a large city to alleviate poverty. One of its programs is supported solely by private donations. In preparing the budget for the following year, the accountant at MSC is concerned about the ability of the charity to continue with the program, because of uncertainty about the local economy.The accountant has developed some data on program donations and costs and how they vary with two measures of the local economy: median income and the unemployment rate. He has found that annual donations for the program average about $500,000 plus $10 for every dollar median income is above (or less $10 for every dollar median income is below) $60,000. Donations are also estimated to be $50,000 less (more) for every percentage point above (below) 3.5 percent. Average annual program costs are $350,000 plus $100,000 for every percentage point the unemployment rate is above 4.0 percent.In looking at the economic forecasts for next year, the accountant determines that the expected median income will be $60,000 and the expected unemployment rate is 4.5 percent. However, some forecasts have median income at $55,000 and others at $65,000. In addition, some forecasters believe that the unemployment rate could be as low as 4 percent and others believe it could be 5 percent.

MSC measures programs by their operating surplus, which is the difference between program donations and program costs. The accountant is quite concerned that this program will end up with a negative surplus and might face cancellation.

RequiredUse a spreadsheet to prepare an analysis of the possible operating surplus for MSC, similar to that in Exhibit 13.15. What is the range of possible operating surpluses? Assume, perhaps unrealistically, that the median income and unemployment rates are independent.

Transcribed Image Text:

B Sales D Operating Cost of Gross Marketing Margin Quantity Revenue Goods Sold & Admin Profit 3 225,000 $5,625,000 $3,712,500 $ 1,912,500 $ 1,652,250 $ 260,250 225,000 $ 6,750,000 $ 3,712,500 $3,037,500 $ 1,652,250 $ 1,385,250 225,000 $7,875,000 $3,712,500 $ 4,162,500 $ 1,652,250 $ 2,510,250 4 240,000 $ 6,000,000 $ 3,960,000 $ 2,040,000 $ 1,686,000 $ 354,000 240,000 $ 7,200,000 $ 3,960,000 $ 3,240,000 $ 1,686,000 $ 1,554,000 240,000 $ 8,400,000 $ 3,960,000 $4,440,000 $ 1,686,000 $ 2,754,000 8 11 260,000 $6,500,000 $ 4,290,000 $2,210,000 $ 1,731,000 $ 479,000 260,000 $ 7,800,000 $ 4,290,000 $ 3,510,000 $ 1,731,000 $ 1,779,000 260,000 $9,100,000 $ 4,290,000 $ 4,810,000 $ 1,731,000 $ 3,079,000 12 13 14 15