Excel: My Mac Excel graph needs tweaking

My graph needs a bit of help, but first the most obvious errors...

(Excel file below)

Graph

1) the colors for the legend on the right side are in the wrong order. Any idea why?
2) The X-Axis shows correct prices, but I would like them on the hash marks

then, the questions that are about taste.

This is a graph for non-technical people. How does it look?

ThreeYearCosts.xlsx

I think my title is klutsy

Is this better?
Three Year TotalCost
for Four Different Transaction Rates
in $5,000 Monthly Increments

And how about the X-Axis title?

If this can not be very clear for the Experts on EE, it will be very confusing for the target audience.

Thanks.

Here is the Excel file

ThreeYearCosts.xlsx
newbiewebSr. Software EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Order of legend entries

Right click on the graph and choose Select Data. In the left hand pane you will see the series titles, select one and then in the header bar there are up and down arrows for changing the order.

Axis labels

Right click on the Axis and choose Format Axis. A pane will appear on the right hand side of the screen, part way down there are two options "On tick marks" and "Between tick marks"; you want the first one.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Not sure on your calculations though.

Is 3%/4%/5%/6% a monthly increase or annual increase?

Either way needs to be compounded. A 3% annual increase on $5,000 for 3 years gives $5,464

This is 5000 x (1.03^3)    (^ = to the power of)

If it is monthly then the compounded would be 5000 x (1.03^36) which is $14,491 which I don't think is what you're expecting.

If its an annual increase of 3% that will be applied monthly then you need to divide the 3% by 12:

=5000 x (1+(0.03/12)^36)
0
newbiewebSr. Software EngineerAuthor Commented:
I know how to change the X-Axis name, I just wanted opinions of whether they should be renamed: Graph title, X and Y axis titles...

As far as the rates...

Those are the rates that a merchant pays to the credit card companies to process the credit cards. So, each month, 3% is taken from their credit card charges, which can be $10K per month.

The three year cost for this would be:

$10K X 3% = $300 X 36 = $10,800

I am not considering the time value of money, but would be happy to if you had an easy formula I could insert into Excel. This would put the three year savings into todays dollars.
0
Rob HensonFinance AnalystCommented:
Your column F calcs are referring to column C rather than column A.

I know how to change the X-Axis name, I just wanted opinions of whether they should be renamed: Graph title, X and Y axis titles...
Point 2 in your question was asking about alignment of labels which is what I was showing how to do.

$10K X 3.5% = $350 X 36 = $12,600
Am I missing something, you don't have 3.5% option in your table.

Considering time value of money can get quite complicated; Net Present Value (NPV), Future Value (FV), Index Rate of Return (IRR) etc etc.. That's not my area of expertise. I had misunderstood what the charge related to so can be just:

 =Value * Charge Rate * Periods

How does that show what savings will be made? That is just the total of charges at a specific rate for the 3 year period for a level of card charges.
0
newbiewebSr. Software EngineerAuthor Commented:
Sorry for the confusion.

I changed the 3.5% to be 3% so you must have seen the first version of that post.

Yes, I just want the total cost of a monthly transaction X 36. That is my goal with this chart.

And thanks for the good find on the calculation error on the final column.

I may make another post about the time value of money.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.