You manufacture WIDGETs. Your old machine broke.
You can buy Machine #1 for $ 5000. Using machine #1, each widget will cost $ 10 to make.
You can buy Machine #2 for $ 2000. Using machine #2, each widget will cost $ 15 to make.
I would like to determine the breakeven point for A and B using TWO different methods.
On sheet1, we will use GOAL-SEEK. On sheet2, we will use graphs.
Here is an outline for sheet1:
|
A
|
B
|
|
# of units
|
|
|
Cost to make B2 units using machine #1
|
|
|
Cost to make B2 units using machine #2
|
|
|
Breakeven Point
|
|
|
|
|
Hint: BEP is where both costs are equal. In general, when P=Q, then P-Q = 0.
On sheet2, create the following grid
|
A
|
B
|
C
|
D
|
|
|
# units
|
Machine #1 cost
|
Machine #2 cost
|
Which is cheaper
|
|
|
300
|
|
|
|
|
|
400
|
|
|
|
|
|
500
|
|
|
|
|
|
600
|
|
|
|
|
|
700
|
|
|
|
|
|
800
|
|
|
|
|
Compute the cost to produce 300 units using machine #1 and then by machine #2, and using an =IF statement, tell me which machine is cheaper.
Do the same for 400, 500, 600, 700, 800 units.
As you can see, #2 is cheaper for smaller quantities and #1 is cheaper for larger quantities.
Please create a line graph showing the two costs intersecting. Please have quantity as the x axis and dollars as the y axis.