Mr and Mrs Tripp, owners of Tripp’s Dairy, are considering making their own kefir and selling it through a variety of specialist shops in the region. They are members of the Dairy Trade Association and have contacted small diaries in other regions to gather data.
Their best estimate of a price-demand curve for the Input Data sheet is as follows:

The variable cost of production is $0.35 per unit, with additional fixed costs of $500 per month where production is below 1,000 units per month; and $1000 per month where production is above 1,000 units.
Because of the uncertainty relating to the figures, Mr and Mrs Tripp wish to assess the impact of a 10% and 20% reduction in the sales volume before deciding the best price point.
You have been asked to help them prepare a financial model to determine the best price for their new product, and the overall impact on the company’s cash flow including if sales are less than hoped.

This is quite a simple financial model to get us started. So let’s go to the Model I have built.

If you want to build this Model for yourself before seeing my version, pause the video here.

This is a small model, and I have combined the Input Data Sheet and Core Model together. I am also assuming that we have already prepared a sheet detailing the purpose of the Model, the stakeholders, the assumptions and the sources of data.

The Input Data area shows the projected number of units sold at each price point along with the variable and fixed costs. All of these are user-definable.

In the Core Model, I am using a drop-down list to incorporate a possible reduction in sales volume. That is built into row 16 of the core model.
We then calculate the total revenue in row 18
Less the variable costs, gives the gross margin.
And we subtract the fixed costs to give the incremental contribution to profit.

We can change the percentage of the projected sales volume in cell C13 to assess the impact on contribution. At 80% of the projected sales volume, the profit maximising price is $1.75 per unit and this remains the case when the full projected sales volume is used.
However, a unit price of $1.99 per unit delivers similar performance, and I would recommend a more detailed study of likely costs between $1.50 and $2 per unit to be sure of the profit maximising price.

Nevertheless, Mr and Mrs Tripp can be reassured that introducing kefir to their range will generate a positive contribution to the diary’s profits and cash flow of at least $1,000 per month at a price of $1.75, even if sales are 20% below projections.

Lesson 7 Pricing Example 2: Cresswell’s Biscuits

Cresswell’s Biscuits have some available capacity for a new product line. They have done a great deal of market research and come up with three options:
a)Taking a contract from a major supermarket to make “own brand” digestive biscuits. This will fully utilise the capacity with 60,000 packets per month guaranteed at a contract price of $0.25 per packet
b)Introducing a new luxury biscuit – Cresswell’s Crunch – that they will sell to high-class retailers. The market is competitive, and sales are likely to be 20,000 packets are month at a price of $0.60 per packet to the retailers.
c)Moving into the market for savoury biscuits – Cresswell’s Tang – and selling them through delicatessens. Breaking into this new market will require more marketing to achieve sales of 40,000 packets per month at $0.70 per packet to the retailers.

The fixed costs for running the new capacity are $2,000 per month for all three options, plus $1,000 depreciation per month. There are also additional costs depending on the option chosen:
•The “own brand” digestive biscuits have an ingredient cost of $0.15 per packet
•Creswell’s Crunch has an ingredient cost of $0.35 per packet; additional labour costs of $2,000 per month, and marketing costs of $1,000 per month
•Cresswell’s Tang has an ingredient cost of $0.30 per packet, labour costs of $2,000 per month, and additional marketing costs of $4,000 per month.

You are required to model the three options to identify, with reasons, the route that you would recommend them to pursue. The sales potential of the Cresswell’s Tang biscuit is particularly uncertain, and you must model this to show the impact of sales volume 20% less than forecast.
For simplicity, you are required to compare the monthly income and cost profiles only. We will come to a more thorough project appraisal over time later in the course.

If you want to build this Model for yourself before seeing my version, pause the video here.

The Cresswell’s Biscuits model is again fairly simple. I am not using a price demand curve in this example but rather testing three different options. In reality, you would probably create price-demand curves for the three options in order to examine them thoroughly. To keep this Model simple, I am assuming we have already identified the optimal price for each product.

The calculation sheet allows the user to vary the projected sales volume for the Cresswell’s Tang biscuits as that is considered more uncertain than the others.
Even at 80% of the projected volume, the proposed new savoury biscuit range – Cresswell’s Tang – appears to be the best option in terms of its contribution to company overheads and profits. However, this is a new market, and there is risk associated with it. For example, sales volume 20% below that forecast (32,000 packets per month) reduces the contribution by nearly 50% to $3,800.
The choice, then, for Cresswell’s Biscuits comes down to risk. The “Own Brand” option guarantees a risk-free return. The new Cresswell’s Tang offers the possibility of greater rewards but the risk that the extra hard work will bring a return no better than the Own Brand option (and possibly worse if other estimates prove incorrect).
Personally, I would advocate for Cresswell’s Tang – the company developing a new market which, if successful, will grow in the future. Provided the company manages the risks, works hard at the marketing, and keeps good control of its costs, this option provides the best return and the most satisfaction for the company and its staff. The “Own Brand” option is, essentially, a dead end. It may work for a short time, but the supermarket buyer will likely continue to pressure costs by tendering the contract to other manufacturers in the future.

