Excel Exercise - Pet Food Scenario

Demand and Supply Excel Exercise – Pet Food

Life's Abundance is a maker of small-batch healthy pet food. While Life's Abundance could pair with box

stores and online platforms like Amazon in order to provide larger varieties, they prefer to keep

manufacturing small and local. In doing so, they focus on producing higher quality, fresh pet food.

Suppose that you manage a small-batch pet food company similar to Life's Abundance. You are

beginning to produce cat food and are determining what price to charge. Assume the market demand

for dry cat food is as follows:

QD = 150 - 14.5PC + 8.8PD - 3.5PN + 6Inc - 22TR + 0.75PET

Where QD is monthly demand for dry cat food (in thousands of bags). PC is the price of dry cat food in

the market. PD is the average price of canned cat food a substitute for dry cat food. PN is the average

price of a container of catnip and is used to gauge the price of complimentary goods. Inc is average state

income (in thousands). TR is the total number of recalls that you have made on your products (this is

included to capture one of the benefits of small-batch quality controlled production). PET is the percent

of households that own pets in your state. This last variable is included to capture a difference in

consumer preferences.

The market for cat food also has supply, produced by pet food companies similar to Life's Abundance

and your company, which can be stated as follows.

QS = -75 + 11.8PC - 3.5PPI - 2.5PD + 5.2VET + 1.2Sup

Where QS is monthly supply of dry cat food (in thousands of bags). PC is the price of dry cat food in the

market. PPI is the Producer Price Index (an index used to gauge changes in the costs of production in the

US). PD is the price of canned cat food which could easily be produced instead of dry cat food. VET is the

number of veterinarian clinics in the market (in the hundreds). Sup is the number of pet food makers

supplying cat food in the market (in hundreds).

Using the market supply and demand functions above and the current market condition information below, create a table that automatically calculates the quantity demand and the quantity supply and allows you to change the market conditions and instantly see the change in quantity demand and quantity supplied. Fill in the values for each of the variables except Price.

Demand:

• Price of canned cat food (per case): $36.99

• Price of Catnip: $8

• Income: $54,000

• Recalls: 0

• Pet ownership rate: 64.6%

Supply

• PPI: 110

• Price of canned cat food (per case): $36.99

• Number of veterinarian clinics: 2800

• Number of Suppliers: 1400

Now that you've set up your demand and supply functions, answer the following questions.

1. When the price of dry cat food increases by $1, what is the effect on quantity demanded and

quantity supplied?

2. How much does a $1 decrease in the price of canned cat food shift the demand and supply

curves?

3. Suppose the price of dry cat food is currently $50 per bag. How many bags of dry cat food will

be demanded and supplied monthly?

4. If the market price of dry cat food falls to $43 per bag, how many bags will be demanded and

supplied monthly?

5. Trying prices in $1 increments between $43 and $50, at what price and quantity does the

market equilibrium occur? (round to closest)

6. Suppose the costs of production decrease to 87.5. If the price of dry cat food stays at the

equilibrium price found in 6., how much will be supplied in the market and will this create a

surplus or shortage?

7. With the decrease in production costs to 87.5, at what price will the market be in equilibrium

again? What will be demanded and supplied at this price? (round to closest)