Claire Swanson operates a snow cone cart that she takes to outdoor fairs and festivals on the weekends. She makes the snow cones from ice shavings topped with flavored sugar syrup. She currently sells each snow cone for $5.00. The variable cost for each snow cone is $2.00. The monthly fixed costs for operating the snow cone cart are $1,800. Currently, Claire is selling 750 snow cones a month. Claire is evaluating the impact of changing the snow cone selling price and would like to earn at least $800 of operating income (profit) per month. 

Requirements 

1. Create a profit calculation in Excel. Use formulas to calculate the contribution margin per unit, total contribution margin, and operating income. Also add $1 less than the target profit to a cell below the profit calculation. 

2. Next to the profit calculation you created in Requirement 1, create a data table in Excel using the What-if Analysis tool. Use volumes of 900 units down to 350 units in 50-unit increments and sales prices of $5.00 up to $6.25 in $0.25 increments. Use conditional formatting to shade the cells in the data table that meet or exceed Claire’s target profit. 

3. Using the data table you created in Requirement 2, answer the following questions: 

a. How many snow cones does Claire need to sell in a month to break even if the selling price remains at $5.00 per snow cone? 

b. How many snow cones does Claire need to sell to break even if she increases the selling price to $6.00 per snow cone? 

c. What would Claire's monthly profit be if she keeps the selling price at $5.00 and sells 850 snow cones? 

d. How many snow cones does Claire need to sell to reach her monthly target profit if she increases the selling price to $6.00 per snow cone? 

e. What would Claire’s monthly profit be if she increases the selling price to $6.25 and sells 800 snow cones?

Answer rating: 100% (QA)

Ill provide you with instructions on how to do it 1 Profit Calculation In an Excel sheet create the ... View the full answer