Computer Science Assessment - CREATING A SPREADSHEET FOR DECISION SUPPORT

T H E BAKERY

Decision Support Using Excel

PREVIEW

Your bakery is doing well, and you are thinking about adding muffins as a complementary product. You would have to borrow money to finance the equipment needed to make muffins. In this case, you will use Microsoft Excel to see if the added product line would make financial sense.

BACKGROUND

You own the bakery, which sells delicious, fresh, hot doughnuts. Your shop is on Main Street in your hometown. You have plenty of traffic both from pedestrians and customers in cars. Sales are brisk, and your business is doing well.

Other shops on Main Street sell baked goods, but none of them sell doughnuts. You notice from observation and from reading the trade journals that more people are eating muffins. You want to know if adding muffins to your menu will increase your profits.

Muffin baking is a separate process from frying and would require a significant amount of investment

capital. You would borrow the money from your bank and install the equipment by the end of 2014.

In this case, you will use Excel to see whether Mellon can achieve profits and manage the debt that would result from adding muffins to the product line.

Your DSS would include the following inputs:

1. Your decision to sell muffins or not

2. The anticipated growth rate of your business

3. The “pricing power” you anticipate in the future

You set your prices by adding an amount called the “margin” to the item’s unit cost. For example, if the unit cost to make a doughnut is 20 cents, you would add a nickel margin, meaning that the doughnut’s selling price is 25 cents. In effect, your profit margin is the nickel. If you have “pricing power,” then you have the ability to add a high margin. If you do not have pricing power, you cannot be aggressive with the margin you add.

Your DSS model needs to account for the effects of the input values on costs, selling prices, and other variables. Your model will let you develop “what-if” scenarios with the inputs, see the results, and then decide what to do.

PART 1: CREATING A SPREADSHEET FOR DECISION SUPPORT

In this Part, you will produce a spreadsheet that models the business decision. Then, in Part 2, you will create the scenarios and the scenario summary and answer the questions posed.

Part 1A

Create a new sheet in the Excel workbook. Make it the first (1st) sheet. Name it “Overview”.

Below row 5 answer the following questions. State the question then give your answer. Make sure you format your answers so they fit on the screen and do not scroll off to the right. Check your spelling and grammar.

Put your answers to the following questions on the Overview Sheet.

1. Tell me about Mellon Bakery in your own words. For example, what does the company do?

2. What is the purpose of this worksheet? What is it being used to help the business determine?

3. Why are scenarios a good tool to accomplish the purpose?

Part 1B

Create the spreadsheet model of the proposal’s financial profile. The model covers the

three years from 2015 to 2017. This section helps you set up each of the following spreadsheet components before entering cell formulas:

• Constants

• Inputs

• Summary of Key Results

• Calculations

• Income and Cash Flow Statements

• Debt Owed

A discussion of each section follows. The spreadsheet skeleton is named bakery.xlsx. You MUST

use it.

Constants Section

Your spreadsheet should include the constants shown in Figure 1. An explanation of the line items follows the figure.

FIGURE 1 Constants section (see Appendix)

• Tax rate—The tax rate is applied to income before taxes. The rate is expected to stay constant each year.

• Minimum cash needed to start year—You want to have at least $1 million in cash at the

beginning of each year. Your banker will lend you the amount you need at the end of a year in order to begin the new year with $1 million.

• Fixed administrative expense—Rent, maintenance, insurance, electricity, and so on, which are expected to increase each year.

• Interest rate for year—The interest rate your banker will charge for any borrowing. The

banker says that interest rates are expected to rise as the economy recovers.

• Business days per year—Your shop is open every day except Christmas Day. Notice that

2016 is a leap year, so you will be open 365 days that year.

• Cost per doughnut—The cost of the raw ingredients needed to make a doughnut, including flour, sugar, salt, flavorings, oil, milk, raisins, and nuts. The cost to make doughnuts has increased each year, and you expect the trend to continue.

• Cost per cup of coffee—The cost of coffee, sugar, artificial sweetener, milk, and cream, all of which are needed to make a cup of coffee. You buy only fairly traded, organic coffee, sugar, and milk, so your costs are higher than average.

• Cost per muffin—The cost of the raw ingredients for each muffin. The same ingredients used to make muffins are used to make doughnuts. Muffins are larger than doughnuts, so you will use more of each ingredient to make muffins. You anticipate increased costs for the next

three years.

• Average salary per worker—The average salary is expected to increase each year, as shown.

Inputs Section

Your spreadsheet should include the following inputs for the years 2015, 2016, and 2017, as shown in

