excel assignment

Bahlman_Chapter_10_2nd_Degree_PD_Problem_Start.xlsx

Home >Mathematics homework help >Statistics homework help >excel assignment

Problem

2nd Degree PD Problem
Shell has over 13,000 gas stations in the United States. In addition to purchasing gasoline, customers can also purchase snacks and beverages at gas stations. Most gas stations sell fountain soda which can be purchased in various sizes. Suppose you manage a gas station and must determine how much you will charge for fountain soda. The sizes you will make available are 8oz (S), 16oz (M), and 24oz (L). For simplicity, we will assume customer demand (Q) is fountain soda in 8oz increments; for example, a customers who demands 2 is demanding a 16oz soda. Demand for soda is Q = 2,500 - 1,500P. Assume zero fixed costs. Marginal cost per 8oz of soda is $.20. The demand function and the cost value are gathered below.
		Size	Number of 8oz increments
		8oz (S)	1
		16oz (M)	2
		24oz (L)	3
		Q =	2,500	-	1,500	P
		Marginal cost per 8oz of soda	$0.20
a)	Set up formulas to calculate quantity demanded, total revenue, total cost, and profit. Using solver, find the profit maximizing price and quantity. What are the profit maximizing price, quantity, and profit? How much would medium and large sodas cost under this pricing structure?
	Price (S)
	Quantity Demanded
	Total Revenue
	Total Cost
	Profit
	Price (M)
	Price (L)
b)	Suppose you wanted to engage in second degree price discrimination. You decide to charge $1.25 for a S (the first 8oz). How many 8 oz portions do you sell and what is your profit?
	Price (S)	$1.25
	Quantity Demanded
	Total Revenue
	Total Cost
	Profit
c)	Suppose you charge $.50 extra to increase the size from S to M. What is the price for a Medium soda? How many additional 8oz portions do you sell (subtract the demand found in part b from the new demand)? How much additional profit is made (find TR and TC for the increased sales and subtract them)?
	Charge	$0.50
	Price (M)
	New Quantity Demanded
	Additional Quantity Demanded
	Additional Total Revenue
	Additional Total Cost
	Additional Profit
d)	Suppose you charge $.40 extra to increase the size from M to L. How many additional 8oz portions do you sell (subtract the demand found in parts b and c from the new demand)? How much additional profit is made (find TR and TC for the increased sales and subtract them)?
	Charge	$0.40
	Price (L)
	New Quantity Demanded
	Additional Quantity Demanded
	Additional Total Revenue
	Additional Total Cost
	Additional Profit
e)	What is total profit using second degree price discrimination?
	Total Profit
	Would one decide to price discriminate? Why or why not?
	One	decide to discriminate because the total profit

Instructions

Project Description: In this problem, you will use the Solver Add-in to determine the profit maximizing price. Then you will determine the prices for different sizes of soda, calculate the quantity demanded and profit for size S, additional quantity demanded and additional profit for sizes M and L, and the total profit using the second degree price discrimination. Finally, you will determine whether the second degree price discrimination should be applied.
For the purpose of grading the project you are required to perform the following tasks:
Step	Instructions	Points Possible
1	Start Excel.	0
2	Prepare cells E14-E17 to use the Solver Add-in. In cell E14, by using cell references, calculate the quantity demanded. Use cells F9, H9, and E13. In cell E15, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E13 and E14. In cell E16, by using cell references, calculate the total cost for the quantity demanded. Use cells F10 and E14. In cell E17, by using cell references, calculate the profit. Use cells E15 and E16. Note: The total cost should be a positive value.	4
3	Use the Solver Add-in to find the price for the size S that maximizes the profit. Using Solver Add-in Select the Solver in the Analyze group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). Choose cell E17 in the Set Objective field. Select the Max option below. Choose cell E13 in the By Changing Variable Cells field. There are no additional constraints. Make sure that the “Make Unconstrained Variables Non-Negative” box is checked. Leave the GRG Nonlinear solving method. Click Solve. In the popup window select the Keep Solver Solution option. Do not check any additional boxes and click OK. As the result, you will see the price in cell E13, that gives the maximum profit. The values for the quantity demanded, total revenue, total cost, and profit will update automatically.	1
4	In cells E18 and E19, by using cell references, calculate the price values for sizes M and L. Use cells F7, F8, and E13.	2
5	In cells E22-E25, perform your calculations for the size S if the price is set at the level specified in cell E21. In cell E22, by using cell references, calculate the quantity demanded. Use cells F9, H9, and E21. In cell E23, by using cell references, calculate the total revenue. Use cells E21 and E22. In cell E24, by using cell references, calculate the total cost. Use cells F10 and E22. In cell E25, by using cell references, calculate the profit. Use cells E23 and E24. Note: The total cost should be a positive value.	4
6	In cells E28-E33, perform your calculations for the situation when the size can be increased from S to M for the extra charge specified in cell E27. In cell E28, by using cell references, calculate the price for size M. Use cells E21 and E27. In cell E29, by using cell references, calculate the new amount of quantity demanded. Use cells F9, H9, and E27. In cell E30, by using cell references, calculate the additional quantity demanded. Use cells E22 and E29. In cell E31, by using cell references, calculate the additional total revenue. Use cells E27 and E30. In cell E32, by using cell references, calculate the additional total cost. Use cells F10 and E30. In cell E33, by using cell references, calculate the additional profit. Use cells E31 and E32. Note: The total cost should be a positive value.	6
7	In cells E36-E41, perform your calculations for the situation when the size can be increased from M to L for the extra charge specified in cell E35. In cell E36, by using cell references, calculate the price for size L. Use cells E28 and E35. In cell E37, by using cell references, calculate the new amount of quantity demanded. Use cells F9, H9, and E35. In cell E38, by using cell references, calculate the additional quantity demanded. Use cells E29 and E37. In cell E39, by using cell references, calculate the additional total revenue. Use cells E35 and E38. In cell E40, by using cell references, calculate the additional total cost. Use cells F10 and E38. In cell E41, by using cell references, calculate the additional profit. Use cells E39 and E40. Note: The total cost should be a positive value.	6
8	In cell E43, by using cell references, calculate the total profit using the second degree price discrimination. Use cells E25, E33, and E41. Note: Do not use the Excel SUM function.	1
9	In cells D45 and J45, determine whether you will decide to price discriminate.	2
10	Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.	0