Forecast Accuracy

[LarryM13]

Hello all,

I would greatly appreciate if you can help me put a formula together to display the % accurate for the following forecast and actual results.

Please note these are random figures

<TABLE style="WIDTH: 739pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=986 border=0><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" span=2 width=108><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" span=4 width=108><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=108 height=16> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=108>Jan-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>Feb-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=108>Mar-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=108>Apr-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=108>May-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=108>Jun-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>Jul-10</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=108>Aug-10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>Actual</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$10,000 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$8,000 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>($9,000)</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$5,000 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>($60,000)</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>($57,000)</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>($45,000)</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>($100,000)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>Forecast</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$15,000 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$5,000 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$5,000 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>($9,000)</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>($57,000)</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>($60,000)</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>($1,000)</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$5,000 </TD></TR></TBODY></TABLE>

Thank you very much in advance!

 

[Matty]

Welcome to the Board...

Maybe like:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 85px"><COL style="WIDTH: 59px"><COL style="WIDTH: 50px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"></TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Jan-10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Feb-10</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Actual</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">10000</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">8000</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Forecast</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">15000</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">5000</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Accuracy (%)</TD><TD style="TEXT-ALIGN: right">66.67%</TD><TD style="TEXT-ALIGN: right">40.00%</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B4</TD><TD>=1-ABS(B2-B3)/B3</TD></TR><TR><TD>C4</TD><TD>=1-ABS(C2-C3)/C3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
With this logic, your forecast accuracy is hit harder when you under forecast (over sell).

Does this help?

Matty

 

[LarryM13]

Welcome to the Board...

Maybe like:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 85px"><COL style="WIDTH: 59px"><COL style="WIDTH: 50px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: right">Jan-10</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: right">Feb-10</TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Actual</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: right">10000</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: right">8000</TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Forecast</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: right">15000</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; TEXT-ALIGN: right">5000</TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Accuracy (%)</TD><TD style="TEXT-ALIGN: right">66.67%</TD><TD style="TEXT-ALIGN: right">40.00%</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B4</TD><TD>=1-ABS(B2-B3)/B3</TD></TR><TR><TD>C4</TD><TD>=1-ABS(C2-C3)/C3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
With this logic, your forecast accuracy is hit harder when you under forecast (over sell).

Does this help?

Matty

Hi Matt,

I understand what you are saying above, but that only works when forecast and actual are both positive.

Look what happens when we introduce negatives into the equation
<TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=530 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" span=2 width=54><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=3 width=61><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl69 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" width=64 height=18>

</TD><TD class=xl70 id=td_post_2400399 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 43pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=57>10-Jan</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=50>10-Feb</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=54>10-Mar</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=54>10-Apr</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=61>10-May</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=61>10-Jun</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=61>10-Jul</TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8 1pt solid; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" align=right width=68>10-Aug</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl72 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" width=64 height=18>Actual</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 43pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=57>$10,000 </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 38pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=50>$8,000 </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=54>($9,000)</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=54>$5,000 </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=61>($60,000)</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=61>($57,000)</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=61>($45,000)</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 51pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #efefef" width=68>($100,000)</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl73 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #efefef" width=64 height=18>Forecast</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 43pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=57>$15,000 </TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 38pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=50>$5,000 </TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=54>$5,000 </TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 41pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=54>($9,000)</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=61>($57,000)</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=61>($60,000)</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 46pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=61>($1,000)</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 51pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=68>$5,000 </TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>67%

</TD><TD class=xl79 id=td_post_2400399 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>40%</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>-180%</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>256%</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>105%</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>105%</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4500%</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>-2000%</TD></TR></TBODY></TABLE>

(I apologize for not knowing how to paste in excel cells)

With the above, how can you be negative 180% accurate in March? Or for that matter how can you be 4500% accuate in July. Accuracy should be from 0% to 100%

 

[Matty]

Hi Larry,

I did notice the negative () values in your data, but I put this down to the random factor you mentioned as I could quite understand why you would forecast a negative value (or have a negative actual for that matter).

Could you detail the results you'd expect to see give the Forecast and Actual values posted and I'll see whether I can help further.

Matty

 

[West Man]

An accuracy of 0% would seem to imply that a worse estimate is not possible. This is in itself an impossibility. So if 0% is not possible to compute, how can we determine how close to or far away from 0% a given estimate might be?

 

[mikerickson]

How about
=MIN(ABS(B2/B3),ABS(B3/B2))

there are some odd features of this.
If projection = 10, then actual = 5 and actual = 20 will both return 50%

I think that MIN(ABS(IF(B3*B2>0,B3,B2-B3)/B2), ABS(B2/IF(B3*B2>0,B3,B2-B3)))
Will adapt to negative projections/actuals.

 

[tusharm]

How would you define accuracy so that it is a number between 0 and 1?

{snip} Accuracy should be from 0% to 100%

 

[LarryM13]

Hi Larry,

I did notice the negative () values in your data, but I put this down to the random factor you mentioned as I could quite understand why you would forecast a negative value (or have a negative actual for that matter).

Could you detail the results you'd expect to see give the Forecast and Actual values posted and I'll see whether I can help further.

Matty

Its financial data..some months you make money and some you don't

 

[LarryM13]

How would you define accuracy so that it is a number between 0 and 1?

I guess I meant between 1% and 100% accurate

 

[tusharm]

Fine. Define what it means to be 1% accurate.

I guess I meant between 1% and 100% accurate