{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Intro","Terms","BE 1 ","BE Chart","BE Chart  (ans)","TS & BE Chart 2","TS & BE Chart 2 (Ans)","Summary"," Source "],"not_rendered":{"charts":4,"images":4,"macros":0,"external_links":1,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Intro","index":0,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,null,null,null,null,"Creating Break Even & Target Sales Charts in Excel"]
[]
[null,null,null,null,null,"Calculate the BE point and Income targets using (X, Y) Scatter Charts"]
[null,null,null,null,null,"BE point is where the total revenue and total costs intersect."]
[null,null,null,null,null,"When sales volume is less than the break even point, the company's not selling enough to cover its expenses."]
[null,null,null,null,null,"Calculate the BE point and target income, through unit price, variable costs, and fixed costs measurements."]
[]
[null,null,null,null,null,"We will create a dynamic (X, Y) scatter chart showing the BE point, and;"]
[null,null,null,null,null,"(X, Y) scatter chart showing the sales required to achieve income targets."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,"Tim Wright"]

{"sheet":"Terms","index":1,"n_rows":19,"n_cols":7,"rows_kept":19,"cut":false,"hidden":false,"merged":[[4,6,4,8]]}
["Terms"]
["Unit Price, Variable Costs, Contribution Margin, Fixed Costs"]
[]
["Input Values in Green Cells"]
["Units ","Unit Price\n (Sales)","Unit Variable\n Cost","Total Fixed\n Costs",null,null,"Fixed Costs"]
[1000,70,50,8000]
[null,null,null,null,null,null,"Units: number of products sold"]
["Example: Calculate the \"income\" if we sold 1000 units."]
[null,null,null,null,null,null,"Unit Price (UP): Sale price of the item (Clothes, jewelry, admission tickets, electronics)."]
[null,"Units","(S + VC)","Fixed"]
["UP/Sales",{"$f":"A6","v":1000},{"$f":"B6","v":70},null,{"$f":"IFERROR(B11*C11,\"\")","v":70000},null,"Variable Costs: Are costs that change in proportion to the goods or services that a business produces."]
["Variable",{"$f":"A6","v":1000},{"$f":"C6","v":50},null,{"$f":"IFERROR(B12*C12,\"\")","v":50000}]
["CM",null,null,null,{"$f":"SUM(E11-E12)","v":20000},null,"Fixed Costs:  Are business expenses that are not dependent on the level of goods or services"]
["Fixed",null,null,{"$f":"IFERROR(D6,\"\")","v":8000},{"$f":"IFERROR(D6,\"\")","v":8000},null,"produced by the business. They tend to be time-related, such as salaries or rents being paid "]
[null,null,null,null,null,null,"per month, and are often referred to as overhead costs."]
[null,null,null,"BE Units",{"$f":"E14/(B6-C6)","v":400}]
[null,null,null,null,null,null,"Contribution Margin (CM): The difference between unit price (sales) and variable costs."]
[null,null,null,"Total Costs:",{"$f":"E12+E14","v":58000}]
[null,null,null,null,null,null,"Total Costs: Variable costs plus fixed costs."]