Lesson 8 Pricing Example 3 Carraway’s Takeaway – Pricing Options with Cash flow
So far, we have based our pricing models on maximising the contribution to the business. This is a good place to start, but many businesses will also be interested in the cash flow impact on the business. This example adds cash flow into the scenario.
Carraway’s Takeaway has a bustling lunchtime trade in the town centre. They have previously only sold sandwiches and soups and are considering expanding their offering. They have come up with three options:
1.The Sushi Platter. The town is home to many businesses, several with strong Japanese connections. Sushi, it is believed, will sell well.
2.A Slice of Pie. There is a strong local demand for “different” choices, and the team at Carraway’s believe that a range of gourmet pies could be offered with salad – including vegetarian pies.
3.Wrap It Up. The existing, strong, sandwich offering could be complimented with a range of sandwich wraps – the market is already coming through the door.
I have prepared Input Data sheets and Calculation sheets for all three options in the workbook provided with this course. Let’s go there now.

I have combined all the information for Carraway’s Takeaway on one sheet to make for easier viewing. In a real situation, I would encourage separate tabs for each section.
First, let’s look at the assumptions for the Model. I’ve kept these simple:
1) The capital cost of each option is a start-up cost incurred in full at the beginning of the project
2) There are no loan charges or interest costs: the company has the funds to undertake the capital works required.
3) Revenues are received in the current month - this is a cash business
4) Ingredient and labour costs are paid one month in arrears
5) Inflation, tax and VAT are ignored

Next, we have an input data sheet for each of the three options showing a price-demand curve and subsequent costs.

The Calculation sheets are more complex because we need a model for each of the three price points for each of the three options.
I’ve highlighted the price point at which contribution is maximised after six months for each of the three service options.
Contribution from the Sushi platter option is maximised at a price of $3.99, although the price point $4.99 produces a similar contribution. However, at $5.99 each, this option makes a loss.
The Slice of Pie idea produces a higher contribution than the Sushi Platter option at its three price points, with the highest contribution generated when each slice of pie is sold for $4.99
However, it is the Wrap it Up option that is by far the most profitable, doubling the contribution of the Slice of Pie idea. At a price of $2.99 per wrap, a contribution of over $46000 is generated after six months.

The best option for Carraway’s Takeaway would appear to be to concentrate on the market that they already know by expanding their sandwich range into wraps at a price of $2.99 each.
It would also be interesting to carry out some sensitivity analysis on these three options to gauge the risk inherent in them.

Now let’s look at the cash flow statement in the output data section, which adds a whole new layer to the analysis.
Let’s quickly revisit the assumptions before we go further. And the first assumption is the most significant – the capital cost of each option is incurred at the beginning of the project.
In the six months covered by this Model, the Wrap it Up option is the only one that generates a positive cash balance with a payback period on that investment of three months.
Neither of the two other options payback their investment during the first six months. This is not necessarily a bad thing – the capital equipment (and, therefore, the project) have an expected life of five years – but it shows that the Wrap it Up is the lowest risk as well as delivering the highest contribution.


Lesson 9 Exercise: The Trumpton Town Café

You have been asked to help prepare a pricing model for your friends Suzi and Sam, who run the Trumpton Town Café. They opened the café two years ago with funds loaned by a wealthy family member and by a locally owned catering supplier keen to encourage businesses to use their products.
The café employs four part-time members of staff, including a bookkeeper.
The number of customers using the café in the morning from its opening time of 9.30 am to 11.30am has been low. Trade is very good at lunchtime, and for afternoon teas, but Suzi and Sam are losing money on those first two hours.
They would like a simple financial model to model different pricing strategies for that morning period to see how they might improve profitability. Data on price and potential demand has been supplied by the catering supplier based on the experiences of other catering outlets. The information on costs has been extracted from the café’s own records.
The options are:
1.Close the café for the morning period, opening at 11am to prepare for lunch. That would save wage costs of $1200 per month, and energy costs of $300 per month.
2.Open at 9.30am as at present and offer a “morning coffee” promotion to attract early shoppers.
3.Open at 8am and offer a special breakfast promotion to attract early business. The catering supplier loan-holder has provided data on possible outcomes. This would incur additional wage costs of $1200 per month, and energy costs of $300 per month. The “morning coffee” promotion could also be offered.

