Question

Using the Goal Seek function in Microsoft Excel, prepare an Excel spreadsheet to answer the following questions: Warner Clothing is considering the introduction of a new baseball cap for sales by local vendors. The company has collected the following price and cost characteristics:

Sales Price............$15 per unit
Variable cost......... 3 per unit
Fixed cost.............. 42,000 per month

Required
a. What number must Warner sell per month to break even?
b. What number must Warner sell per month to make an operating profit of $30,000?

Answer 1

3,500 units

6,000 units

Step-by-step explanation:

Given:

Sales Price = $15 per unit

Variable cost = $3 per unit

Fixed cost = $42,000 per month

A. Break even point

Break even point(in units) = Total fixed cost / (Sales Price - Variable cost)

= $42,000 / ($15 - $3)

= $42,000/ $12

= 3,500 units

B. Number of sales unit

Sales unit for desired profit = (Total fixed cost + Desired profit)/ (Sales Price - Variable cost)

= ($42,000 + $30,000) / ($15 -$3)

= $72,000 / $12

= 6,000 units