{"sheet":"BE 1 ","index":2,"n_rows":28,"n_cols":12,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["Input Values in Green Cells",null,null,null,null,null,null,"Input Values in Green Cells"]
["Units ","Unit Price\n (Sales)","Unit Variable\n Cost ","Total Fixed\n Costs","Target Income",null,null,"Units ","Unit Price\n (Sales)","Unit Variable\n Cost ","Total Fixed\n Costs","Target Income"]
["?",50,40,4000,0,null,null,"?",50,40,4000,70000]
[]
["Find the sales necessary to achieve the break even point given",null,null,null,null,null,null,"Find the target sales necessary to reach the target income given the"]
["the following data:",null,null,null,null,null,null,"following data:"]
[]
["Find break even point.",null,null,null,null,null,null,"Find target sales point."]
["Target Income: ",null,{"$f":"E3","v":0},null,null,null,null,"Target Income:  ",null,{"$f":"L3","v":70000}]
["Target Units: Unknown X",null,null,null,null,null,null,"Target Units: Unknown X"]
["Target Sales in Dollars: X * Unit Sale Price",null,null,null,null,null,null,"Target Sales in Dollars: X * Unit Sale Price"]
[]
["Sales","Variable","Fixed","Target",null,null,null,"Sales","Variable","Fixed","Target"]
[{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},{"$f":"C9","v":0},null,null,null,{"$f":"I3","v":50},{"$f":"J3","v":40},{"$f":"K3","v":4000},{"$f":"J9","v":70000}]
[]
["X = Units (Solve for x)",null,null,null,null,null,null,"X = Units (Solve for x)"]
[]
["Sales","(Variable)","(Fixed)","Units",null," ",null,"Sales","(Variable)","(Fixed)","Units","Target Income"]
[{"$f":"A14","v":50},{"$f":"B14","v":40},{"$f":"C14","v":4000},{"$f":"C9","v":0},null,null,null,{"$f":"H14","v":50},{"$f":"I14","v":40},{"$f":"J14","v":4000},{"$f":"J9","v":70000}]
[null,{"$f":"IFERROR(A19-B19,\"\")","v":10},{"$f":"C19","v":4000},{"$f":"C14","v":4000},null,null,null," 15x + 300,000",{"$f":"IFERROR(H19-I19,\"\")","v":10},{"$f":"J19","v":4000},{"$f":"J14","v":4000},{"$f":"J9","v":70000}]
[null,null,{"$f":"B20","v":10},{"$f":"D20","v":4000},null,null,null,null,null,{"$f":"I20","v":10},{"$f":"IFERROR(K20+L20,\"\")","v":74000}]
[null,null,"X",{"$f":"IFERROR(D21/C21,\"\")","v":400},null,null,null,null,null,"X",{"$f":"IFERROR(K21/J21,\"\")","v":7400}]
[]
[null,null,"Target Units:",{"$f":"ROUND(D22,3)","v":400},null,null,null,null,null,"Target Units:",{"$f":"ROUND(K22,3)","v":7400}]
[null,null,"Target Sales:",{"$f":"IFERROR(ROUND(D24*B3,2),\"\")","v":20000},null,null,null,null,null,"Target Sales:",{"$f":"K24*I3","v":370000}]
[]
[]
["Break even volume, where total revenue and total costs intersect."]

{"sheet":"BE Chart","index":3,"n_rows":27,"n_cols":15,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["Input Values in Green Cells",null,null,null,null,null,null,null,"Assumptions"]
["Units ","Unit Price\n (Sales)","Unit Variable\n Cost","Total Fixed\n Costs","Target Income",null,null,null,"Units Start","Units increment","Unit Price","Unit Variable Cost","Total Fixed Costs"]
["?",50,40,4000,0,null,null,null,0,70,50,40,10000]
[]
["Find the sales necessary to achieve the break even point given",null,null,null,null,null,null,"Units","Sales","Variable Cost","Contribution Margin","Fixed Costs","Total Costs"]
["the following data:"]
["Break even point (TC and S intersect)."]
[]
["Target Income:   ",null,{"$f":"E3","v":0}]
["Break Even Sales in Units: Unknown X",null,null,null,null,null,null,null,null,null,null,null,null,null,"Decreasing Fixed Costs"]
["Break Even Sales in Dollars: X * Unit Sale Price",null,null,null,null,null,null,null,null,null,null,null,null,null,"Profit is increased due to the reduction in Fixed costs."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"A reduced fixed cost lowers to the total cost line."]
["Sales","Variable","Fixed","Target",null,null,null,null,null,null,null,null,null,null,"The break even point is lowered."]
[{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},{"$f":"C9","v":0},null,null,null,null,null,null,null,null,null,null,"Results in increased profits."]
[]
["X = Units (Solve for x)",null,"BE Units = FC/(S - VC)",null,null,null,null,null,null,null,null,null,null,null,"Decreasing Variable Costs"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Reducing material and labor costs for each unit produced."]
["Sales","(Variable)","(Fixed)","Units",null,null,null,null,"Break Even x","Break Even y","Label",null,null,null,"Product produced with less time or less material."]
[{"$f":"A14","v":50},{"$f":"B14","v":40},{"$f":"C14","v":4000},0,null,null,null,null,null,null,null,null,null,null,"When the slope of the variable costs line decreases,"]
[null,{"$f":"IFERROR(A19-B19,\"\")","v":10},{"$f":"C19","v":4000},{"$f":"C14","v":4000},null,null,null,null,null,null,null,null,null,null,"the break even point is reduced."]
[null,null,{"$f":"B20","v":10},{"$f":"D20","v":4000},null,null,null,null,null,null,null,null,null,null,"Fixed costs are recovered sooner and profits are realized."]
[null,null,"X",{"$f":"IFERROR(D21/C21,\"\")","v":400},null,null,null,null,"0 = Units*(S - VC) - FC"]
[null,null,null,null,null,null,null,null,"FC = Units*(S - VC)",null,null,null,null,null,"Increasing Selling Price"]
[null,null,"Break even units:",{"$f":"ROUND(D22,3)","v":400},null,null,null,null,"Units = FC/(S - VC)",null,null,null,null,null,"Raises the selling price without lowering volume increase profits."]
[null,null,"Break even sales:",{"$f":"IFERROR(ROUND(D24*B3,2),\"\")","v":20000},null,null,null,null,null,null,null,null,null,null,"Total revenue is higher because the selling price per unit is higher."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"With same selling volume X, the company increases profits."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"And the break even volume is reduced."]

{"sheet":"BE Chart  (ans)","index":4,"n_rows":28,"n_cols":14,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["Input Values in Green Cells"]
["Units ","Unit Price\n (Sales)","Unit Variable\n Cost","Total Fixed\n Costs","Target Income",null,null,"Units Start","Units increment","Unit Price","Unit Variable Cost","Total Fixed Costs"]
["?",50,40,4000,0,null,null,0,70,{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000}]
[]
["Find the sales necessary to achieve the break even point given",null,null,null,null,null,"Units","Sales","Variable Cost","Contribution Margin","Fixed Cost","Total Costs"]
["the following data:",null,null,null,null,null,0,{"$f":"$J$3*G6","v":0},{"$f":"$K$3*G6","v":0},{"$f":"H6-I6","v":0},{"$f":"$L$3","v":4000},{"$f":"I6+K6","v":4000}]
[null,null,null,null,null,null,{"$f":"G6+$I$3","v":70},{"$f":"$J$3*G7","v":3500},{"$f":"$K$3*G7","v":2800},{"$f":"H7-I7","v":700},{"$f":"$L$3","v":4000},{"$f":"I7+K7","v":6800}]
["Find break even point.",null,null,null,null,null,140,7000,5600,1400,4000,9600]
["Target Income:   ",null,{"$f":"E3","v":0},null,null,null,210,10500,8400,2100,4000,12400]
["Break Even Sales in Units: Unknown X",null,null,null,null,null,280,14000,11200,2800,4000,15200,null,"Decreasing Fixed Costs"]
["Break Even Sales in Dollars: X * Unit Sale Price",null,null,null,null,null,350,17500,14000,3500,4000,18000,null,"Profit is increased due to the reduction in Fixed costs."]
[null,null,null,null,null,null,420,21000,16800,4200,4000,20800,null,"A reduced fixed cost lowers to the total cost line."]
["Sales","Variable","Fixed","Target",null,null,490,24500,19600,4900,4000,23600,null,"Results in increases profits."]
[{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},{"$f":"C9","v":0},null,null,560,28000,22400,5600,4000,26400,null,"The break even point is lowered."]
[null,null,null,null,null,null,630,31500,25200,6300,4000,29200]
["X = Units (Solve for x)",null,null,null,null,null,700,35000,28000,7000,4000,32000,null,"Decreasing Variable Costs"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Reduce material and labor costs for each unit produced."]
["Sales","(Variable)","(Fixed)","Units",null,null,null,"Break Even x","Break Even y","Label",null,null,null,"Produced with less time or less material."]
[{"$f":"A14","v":50},{"$f":"B14","v":40},{"$f":"C14","v":4000},0,null,null,null,{"$f":"ROUND(L3/(J3-K3),2)","v":400},{"$f":"H19*J3","v":20000},{"$f":"\"BEU approx. = \"&H19","v":"BEU approx. = 400"},null,null,null,"The slope of the variable costs line decreases."]
[null,{"$f":"IFERROR(A19-B19,\"\")","v":10},{"$f":"C19","v":4000},{"$f":"C14","v":4000},null,null,null,null,null,null,null,null,null,"The break even point is reduced."]
[null,null,{"$f":"B20","v":10},{"$f":"D20","v":4000},null,null,null,null,null,null,null,null,null,"Fixed costs are recovered sooner and profits are realized."]
[null,null,"X",{"$f":"IFERROR(D21/C21,\"\")","v":400},null,null,null,"0 = Units*(S - VC) - FC"]
[null,null,null,null,null,null,null,"FC = Units*(S - VC)",null,null,null,null,null,"Increasing Selling Price"]
[null,null,"Break even units:",{"$f":"ROUND(D22,3)","v":400},null,null,null,"Units = FC/(S - VC)",null,null,null,null,null,"Increase selling price per unit."]
[null,null,"Break even sales:",{"$f":"IFERROR(ROUND(D24*B3,2),\"\")","v":20000},null,null,null,null,null,null,null,null,null,"Raising the selling price without lowering volume increase profits."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Total revenue is higher because the selling price per unit is higher."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"With same selling volume X, the company increases profits."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"And the break even volume is reduced."]

{"sheet":"TS & BE Chart 2","index":5,"n_rows":47,"n_cols":13,"rows_kept":47,"cut":false,"hidden":false,"merged":[]}
["Input Values in Green Cells",null,null,null,null,null,"Assumptions"]
["Units ","Unit Price\n (Sales)","Unit Variable\n Cost","Total Fixed\n Costs","Target Income",null,"Time","Units\n Start","Units Increment","Unit Price","Unit Variable Cost","Total Fixed Costs","Target\nIncome"]
["?",50,40,4000,10000,null,"Month",0,200,{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},10000]
[]
["Find the sales necessary for the break-even point and the",null,null,null,null,null,"Units","Sales","Variable Cost","Contribution Margin","Fixed","Total Costs"]
["target income given the following data:"]
[]
["Find target sales point."]
["Target Income:   ",null,{"$f":"E3","v":10000}]
["Break Even Sales in Units: Unknown X"]
["Break Even Sales in Dollars: X * Unit Sale Price"]
[]
["Sales","Variable","Fixed","Target"]
[{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},{"$f":"C9","v":10000}]
[]
["X = Units (Solve for x)",null,"Units = FC/(S - VC)"]
["Units = FC/(S - VC)"]
["Sales","(Variable)","(Fixed)","Units",null,null,null,"Target Units x","Target Sales y","Label"]
[{"$f":"A14","v":50},{"$f":"B14","v":40},{"$f":"C14","v":4000},0]
[null,{"$f":"IFERROR(A19-B19,\"\")","v":10},{"$f":"C19","v":4000},{"$f":"C14","v":4000},{"$f":"D14","v":10000}]
[null,null,{"$f":"B20","v":10},{"$f":"D20+E20","v":14000},null,null,null,"BE Units\n x","BE Sales\n y","Label"]
[null,null,"X",{"$f":"IFERROR(D21/C21,\"\")","v":1400},null,null,null,{"$f":"ROUND('BE Chart  (ans)'!H19,2)","v":400},{"$f":"'BE Chart  (ans)'!I19","v":20000},{"$f":"\"BEU approx. = \"&H22","v":"BEU approx. = 400"}]
[]
[null,null,"Target sales units:",{"$f":"ROUND(D22,3)","v":1400}]
[null,null,"Target sales:",{"$f":"IFERROR(ROUND(D24*B3,2),\"\")","v":70000},null,null,null,"Units = FC/(S - VC)"]
[null,null,null,null,null,null,null,"TS = (FC + TI)/(S - VC)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Tim Wright"]

{"sheet":"TS & BE Chart 2 (Ans)","index":6,"n_rows":47,"n_cols":13,"rows_kept":47,"cut":false,"hidden":false,"merged":[]}
["Input Values in Green Cells",null,null,null,null,null,"Assumptions"]
["Units ","Unit Price\n (Sales)","Unit Variable\n Cost","Total Fixed\n Costs","Target Income",null,"Time","Units\n Start","Units Increment","Unit Price","Unit Variable Cost","Total Fixed Costs","Target\nIncome"]
["?",50,40,4000,10000,null,"Month",0,200,{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},10000]
[]
["Find the sales necessary for the break-even point and the",null,null,null,null,null,"Units","Sales","Variable Cost","Contribution Margin","Fixed","Total Costs"]
["target income given the following data:",null,null,null,null,null,{"$f":"H3","v":0},{"$f":"G6*$J$3","v":0},{"$f":"G6*$K$3","v":0},{"$f":"H6-I6","v":0},{"$f":"$L$3","v":4000},{"$f":"I6+K6","v":4000}]
[null,null,null,null,null,null,{"$f":"G6+$I$3","v":200},{"$f":"G7*$J$3","v":10000},{"$f":"G7*$K$3","v":8000},{"$f":"H7-I7","v":2000},{"$f":"$L$3","v":4000},{"$f":"I7+K7","v":12000}]
["Find target sales point.",null,null,null,null,null,{"$f":"G7+$I$3","v":400},20000,16000,4000,4000,20000]
["Target Income:   ",null,{"$f":"E3","v":10000},null,null,null,600,30000,24000,6000,4000,28000]
["Break Even Sales in Units: Unknown X",null,null,null,null,null,800,40000,32000,8000,4000,36000]
["Break Even Sales in Dollars: X * Unit Sale Price",null,null,null,null,null,1000,50000,40000,10000,4000,44000]
[null,null,null,null,null,null,1200,60000,48000,12000,4000,52000]
["Sales","Variable","Fixed","Target",null,null,1400,70000,56000,14000,4000,60000]
[{"$f":"B3","v":50},{"$f":"C3","v":40},{"$f":"D3","v":4000},{"$f":"C9","v":10000},null,null,1600,80000,64000,16000,4000,68000]
[null,null,null,null,null,null,1800,90000,72000,18000,4000,76000]
["X = Units (Solve for x)",null,"Units = FC/(S - VC)",null,null,null,2000,100000,80000,20000,4000,84000]
["Units = FC/(S - VC)"]
["Sales","(Variable)","(Fixed)","Units",null,null,null,"Target Units x","Target Sales y","Label"]
[{"$f":"A14","v":50},{"$f":"B14","v":40},{"$f":"C14","v":4000},0,null,null,null,{"$f":"(L3+M3)/(J3-K3)","v":1400},{"$f":"H19*J3","v":70000},{"$f":"\"TS approx. = \"&H19","v":"TS approx. = 1400"}]
[null,{"$f":"IFERROR(A19-B19,\"\")","v":10},{"$f":"C19","v":4000},{"$f":"C14","v":4000},{"$f":"D14","v":10000}]
[null,null,{"$f":"B20","v":10},{"$f":"D20+E20","v":14000},null,null,null,"BE Units\n x","BE Sales\n y","Label"]
[null,null,"X",{"$f":"IFERROR(D21/C21,\"\")","v":1400},null,null,null,{"$f":"ROUND('BE Chart  (ans)'!H19,2)","v":400},{"$f":"'BE Chart  (ans)'!I19","v":20000},{"$f":"\"BEU approx. = \"&H22","v":"BEU approx. = 400"}]
[]
[null,null,"Target sales units:",{"$f":"ROUND(D22,3)","v":1400}]
[null,null,"Target sales:",{"$f":"IFERROR(ROUND(D24*B3,2),\"\")","v":70000},null,null,null,"Units = FC/(S - VC)"]
[null,null,null,null,null,null,null,"TS = (FC + TI)/(S - VC)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Tim Wright"]

{"sheet":"Summary","index":7,"n_rows":72,"n_cols":6,"rows_kept":72,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Created Break Even & Target Sales Charts in Excel"]
[]
[null,"This tutorial showed how to calculate the BE point, Sales and Income targets,"]
[null,"using unit price, variable costs, and fixed costs measurements."]
[null,"We created an (X, Y) Scatter Chart showing the BE point."]
[null,"Then we created an (X, Y) Scatter Chart showing the sales targets required"]
[null,"to reach income targets."]
[]
[]
[null,"Check it out!!!"]
[]
[null,"How to Pass A Microsoft Excel Test - By Tim Wright"]
[]
[null,null,null,null,"Published December 2016"]
[]
[null,null,null,null,"Publisher:","https://www.createspace.com/6607014"]
[null,null,null,null,"Amazon:","http://mobile-train.com/passtest/"]
[]
[]
[null,null,null,null,"Website:","www.timwright-hr/"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Tim Wright"]

{"sheet":" Source ","index":8,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"Tim Wright"]
[null,"E-Mail: ","twright@icloud.com"]
[null,"LinkedIn","https://www.linkedin.com/in/timwrighthr"]