Figure 2.

FIGURE 2 Inputs section (see Appendix)

• Sell muffins?—Will you add muffins as a product line? Enter a Y or an N. The choice applies to all three years.

• Growth rate—Enter the rate of sales growth expected for each year. This input will later be

used to estimate doughnut sales in the three years.

• Pricing Power?—Do you anticipate having pricing power in the three-year period? In other words, can you set your selling prices aggressively? Enter a Y or an N. The choice applies to all three years.

Summary of Key Results Section

Your spreadsheet should include the results shown in Figure 3. An explanation of each item follows the figure.

FIGURE 3 Summary of key results section (see Appendix)

For each year, your spreadsheet should show net income after taxes, cash on hand at the end of the year, and bank debt owed at the end of the year. The cells should be formatted as currency with zero decimals. These values are computed elsewhere in the spreadsheet and should be echoed here.

Calculations Section

You should calculate intermediate results that will be used in the income and cash flow statements that follow. Calculations, as shown in Figure 4, may be based on year-end 2010 values. When

called for, use absolute referencing properly. An explanation of each item in this section follows the figure.

FIGURE 4 Calculations section (see Appendix)

• Average number of doughnuts sold per day—This number is a function of the growth rate from the Inputs section. If the expected growth rate is High in a year, the number sold in that year will be 10% more than the number sold in the prior year. If the expected growth rate is Medium, the number sold in a year will be 3% more than in the prior year. If the expected growth rate is Low, the number sold will be 3% less than in the prior year.

• Average cups of coffee sold per day—This number is a function of the growth rate from the Inputs section. If the expected growth rate is High in a year, the number sold in that year will be 8% more than the number sold in the prior year. If the expected growth rate is Medium, the number sold in a year will be 5% more than in the prior year. If the expected growth rate is Low, the number sold will be 3% more than in the prior year.

• Average number of muffins sold per day—This number is a function of the decision to sell

muffins, which is shown in the Inputs section. If you decide not to sell muffins, then of course this number will be zero. If you decide to sell muffins, then 500 will be sold per day in 2015, the number sold will be 3% greater in 2016 than in 2015, and the number sold will be 3% greater in 2017 than in 2016.

• Margin per item—This number is a function of the pricing power, which is shown in the Inputs section. If you will have pricing power, the margin per item will be 45 cents. Otherwise, the margin per item will be 40 cents.

• Number of workers—This number is a function of expected growth, which is shown in the Inputs section. If the annual growth rate is expected to be High, one worker more will be employed than in the prior year. If the annual growth rate is expected to be Medium, the number of workers will remain the same as in the prior year. If the yearly growth rate is expected to be Low, one worker less will be employed than in the prior year. In no year, however, will only one worker be employed; you cannot run the shop with just one worker.

Income and Cash Flow Statements

The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This is followed by the income statement and concludes with the calculation of cash on hand at year’s end.

For readability, format cells in this section as currency with zero decimals. Your spreadsheets should look like those shown in Figures 5 and 6. A discussion of each item in the section follows each figure.

FIGURE 5 Income and Cash Flow Statements section (see Appendix)

• Beginning-of-year cash on hand—The cash on hand at the end of the prior year.

• Yearly doughnut revenue—This amount is a function of the selling price, the number of doughnuts sold per day, and the number of business days. Recall that the selling price equals the unit cost plus the margin.

• Yearly coffee revenue—This amount is a function of the selling price, the number of cups of coffee sold per day, and the number of business days. Recall that the selling price equals

the unit cost plus the margin.

• Yearly muffin revenue—This amount is a function of the selling price, the number of muffins sold per day, and the number of business days. Recall that the selling price equals the unit cost plus the margin.

• Total revenue—This amount is the sum of doughnut, coffee, and muffin revenue.

• Yearly doughnut costs—This amount is a function of the number of doughnuts sold, the unit cost of a doughnut, and the number of business days.

• Yearly coffee costs—This amount is a function of the number of cups sold, the unit cost of

a cup of coffee, and the number of business days.

• Yearly muffin costs—This amount is a function of the number of muffins sold, the unit cost of a muffin, and the number of business days.

• Salary costs—This amount is a function of the number of workers in the year and the

average salary per worker.

• Fixed administrative costs—This amount is a constant that can be echoed here.

• Total costs—The sum of doughnut, coffee, muffin, salary, and fixed costs in the year.

• Income before interest and taxes—The difference between total revenue and total costs.

• Interest expense—The product of the debt owed at the beginning of the year and the annual interest rate on debt.

