Question
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
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?
Step by Step Solution
3.34 Rating (157 Votes )
There are 3 Steps involved in it
Step: 1
Ill provide you with instructions on how to do it 1 Profit Calculation In an Excel sheet create the ...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started