I have a financial scorecard spreadsheet that calculates different financial ratios. For each ratio, I would like to graph the calculated result as a plotted point on top of a bar graph that shows the ranges of acceptable performance and unacceptable performance.
As an example, one of the calculations is the current ratio. It is calculated by taking current assets divided by current liabilities. A sample calculation might look like this: $160,500 / $75,000 = 2.14. For every dollar of short-term debt this business can cover it 2.14 times with its liquid assets.
I wish to plot this result along a horizontal bar graph. The bar graph has three ranges…a RED warning range from .6 to 1.3, a YELLOW cautionary range from 1.31 to 2 and a GREEN good range from 2.1 to 2.7.
The resulting graph would look like this:
Scorecard Graph.jpg
I have figured out how to make the bar graph with the three corresponding colored ranges and have also figured out how to start the scale of the graph at .6 and show the corresponding tick marks a 1.3, 2.0 and 2.7.
I cannot however figure out how to plot the resulting 2.14 from the above calculation. I am also curious to know if I can “peg” the resulting answer at .6 even if it is less than .6 or at 2.7 even if the answer is above 2.7…I am thinking this involves an “if” statement…it may be over my head.
Am I approaching this the correct way and what is the best way to plot the calculated result on the bar graph?
Thank You in advance!
Dave Dietz
Bookmarks