Questions Part 1
Answer the following questions:
A)Who are the stakeholders for the pricing model?
B)What aim is each of the stakeholders likely to have for the Model? And, consequently, what assumptions used in the Model might each stakeholder have a particular interest in?
C)Write a couple of sentences as a “Problem Statement” for the Trumpton Town Café.
Pause the video here while you consider your answers.

Questions Part 2
You have been asked to develop a pricing model to assess which of the three options delivers the best contribution to the company’s profits, based on incremental costs and revenues. To assess risk, you are required to review the impact of a 10% and 20% reduction in demand compared to the forecast.
The following Input Data has been prepared by the company:
1.Closing the café in the morning and opening at 11am will generate no income but will save costs totalling $1,500 per month
2.Opening at 9.30am with a morning coffee promotion priced at $3.95 would, it is believed, attract 400 additional customers per month. The incremental food cost would be 50%. There are no additional wage or energy costs to the current situation.
3.Opening at 8am with a breakfast promotion would attract around 2,000 additional customers per month with an additional spend of $2.50 per head. Additional food costs would be 40%, and this option would incur additional wage costs of $1200 per month, and energy costs of $300 per month. The “morning coffee” promotion could also be offered from 9.30am to 11am if desired. The morning coffee promotion would attract customers as detailed in option 2 above.

Prepare an Input Data sheet for the Trumpton Town Café and a Calculation Sheet to help them select the best option, including the sensitivity analysis requested. Consider only the incremental revenues and costs of the options.

Pause the video here while you consider your answers.

Suggested Answer
Suggested Answer Part 1
Let’s look at stakeholders first. The stakeholders for this pricing model are Suzi and Sam themselves; the family member who has invested in the business; and a representative of the catering company who has invested. I believe that the staff members who may gain or lose work as a result of the changes should also have a representative stakeholder.

Suzi and Sam are likely to want to improve the overall profitability of their business and, therefore, its long-term success. They will be interested in risk assessing the pricing-demand data in order to make an informed decision. And they will be particularly interested in assumptions about growth in customer sales and costs.

The family investor will want to get their money back, with the interest agreed and will be primarily interested in the financial security of the business. Like Suzi and Sam, he or she will be interested in the assumptions about customer numbers and sales. He or she will also be interested in any assumptions that may impact cash flow – for example, creditor payments; and any assumptions about any additional capital requirement and how it would be funded.

The catering supplier funder will also be interested in getting their money back with interest and will want to be assured about the financial security of their loan. However, they are also interested in growing their own business by securing customers whose needs are likely to increase. As a supplier to the café, they will be interested in any assumptions made about the sourcing of the additional supplies needed, including assumptions about catering costs. There is the possibility of friction between Suzi and Sam, and this stakeholder if the café wishes to use other suppliers – for example, artisan bakers for the “morning coffee” promotion or local farms for the breakfast menu.

The staff will be concerned about their security of employment, including any impact on wages and workload. They will be interested in assumptions about how additional hours will be resourced; and what that will mean for them. The café’s staff may be enthusiastic about the potential for additional hours (or a pay-rise if the promotions are successful). However, some of the staff may not want to work extra hours; or may be worried about the impact of bringing in new staff.

My thoughts on a Problem Statement for the Trumpton Town Café are as follows:
“Presently, Trumpton Town Café is making a loss on its activities before 11.30am. This puts the business and its staff at risk, and we seek to reverse this situation by testing different pricing strategies for the early morning. The aim is to find a solution that boosts profitability by at least 10%; while allowing a wage increase of the same amount, and ensure the financial security of the business”.

This could be used to describe the purpose of the Model.

Let’s go to the Calculation Sheet now to review the Model

Suggested Answer Part 2
I have the Input Data area at the top of the sheet, with a drop-down menu that allows the project sales volume to be adjusted by the amounts requested.

For option 1, opening the café at 11am, there are no additional costs or revenues. Instead, the costs of earlier opening are saved, and I have shown these as negative wage costs and negative energy costs.
There are no incremental wage or energy costs for the morning coffee promotion, but incremental revenue is generated.
For option 3 there are additional wage and energy costs from opening earlier. I have combined this option of breakfast opening with the mid-morning coffee promotion to maximise contribution.

The calculation sheet shows the incremental revenues and costs of the three options and it is clear that option 3, opening for breakfast, delivers the highest contribution. Option 2, offering a morning coffee promotion only will generate a positive contribution but less than that if Suzi and Sam shut the café until 11 am.
Switch to 80%
If sales are 20% less than forecast, the option to open at 8.30 am still generates a slightly higher contribution than that of opening at 11 am. However, the difference is very small, and the café owners need to be confident in their costs and aware of other risk factors before making that decision.

We’ll come back to Trumpton Café later in the course to review the cash flow impact of the breakfast opening option.