Setting the Right Price (Spreadsheet Exercise)

Analysis and Metrics, Free Activities, Pricing

Topics

price points, profitability, demand curve, fixed and variable costs

Review the activity below or download the PDF student worksheet

Student Discussion Activity

Introduction

The local pizza store has approached you to help them work out the best price to charge for their pizzas.

Through a series of marketing experiments with price, they have managed to construct a demand curve which provides an estimate of demand for each price point from $1 up to $20.

In addition, they have provided you with their cost structure – so you have everything you need to calculate what price they should charge. But please note that you will probably need to use a spreadsheet to make this calculation exercise much easier.

Cost Structure of the Pizza Store

The store’s weekly rental cost is $1,000. This is a fixed cost that does not charge with their sales level.

Their average unit cost is $3 per pizza. This is a variable cost that increases with every pizza sold.

And their staff cost is $500 per staff member per week. This is a semi-variable cost that increases by increments depending on how many pizzas that they plan to sell during the week.

To help you calculate staff costs, they have provided you with this simple table:

  • They need 2 staff members if they sell 0-500 pizzas per week
  • They need 3 staff members if they sell 501-1,000 pizzas per week
  • They need 4 staff members if they sell 1,001-1,500 pizzas per week

Estimated Demand (Sales) Curve

Below is their demand curve (and underneath is the data). As can be seen (and expected), as the price falls the quantity demanded (their unit sales) increases. At a price of $20 they sell 203 pizzas, and at just $1 per pizza they will sell 1,500 pizzas.

 

Here is the price and quantity data for the above demand curve:

 Price  Quantity  Price  Quantity  Price  Quantity  Price  Quantity
20 203 15 343 10 581 5 984
19 225 14 381 9 646 4 1094
18 250 13 424 8 717 3 1215
17 278 12 471 7 797 2 1350
16 309 11 523 6 886 1 1500

Student Discussion Questions

  1. Calculate the best price to maximize profits for the pizza store.
  2. If they wanted to maximize revenue (instead of profits), what is the price point that will achieve that goal?
  3. Assuming that they were in a price war with another local competitor, what is the lowest price that they can charge in order to cover their costs and at least break-even?

Note: For the above calculations, just price to the whole dollar only – there is no need to price down to cents.

Related Activities

Scroll to Top