• Income before taxes—The income before interest and taxes minus the interest expense.

• Income tax expense—This value is zero if the income before taxes is zero or negative.

Otherwise, income tax expense is the product of the year’s tax rate and the income before taxes.

• Net income after tax—The difference between the income before taxes and income tax expense.

Line items for the year-end cash calculation are discussed next. In Figure 6, column B represents

2014, column C is for 2015, and so on. Year 2014 values are NA except for End-of-year cash on hand, which is $1 million.

FIGURE 6 End-of-year cash on hand section (see Appendix)

• Net cash position (NCP)—The NCP at the end of a year equals the cash at the beginning of the year plus the year’s net income after taxes.

• Borrowing from bank—Assume that a bank will lend you enough money at the end of the

year to reach the minimum cash needed to start the next year. If the NCP is less than this minimum, you must borrow enough to start the next year with the minimum. Borrowing increases the cash on hand, of course.

• Repayment to bank—If the NCP is more than the minimum cash needed at the end of a year and debt is owed, you must pay off as much debt as possible (but not take cash below the minimum cash required to start the next year). Repayments reduce cash on hand, of course.

• End-of-year cash on hand—The NCP plus any borrowing and minus any repayments.

Debt Owed Section

This section shows a calculation of debt owed to the bank at year’s end, as shown in Figure 7. An explanation of each item follows the figure.

FIGURE 7 Debt Owed section (see Appendix)

• Beginning-of-year debt owed—Debt owed at the beginning of a year equals the debt owed at the end of the prior year.

• Borrowing from bank—This amount has been calculated elsewhere and can be echoed to

this section. Borrowing increases the amount of debt owed.

• Repayment to bank—This amount has been calculated elsewhere and can be echoed to this section. Repayments reduce the amount of debt owed.

• End-of-year debt owed—In 2015 through 2017, this is the amount owed at the beginning of a year, plus borrowing during the year, and minus repayments during the year. In 2014, this debt equals $500,000 if muffins will be added and zero if muffins will not be added. (Recall that adding muffins would require a bank-financed loan at the end of 2014.)

PART 2: USING THE SPREADSHEET FOR DECISION SUPPORT

You complete the case by using the spreadsheet to gather data needed to determine your strategy and by answering the questions posed.

You think that a low growth rate is unlikely throughout the three years. You think that growth will either be medium in all years or will start low and then improve. You think that the following six scenarios will help answer your questions:

1. Muffins-Medium-No Power—You add muffins and enjoy medium growth in all years, but you have no pricing power.

2. Muffins-Increasing-No Power—You add muffins and see low growth in 2015, medium growth in

2016, and high growth in 2017. However, you have no pricing power.

3. Muffins-Medium-Power—You add muffins, enjoy medium growth in all years, and have pricing power.

4. Muffins-Increasing-Power—You add muffins and see low growth in 2015, medium growth in

2016, and high growth in 2017. You also have pricing power.

5. No Muffins-Medium-Power—You do not add muffins, and you enjoy medium growth and pricing power in all years.

6. No Muffins-Increasing-Power—You do not add muffins, and you see low growth in 2015, medium growth in 2016, and high growth in 2017. You also have pricing power.

The primary business question is whether muffins should be added as a product. You will use your spreadsheet to gather data on this issue and the following related questions. The data will help you decide whether to add muffins.

1. Are net income and debt significantly different, with and without muffins?

2. If muffins are added, how quickly will the $500,000 loan be paid off? Assume that you think you can pay off the loan in six years. You can estimate this by seeing how much of the loan remains after three years. In which scenarios does it appear that the loan will be paid off within six years?

3. How important is it to have pricing power? Compare the results of two similar scenarios.

For example, how different are the results between scenarios 1 and 3 or between scenarios

2 and 4?

Part 2A: Using the Spreadsheet to Gather Data

You have built the spreadsheet to model the business situation. For each of the six scenarios listed earlier, you want to know the net income after taxes, the end-of-year cash on hand, and the end-of- year debt owed in 2017.

You must use Scenario Manager to run “what-if” scenarios with the six sets of input values. Set up the six scenarios.

The three output cells are the 2017 net income after taxes, 2017 end-of-year cash on hand, and

2017 end-of-year debt owed from the Summary of Key Results section. Run the Scenario Manager to gather the data in a scenario summary. The scenario summary must be in your file to get credit.

Part 2B: Documenting Your Recommendations in a Memorandum

At the bottom of the scenario summary report state your answers to the questions given above (Recall that adding muffins requires a $500,000 loan.)

DELIVERABLES

Appendix

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Figure 7