Supply Chain Operation
JOHNJACK00
Breakeven.xlsxPart #1-Supplier A
| Break Even Analysis for Supplier A | Variable Cost | $26.48 | |||||||
| Selling Price Per Unit | $32.99 | ||||||||
| Fixed Cost | $4,500,000 | ||||||||
| Units | Fixed Costs | Variable Costs | Total Cost | Total Revenue | BEPU | 691,244 | |||
| 0 | $4,500,000 | $0 | $4,500,000 | $0 | BEP$ | $22,804,147.47 | |||
| 50,000 | $4,500,000 | $1,324,000 | $5,824,000 | $1,649,500 | |||||
| 100,000 | $4,500,000 | $2,648,000 | $7,148,000 | $3,299,000 | |||||
| 150,000 | $4,500,000 | $3,972,000 | $8,472,000 | $4,948,500 | |||||
| 200,000 | $4,500,000 | $5,296,000 | $9,796,000 | $6,598,000 | |||||
| 250,000 | $4,500,000 | $6,620,000 | $11,120,000 | $8,247,500 | |||||
| 300,000 | $4,500,000 | $7,944,000 | $12,444,000 | $9,897,000 | |||||
| 350,000 | $4,500,000 | $9,268,000 | $13,768,000 | $11,546,500 | |||||
| 400,000 | $4,500,000 | $10,592,000 | $15,092,000 | $13,196,000 | |||||
| 450,000 | $4,500,000 | $11,916,000 | $16,416,000 | $14,845,500 | |||||
| 500,000 | $4,500,000 | $13,240,000 | $17,740,000 | $16,495,000 | |||||
| 550,000 | $4,500,000 | $14,564,000 | $19,064,000 | $18,144,500 | |||||
| 600,000 | $4,500,000 | $15,888,000 | $20,388,000 | $19,794,000 | |||||
| 650,000 | $4,500,000 | $17,212,000 | $21,712,000 | $21,443,500 | |||||
| 700,000 | $4,500,000 | $18,536,000 | $23,036,000 | $23,093,000 | |||||
| 750,000 | $4,500,000 | $19,860,000 | $24,360,000 | $24,742,500 | |||||
| 800,000 | $4,500,000 | $21,184,000 | $25,684,000 | $26,392,000 | |||||
| 850,000 | $4,500,000 | $22,508,000 | $27,008,000 | $28,041,500 | |||||
| 900,000 | $4,500,000 | $23,832,000 | $28,332,000 | $29,691,000 | |||||
| 950,000 | $4,500,000 | $25,156,000 | $29,656,000 | $31,340,500 | |||||
| 1,000,000 | $4,500,000 | $26,480,000 | $30,980,000 | $32,990,000 | |||||
| 1,050,000 | $4,500,000 | $27,804,000 | $32,304,000 | $34,639,500 | |||||
| 1,100,000 | $4,500,000 | $29,128,000 | $33,628,000 | $36,289,000 | |||||
| 1,150,000 | $4,500,000 | $30,452,000 | $34,952,000 | $37,938,500 | |||||
| 1,200,000 | $4,500,000 | $31,776,000 | $36,276,000 | $39,588,000 | |||||
| 1,250,000 | $4,500,000 | $33,100,000 | $37,600,000 | $41,237,500 | |||||
| 1,300,000 | $4,500,000 | $34,424,000 | $38,924,000 | $42,887,000 | |||||
Insert formulas to calculate break-even point in units and break-even point in dollars
Break Even Analysis for Sales A
Variable Costs 600000 650000 700000 15888000 17212000 18536000 Total Cost 600000 650000 700000 20388000 21712000 23036000 Total Revenue 600000 650000 700000 19794000 21443500 23093000 Fixed Costs 600000 650000 700000 4500000 4500000 4500000
Units Sold
Dollars ($)
Fixed Costs ($)
Enter correct data here
Enter # of units sold to format the x-axis and calculate costs.
Enter fixed costs in each row
Enter formula to calculate variable costs
Enter formula to calculate total costs
Enter formula to calculate total revenue
Part #1-Supplier B
| Break Even Analysis for Supplier B | Variable Cost | $26.22 | |||||||
| Selling Price Per Unit | $32.99 | ||||||||
| Fixed Cost | $4,500,000 | ||||||||
| Units | Fixed Costs | Variable Costs | Total Cost | Total Revenue | BEPU | 664,697 | |||
| 0 | $4,500,000 | $0 | $4,500,000 | $0 | BEP$ | $21,928,360.41 | |||
| 50,000 | $4,500,000 | $1,311,000 | $5,811,000 | $1,649,500 | |||||
| 100,000 | $4,500,000 | $2,622,000 | $7,122,000 | $3,299,000 | |||||
| 150,000 | $4,500,000 | $3,933,000 | $8,433,000 | $4,948,500 | |||||
| 200,000 | $4,500,000 | $5,244,000 | $9,744,000 | $6,598,000 | |||||
| 250,000 | $4,500,000 | $6,555,000 | $11,055,000 | $8,247,500 | |||||
| 300,000 | $4,500,000 | $7,866,000 | $12,366,000 | $9,897,000 | |||||
| 350,000 | $4,500,000 | $9,177,000 | $13,677,000 | $11,546,500 | |||||
| 400,000 | $4,500,000 | $10,488,000 | $14,988,000 | $13,196,000 | |||||
| 450,000 | $4,500,000 | $11,799,000 | $16,299,000 | $14,845,500 | |||||
| 500,000 | $4,500,000 | $13,110,000 | $17,610,000 | $16,495,000 | |||||
| 550,000 | $4,500,000 | $14,421,000 | $18,921,000 | $18,144,500 | |||||
| 600,000 | $4,500,000 | $15,732,000 | $20,232,000 | $19,794,000 | |||||
| 650,000 | $4,500,000 | $17,043,000 | $21,543,000 | $21,443,500 | |||||
| 700,000 | $4,500,000 | $18,354,000 | $22,854,000 | $23,093,000 | |||||
| 750,000 | $4,500,000 | $19,665,000 | $24,165,000 | $24,742,500 | |||||
| 800,000 | $4,500,000 | $20,976,000 | $25,476,000 | $26,392,000 | |||||
| 850,000 | $4,500,000 | $22,287,000 | $26,787,000 | $28,041,500 | |||||
| 900,000 | $4,500,000 | $23,598,000 | $28,098,000 | $29,691,000 | |||||
| 950,000 | $4,500,000 | $24,909,000 | $29,409,000 | $31,340,500 | |||||
| 1,000,000 | $4,500,000 | $26,220,000 | $30,720,000 | $32,990,000 | |||||
| 1,050,000 | $4,500,000 | $27,531,000 | $32,031,000 | $34,639,500 | |||||
| 1,100,000 | $4,500,000 | $28,842,000 | $33,342,000 | $36,289,000 | |||||
| 1,150,000 | $4,500,000 | $30,153,000 | $34,653,000 | $37,938,500 | |||||
| 1,200,000 | $4,500,000 | $31,464,000 | $35,964,000 | $39,588,000 | |||||
| 1,250,000 | $4,500,000 | $32,775,000 | $37,275,000 | $41,237,500 | |||||
| 1,300,000 | $4,500,000 | $34,086,000 | $38,586,000 | $42,887,000 | |||||
Break Even Analysis for Supplier B
Variable Costs 500000 550000 600000 650000 700000 750000 800000 13110000 14421000 15732000 17043000 18354000 19665000 20976000 Total Cost 500000 550000 600000 650000 700000 750000 800000 17610000 18921000 20232000 21543000 22854000 24165000 25476000 Total Revenue 500000 550000 600000 650000 700000 750000 800000 16495000.000000002 18144500 19794000 21443500 23093000 24742500 26392000 Fixed Costs 500000 550000 600000 650000 700000 750000 800000 4500000 4500000 4500000 4500000 4500000 4500000 4500000
Units
Dollars ($)
Fixed Costs ($)
Part #1-Supplier C
| Break Even Analysis for Supplier C | Variable Cost | $25.57 | |||||||
| Selling Price Per Unit | $32.99 | ||||||||
| Fixed Cost | $4,500,000 | ||||||||
| Units | Fixed Costs | Variable Costs | Total Cost | Total Revenue | BEPU | 606,469 | |||
| 0 | $4,500,000 | $0 | $4,500,000 | $0 | BEP$ | $20,007,412.40 | |||
| 50,000 | $4,500,000 | $1,278,500 | $5,778,500 | $1,649,500 | |||||
| 100,000 | $4,500,000 | $2,557,000 | $7,057,000 | $3,299,000 | |||||
| 150,000 | $4,500,000 | $3,835,500 | $8,335,500 | $4,948,500 | |||||
| 200,000 | $4,500,000 | $5,114,000 | $9,614,000 | $6,598,000 | |||||
| 250,000 | $4,500,000 | $6,392,500 | $10,892,500 | $8,247,500 | |||||
| 300,000 | $4,500,000 | $7,671,000 | $12,171,000 | $9,897,000 | |||||
| 350,000 | $4,500,000 | $8,949,500 | $13,449,500 | $11,546,500 | |||||
| 400,000 | $4,500,000 | $10,228,000 | $14,728,000 | $13,196,000 | |||||
| 450,000 | $4,500,000 | $11,506,500 | $16,006,500 | $14,845,500 | |||||
| 500,000 | $4,500,000 | $12,785,000 | $17,285,000 | $16,495,000 | |||||
| 550,000 | $4,500,000 | $14,063,500 | $18,563,500 | $18,144,500 | |||||
| 600,000 | $4,500,000 | $15,342,000 | $19,842,000 | $19,794,000 | |||||
| 650,000 | $4,500,000 | $16,620,500 | $21,120,500 | $21,443,500 | |||||
| 700,000 | $4,500,000 | $17,899,000 | $22,399,000 | $23,093,000 | |||||
| 750,000 | $4,500,000 | $19,177,500 | $23,677,500 | $24,742,500 | |||||
| 800,000 | $4,500,000 | $20,456,000 | $24,956,000 | $26,392,000 | |||||
| 850,000 | $4,500,000 | $21,734,500 | $26,234,500 | $28,041,500 | |||||
| 900,000 | $4,500,000 | $23,013,000 | $27,513,000 | $29,691,000 | |||||
| 950,000 | $4,500,000 | $24,291,500 | $28,791,500 | $31,340,500 | |||||
| 1,000,000 | $4,500,000 | $25,570,000 | $30,070,000 | $32,990,000 | |||||
| 1,050,000 | $4,500,000 | $26,848,500 | $31,348,500 | $34,639,500 | |||||
| 1,100,000 | $4,500,000 | $28,127,000 | $32,627,000 | $36,289,000 | |||||
| 1,150,000 | $4,500,000 | $29,405,500 | $33,905,500 | $37,938,500 | |||||
| 1,200,000 | $4,500,000 | $30,684,000 | $35,184,000 | $39,588,000 | |||||
| 1,250,000 | $4,500,000 | $31,962,500 | $36,462,500 | $41,237,500 | |||||
| 1,300,000 | $4,500,000 | $33,241,000 | $37,741,000 | $42,887,000 | |||||
Break Even Analysis for Supplier C
Variable Costs 500000 550000 600000 650000 700000 750000 12785000 14063500 15342000 16620500 17899000 19177500 Total Cost 500000 550000 600000 650000 700000 750000 17285000 18563500 19842000 21120500 22399000 23677500 Total Revenue 500000 550000 600000 650000 700000 750000 16495000.000000002 18144500 19794000 21443500 23093000 24742500 Units 500000 550000 600000 650000 700000 750000 500000 550000 600000 650000 700000 750000 Fixed Costs 11 12 13 14 15 16 4500000 4500000 4500000 4500000 4500000 4500000
Units
Dollars ($)
Fixed Costs ($)
Part #1-Supplier D
| Break Even Analysis for Supplier D | Variable Cost | $25.48 | |||||||
| Selling Price Per Unit | $32.99 | ||||||||
| Fixed Cost | $4,500,000 | ||||||||
| Units | Fixed Costs | Variable Costs | Total Cost | Total Revenue | BEPU | 599,201 | |||
| 0 | $4,500,000 | $0 | $4,500,000 | $0 | BEP$ | $19,767,643.14 | |||
| 50,000 | $4,500,000 | $1,274,000 | $5,774,000 | $1,649,500 | |||||
| 100,000 | $4,500,000 | $2,548,000 | $7,048,000 | $3,299,000 | |||||
| 150,000 | $4,500,000 | $3,822,000 | $8,322,000 | $4,948,500 | |||||
| 200,000 | $4,500,000 | $5,096,000 | $9,596,000 | $6,598,000 | |||||
| 250,000 | $4,500,000 | $6,370,000 | $10,870,000 | $8,247,500 | |||||
| 300,000 | $4,500,000 | $7,644,000 | $12,144,000 | $9,897,000 | |||||
| 350,000 | $4,500,000 | $8,918,000 | $13,418,000 | $11,546,500 | |||||
| 400,000 | $4,500,000 | $10,192,000 | $14,692,000 | $13,196,000 | |||||
| 450,000 | $4,500,000 | $11,466,000 | $15,966,000 | $14,845,500 | |||||
| 500,000 | $4,500,000 | $12,740,000 | $17,240,000 | $16,495,000 | |||||
| 550,000 | $4,500,000 | $14,014,000 | $18,514,000 | $18,144,500 | |||||
| 600,000 | $4,500,000 | $15,288,000 | $19,788,000 | $19,794,000 | |||||
| 650,000 | $4,500,000 | $16,562,000 | $21,062,000 | $21,443,500 | |||||
| 700,000 | $4,500,000 | $17,836,000 | $22,336,000 | $23,093,000 | |||||
| 750,000 | $4,500,000 | $19,110,000 | $23,610,000 | $24,742,500 | |||||
| 800,000 | $4,500,000 | $20,384,000 | $24,884,000 | $26,392,000 | |||||
| 850,000 | $4,500,000 | $21,658,000 | $26,158,000 | $28,041,500 | |||||
| 900,000 | $4,500,000 | $22,932,000 | $27,432,000 | $29,691,000 | |||||
| 950,000 | $4,500,000 | $24,206,000 | $28,706,000 | $31,340,500 | |||||
| 1,000,000 | $4,500,000 | $25,480,000 | $29,980,000 | $32,990,000 | |||||
| 1,050,000 | $4,500,000 | $26,754,000 | $31,254,000 | $34,639,500 | |||||
| 1,100,000 | $4,500,000 | $28,028,000 | $32,528,000 | $36,289,000 | |||||
| 1,150,000 | $4,500,000 | $29,302,000 | $33,802,000 | $37,938,500 | |||||
| 1,200,000 | $4,500,000 | $30,576,000 | $35,076,000 | $39,588,000 | |||||
| 1,250,000 | $4,500,000 | $31,850,000 | $36,350,000 | $41,237,500 | |||||
| 1,300,000 | $4,500,000 | $33,124,000 | $37,624,000 | $42,887,000 | |||||
Break Even Analysis for Supplier D
Variable Costs 450,000 500,000 550,000 600,000 650,000 700,000 750,000 1,300,000 12740000 14014000 15288000 16562000 17836000 19110000 20384000 Total Cost 450,000 500,000 550,000 600,000 650,000 700,000 750,000 1,300,000 17240000 18514000 19788000 21062000 22336000 23610000 24884000 Total Revenue 450,000 500,000 550,000 600,000 650,000 700,000 750,000 1,300,000 16495000.000000002 18144500 19794000 21443500 23093000 24742500 26392000 Units 450,000 500,000 550,000 600,000 650,000 700,000 750,000 1,300,000 500000 550000 600000 650000 700000 750000 800000 Fixed Costs 11 12 13 14 15 16 17 4500000 4500000 4500000 4500000 4500000 4500000 4500000
Units
Dollars ($)
Fixed Costs ($)
Part #1-Supplier E
| Break Even Analysis for Supplier E | Variable Cost | $25.32 | |||||||
| Selling Price Per Unit | $32.99 | ||||||||
| Fixed Cost | $4,500,000 | ||||||||
| Units | Fixed Costs | Variable Costs | Total Cost | Total Revenue | BEPU | 586,701 | |||
| 0 | $4,500,000 | $0 | $4,500,000 | $0 | BEP$ | $19,355,280.31 | |||
| 50,000 | $4,500,000 | $1,266,000 | $5,766,000 | $1,649,500 | |||||
| 100,000 | $4,500,000 | $2,532,000 | $7,032,000 | $3,299,000 | |||||
| 150,000 | $4,500,000 | $3,798,000 | $8,298,000 | $4,948,500 | |||||
| 200,000 | $4,500,000 | $5,064,000 | $9,564,000 | $6,598,000 | |||||
| 250,000 | $4,500,000 | $6,330,000 | $10,830,000 | $8,247,500 | |||||
| 300,000 | $4,500,000 | $7,596,000 | $12,096,000 | $9,897,000 | |||||
| 350,000 | $4,500,000 | $8,862,000 | $13,362,000 | $11,546,500 | |||||
| 400,000 | $4,500,000 | $10,128,000 | $14,628,000 | $13,196,000 | |||||
| 450,000 | $4,500,000 | $11,394,000 | $15,894,000 | $14,845,500 | |||||
| 500,000 | $4,500,000 | $12,660,000 | $17,160,000 | $16,495,000 | |||||
| 550,000 | $4,500,000 | $13,926,000 | $18,426,000 | $18,144,500 | |||||
| 600,000 | $4,500,000 | $15,192,000 | $19,692,000 | $19,794,000 | |||||
| 650,000 | $4,500,000 | $16,458,000 | $20,958,000 | $21,443,500 | |||||
| 700,000 | $4,500,000 | $17,724,000 | $22,224,000 | $23,093,000 | |||||
| 750,000 | $4,500,000 | $18,990,000 | $23,490,000 | $24,742,500 | |||||
| 800,000 | $4,500,000 | $20,256,000 | $24,756,000 | $26,392,000 | |||||
| 850,000 | $4,500,000 | $21,522,000 | $26,022,000 | $28,041,500 | |||||
| 900,000 | $4,500,000 | $22,788,000 | $27,288,000 | $29,691,000 | |||||
| 950,000 | $4,500,000 | $24,054,000 | $28,554,000 | $31,340,500 | |||||
| 1,000,000 | $4,500,000 | $25,320,000 | $29,820,000 | $32,990,000 | |||||
| 1,050,000 | $4,500,000 | $26,586,000 | $31,086,000 | $34,639,500 | |||||
| 1,100,000 | $4,500,000 | $27,852,000 | $32,352,000 | $36,289,000 | |||||
| 1,150,000 | $4,500,000 | $29,118,000 | $33,618,000 | $37,938,500 | |||||
| 1,200,000 | $4,500,000 | $30,384,000 | $34,884,000 | $39,588,000 | |||||
| 1,250,000 | $4,500,000 | $31,650,000 | $36,150,000 | $41,237,500 | |||||
| 1,300,000 | $4,500,000 | $32,916,000 | $37,416,000 | $42,887,000 | |||||
Break Even Analysis for Supplier E
Variable Costs 450000 500000 550000 600000 650000 11394000 12660000 13926000 15192000 16458000 Total Cost 450000 500000 550000 600000 650000 15894000 17160000 18426000 19692000 20958000 Total Revenue 450000 500000 550000 600000 650000 14845500 16495000.000000002 18144500 19794000 21443500 Units 450000 500000 550000 600000 650000 450000 500000 550000 600000 650000 Fixed Costs 10 11 12 13 14 4500000 4500000 4500000 4500000 4500000
Units
Dollars (Cost)
Fixed costs
Part #2 Analysis
| Break Even Analysis for New Costs | Variable Cost | $26.37 | |||||||
| Selling Price Per Unit | $35.00 | ||||||||
| Fixed Cost | $4,500,000 | ||||||||
| Units | Fixed Costs | Variable Costs | Total Cost | Total Revenue | BEPU | 521,437 | |||
| 0 | $4,500,000 | $0 | $4,500,000 | $0 | BEP$ | $18,250,289.69 | |||
| 50,000 | $4,500,000 | $1,318,500 | $5,818,500 | $1,750,000 | |||||
| 100,000 | $4,500,000 | $2,637,000 | $7,137,000 | $3,500,000 | |||||
| 150,000 | $4,500,000 | $3,955,500 | $8,455,500 | $5,250,000 | |||||
| 200,000 | $4,500,000 | $5,274,000 | $9,774,000 | $7,000,000 | |||||
| 250,000 | $4,500,000 | $6,592,500 | $11,092,500 | $8,750,000 | |||||
| 300,000 | $4,500,000 | $7,911,000 | $12,411,000 | $10,500,000 | |||||
| 350,000 | $4,500,000 | $9,229,500 | $13,729,500 | $12,250,000 | |||||
| 400,000 | $4,500,000 | $10,548,000 | $15,048,000 | $14,000,000 | |||||
| 450,000 | $4,500,000 | $11,866,500 | $16,366,500 | $15,750,000 | |||||
| 500,000 | $4,500,000 | $13,185,000 | $17,685,000 | $17,500,000 | |||||
| 550,000 | $4,500,000 | $14,503,500 | $19,003,500 | $19,250,000 | |||||
| 600,000 | $4,500,000 | $15,822,000 | $20,322,000 | $21,000,000 | |||||
| 650,000 | $4,500,000 | $17,140,500 | $21,640,500 | $22,750,000 | |||||
| 700,000 | $4,500,000 | $18,459,000 | $22,959,000 | $24,500,000 | |||||
| 750,000 | $4,500,000 | $19,777,500 | $24,277,500 | $26,250,000 | |||||
| 800,000 | $4,500,000 | $21,096,000 | $25,596,000 | $28,000,000 | |||||
| 850,000 | $4,500,000 | $22,414,500 | $26,914,500 | $29,750,000 | |||||
| 900,000 | $4,500,000 | $23,733,000 | $28,233,000 | $31,500,000 | |||||
| 950,000 | $4,500,000 | $25,051,500 | $29,551,500 | $33,250,000 | |||||
| 1,000,000 | $4,500,000 | $26,370,000 | $30,870,000 | $35,000,000 | |||||
| 1,050,000 | $4,500,000 | $27,688,500 | $32,188,500 | $36,750,000 | |||||
| 1,100,000 | $4,500,000 | $29,007,000 | $33,507,000 | $38,500,000 | |||||
| 1,150,000 | $4,500,000 | $30,325,500 | $34,825,500 | $40,250,000 | |||||
| 1,200,000 | $4,500,000 | $31,644,000 | $36,144,000 | $42,000,000 | |||||
| 1,250,000 | $4,500,000 | $32,962,500 | $37,462,500 | $43,750,000 | |||||
| 1,300,000 | $4,500,000 | $34,281,000 | $38,781,000 | $45,500,000 | |||||
Beak Even Analysis Revised
Variable Costs 400000 450000 500000 550000 600000 10548000 11866500 13185000 14503500 15822000 Total Cost 400000 450000 500000 550000 600000 15048000 16366500 17685000 19003500 20322000 Total Revenue 400000 450000 500000 550000 600000 14000000 15750000 17500000 19250000 21000000 Units 400000 450000 500000 550000 600000 400000 450000 500000 550000 600000 Fixed Costs 9 10 11 12 13 4500000 4500000 4500000 4500000 4500000
Units Sold
Dollars ($)
Fixed Costs ($)
Conclusions
| Part #1 Conclusion | ||
| Indicate which of the five payment options has the lowest break-even point and provide the break-even point in dollars and units. | ||
| Option: | Supplier E | |
| BEPU: | 586,701 | |
| BEP$: | $19,355,280 | |
| Part #2 Conclusion | ||
| Indicate the new break-even point in dollars and units. | ||
| BEPU: | 521,437 | |
| BEP$: | $ 18,250,290 | |
| Part #3 Reflection | ||
| Discuss whether the new BEP increased or decreased. Since the selling price increased and the variable costs increased, explain why the BEPU behaved as you have observed. | ||
| The new Break Even Point decreased. This was brought about by the increase in selling price which was way higher than the increase in variable costs. When the contribution margin increases, the break even point reduces. | ||
