You are a partner in a small bakery that sells a variety of bread, rolls, cookies, and donuts After reading OnwardLinks to an external site by Howard Schultz, you fall in love the idea of making the bakery more than just a store Instead, you want the bakery to be a place where people gather and build community To do this, you look into the option of adding indoor and outdoor seating as well as a commercial coffee machine You pitch the idea to the other partners and insist that it will likely be good for business as well The partners want to see some numbers before they agree They agree to let you use $1,500 to hire a consultant to help you put the numbers together The partners expect a 12 return on investment (Cost of Capital) Since this is a partnership, we will not worry about Taxes and Depreciation Here are the other relevant numbers Cost of commercial coffee maker $5,000 Cost of adding seating $10,000 Life span is on average 10 years with no salvage value No opportunity costs but will complement current products Estimated $2,500 increase in cash flows from sale of bakery items each year Expect to Sell 15,000 cups of coffee in year 1 This should grow to 18,000 in year 2 21,000 in year 3, and plateau at 24,000 in year 4 In year 1, a cup of coffee will sell for $2 00 with the expectation of increasing the price by 3 per year In year 1, the cost to the bakery of a cup of coffee is estimated at 40 cup with the expectation of a 3 increase in the cost per year Staffing will need to be increased This is a fixed cost and estimated at $30,000 for year 1 with a 3 increase per year Working Capital as a percent of next year's sales is estimated at 10 You plug all the numbers into a handy spreadsheet found on the learning content page of this module, and see the the MIRR is 20 76 with an NPV of $24,443 45 You are very happy with the results, but your partners have questions How did the consultant arrive at their numbers Give some probable sources the consultant may have relied on to find reliable numbers Your partners notice that there is no analysis of risk All they see is the most likely outcome (base case) Assume that the consultant is unavailable to provide you with a worst case or best case scenario until later that year If partners wanted to make a decision now, give examples for how the risk could be mitigated By doing some independent research and following the methodology of the consultant, you are able to create an informed worst case scenario In the worst case, equipment costs increase are $20,000, externalities are $1,000, units sold in year 1 are 12,000, year 1 sales price is $1 89, and cost per unit is $0 45 Everything else is likely unaffected Under this scenario, net cash flows would be negative for the first nine years and result in an MIRR of 25 42 If the partners decide to go ahead with the project give an example of two decision point where the partners could decide to abandon or scale back the project if it looks like the worst case scenario is happening Why would these be logical decision points This is a bonus question Using the Excel spreadsheet and the Base Scenario, determine the Break Even amount when you adjust various inputs The best way to do this is through using Goal Setting in What If Analysis Report on three inputs you adjusted and what the break even amount is

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 02, 2024

You are a partner in a small bakery that sells a variety of bread, rolls, cookies, and donuts. After reading OnwardLinks to an external site.

You are a partner in a small bakery that sells a variety of bread, rolls, cookies, and donuts. After reading OnwardLinks to an external site. by Howard Schultz, you fall in love the idea of making the bakery more than just a store. Instead, you want the bakery to be a place where people gather and build community. To do this, you look into the option of adding indoor and outdoor seating as well as a commercial coffee machine. You pitch the idea to the other partners and insist that it will likely be good for business as well. The partners want to see some numbers before they agree. They agree to let you use $1,500 to hire a consultant to help you put the numbers together.

The partners expect a 12% return on investment (Cost of Capital). Since this is a partnership, we will not worry about Taxes and Depreciation. Here are the other relevant numbers:

Cost of commercial coffee maker = $5,000; Cost of adding seating = $10,000
Life span is on average 10 years with no salvage value
No opportunity costs but will complement current products. Estimated $2,500 increase in cash flows from sale of bakery items each year.
Expect to Sell 15,000 cups of coffee in year 1. This should grow to 18,000 in year 2; 21,000 in year 3, and plateau at 24,000 in year 4.
In year 1, a cup of coffee will sell for $2.00 with the expectation of increasing the price by 3% per year.
In year 1, the cost to the bakery of a cup of coffee is estimated at 40/cup with the expectation of a 3% increase in the cost per year.
Staffing will need to be increased. This is a fixed cost and estimated at $30,000 for year 1 with a 3% increase per year.
Working Capital as a percent of next year's sales is estimated at 10%.

You plug all the numbers into a handy spreadsheet found on the learning content page of this module, and see the the MIRR is 20.76% with an NPV of $24,443.45. You are very happy with the results, but your partners have questions.

How did the consultant arrive at their numbers? Give some probable sources the consultant may have relied on to find reliable numbers.
Your partners notice that there is no analysis of risk. All they see is the most likely outcome (base case). Assume that the consultant is unavailable to provide you with a worst case or best case scenario until later that year. If partners wanted to make a decision now, give examples for how the risk could be mitigated.
By doing some independent research and following the methodology of the consultant, you are able to create an informed worst case scenario. In the worst case, equipment costs increase are $20,000, externalities are $1,000, units sold in year 1 are 12,000, year 1 sales price is $1.89, and cost per unit is $0.45. Everything else is likely unaffected. Under this scenario, net cash flows would be negative for the first nine years and result in an MIRR of -25.42%. If the partners decide to go ahead with the project give an example of two decision point where the partners could decide to abandon or scale back the project if it looks like the worst case scenario is happening. Why would these be logical decision points?
This is a bonus question. Using the Excel spreadsheet and the Base Scenario, determine the Break-Even amount when you adjust various inputs. The best way to do this is through using Goal Setting in What-If Analysis. Report on three inputs you adjusted and what the break-even amount is.