Question

Paulina's Pizza is a well-known pizzeria and has contracted with a Business Analyst to estimate its cost equation. Based on

the data provided, the Business Analyst hypothesized that total costs were a function of fixed costs and variable costs. Recalling

from her BUSI 108 class, she hypothesized the following equation to be estimated,

                 Estimated Total Costs = b0 + b1*Pizzas

                 where

                 b0 = total fixed costs

                 b1 = marginal cost to produce 1 pizza

                 Pizzas = the quantity of pizzas produced

Using the least squares method, her regression results are the following,

                  Estimated Total Cost = 1,000 + 4*Pizzas

Paulina's Pizza tells the Business Analyst that they have tracked daily customer demand and the number of Pizzas sold depends

on the day of the week. Monday through Thursday (MidWeek) a low of 140 Pizzas per day are sold but Friday through Sunday

(Weekend) a high of 220 Pizzas per day are sold.

Pizzas are sold at a price of $10 per Pizza.

a) Assemble the Parameter Sections and the Model Sections for Paulina's Pizza. Calculate Total Cost, Total Revenue and Profit/

(Loss) for 170 Pizzas sold. Starting at 140 Pizzas and increasing by 10 to a maximum of 220 Pizzas, create a One-Way Data Table

calculating the Profit/(Loss) for the range of Pizzas that are sold during a week.

An area not-for-profit organization has asked Paulina's to assist with a fundraiser for their organization. The request is to

give 20% of the Pizza Price sold their organization when a customer presents a printed coupon from the organization. From

past experience with fundraisers, the percentage of customers that present the coupon ranged from 30% to 50%.

b) Using the What-If Analysis and the associated functions, create a table to reveal the range of Profit/(Loss) from both

the range of possible Pizzas sold and the percentage of customers who present the 20% coupon. There are several ways to

approach this problem but the objective is to create a table to show the various outcomes. Remember, Paulina's Pizza will

give 20% of its Total Revenue for that one day to a range of 30% to 50% of the customers that present the coupon.

Answer 1

a) As per the information given in the question

      Estimated Total Cost = 1,000 + 4* No of Pizzas Sold

      Price per Pizza sold = $10

Now for 170 nos. of Pizza sold, the calculation for Total Cost, Total Revenue and Profit/Loss is given below:

Starting at 140 Pizzas and increasing by 10 to a maximum of 220 Pizzas, a One-Way Data Table has been created below. It shows the Profit/(Loss) calculation for the range of Pizzas that are sold during a week.

b) Now we will show 4 scenarios.

     Scenario-1 : No Customer with 20% Coupon

   

Scenario-2: 30% Customer with 20% Coupon

Scenario-3: 40% Customer with 20% Coupon

Scenario-4: 50% Customer with 20% Coupon

No of Pizza Sold per day (n)	140	150	160	170	180	190	200	210	220
Weekly Sales (N = n*7)	980	1050	1120	1190	1260	1330	1400	1470	1540
Total Cost per day (c = 1,000 + 4*n)	$    1,560	$    1,600	$          1,640	$    1,680	$    1,720	$    1,760	$    1,800	$    1,840	$    1,880
Total Cost per week (C = c*7)	$ 10,920	$ 11,200	$       11,480	$ 11,760	$ 12,040	$ 12,320	$ 12,600	$ 12,880	$ 13,160
Price per Pizza (s)	$          10	$          10	$                10	$          10	$          10	$          10	$          10	$          10	$          10
50% Customer presenting Coupon (C50 = N*0.5)	490	525	560	595	630	665	700	735	770
Revenue from this 50% Customer (R50 = C50*s)	$    4,900	$    5,250	$          5,600	$    5,950	$    6,300	$    6,650	$    7,000	$    7,350	$    7,700
20% Contribution to not-for-profit organization (Cont = R50*0.2)	$        980	$    1,050	$          1,120	$    1,190	$    1,260	$    1,330	$    1,400	$    1,470	$    1,540
Revenue from balance 50% Customer (RB50 = N*0.5*s)	$    4,900	$    5,250	$          5,600	$    5,950	$    6,300	$    6,650	$    7,000	$    7,350	$    7,700
Actual Revenue per week (RA50 = RB50 + R50 - Cont)	$    8,820	$    9,450	$       10,080	$ 10,710	$ 11,340	$ 11,970	$ 12,600	$ 13,230	$ 13,860
Profit per week (P50 = RA50 - C)	$ (2,100)	$ (1,750)	$       (1,400)	$ (1,050)	$     (700)	$     (350)	$           -	$        350	$        700
	Loss	Loss	Loss	Loss	Loss	Loss	No Profit / Loss	Profit	Profit

A brief summary of profit / loss for all the 4 nos. of scenarios is given below:

	Total Profit/Loss in a week
Scenario-1	$           5,040	Profit
Scenario-2	$         (1,764)	Loss
Scenario-3	$         (4,032)	Loss
Scenario-4	$         (6,300)	Loss