Charting Problem (in plural) and Breakeven Calculation

hip2b2

Board Regular
Joined
May 5, 2003
Messages
135
Office Version
  1. 2019
Platform
  1. Windows
It's been a while since the last time I've done any charting in Excel 2019 and the problems I am having are mostly really basic.
  1. I can't add axis titles, I know there must be a way to do so I just can't find it
  2. I am able to change the Y-axis scale, but is there a way to change the X-Axis scale without changing the data?
  3. There must be some way that I can calculate the intersection of "Revenue" and "Variable Costs" and show that on the chart as both a data point and vertical and horizontal lines???
Here is the spreadsheet

BEP2xlsm.xlsm
ABCDEF
1Break Even Analysis
2Sales Volume18,000Units
3Fixed Costs12,000$
4Average Variable Costs1.2$
5Unit Sales Price3$
6Contribution Margin1.8$
7BEP sales Volume6,667Units
8BEP20,000$
9
10
11
12
13
14Units/annumFixed CostsVariable CostsTotal CostsRevenue
15012,000012,0000
161,00012,0001,20013,2003,000the Fixed costs values are constant and equal to C3 (= $C$3),
172,00012,0002,40014,4006,000the Variable costs values = Average variable costs * Units (= $C$4 * A11),
183,00012,0003,60015,6009,000the Total costs values = Fixed costs + Variable costs (= B11 + C11),
194,00012,0004,80016,80012,000the Revenue values = Unit sale price * Units (= $C$5 * A11).
205,00012,0006,00018,00015,000
216,00012,0007,20019,20018,000
227,00012,0008,40020,40021,000
238,00012,0009,60021,60024,000
249,00012,00010,80022,80027,000
2510,00012,00012,00024,00030,000
2611,00012,00013,20025,20033,000
2712,00012,00014,40026,40036,000
2813,00012,00015,60027,60039,000
2914,00012,00016,80028,80042,000
3015,00012,00018,00030,00045,000
3116,00012,00019,20031,20048,000
3217,00012,00020,40032,40051,000
3318,00012,00021,60033,60054,000
Data
Cell Formulas
RangeFormula
B6B6=B5-B4
B7B7=ROUND(B3/B6,0)
B8B8=B3/((B5-B4)/B5)
B15:B33B15=$B$3
D15:D33D15=B15+C15
C16:C33C16=$B$4*A16
E16:E33E16=$B$5*A16



Thanks in advance for any help
 

Attachments

  • 2021-03-25_21h11_07.png
    2021-03-25_21h11_07.png
    30.1 KB · Views: 12

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I finally found the solution to Axis Titles (Hooray for me!!).
 
Upvote 0
I got it. PM me if you need help on this.
 

Attachments

  • 2021-03-26_09h47_40.png
    2021-03-26_09h47_40.png
    47.8 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top