Hello
I have a budget for 2019 and want to distribute it across the year based on historical data (2018) With positive numbers on the numerator and demoniator it is very simple to do this. But when there is a negative from a month or for the year I am having some serious issues. These are not the real numbers but the same type of scenarios. I have tried every google search out there and nothing is getting me close
[TABLE="width: 591"]
<colgroup><col width="87" style="width: 65pt;"><col width="89" style="width: 67pt;"><col width="87" style="width: 65pt;"><col width="136" style="width: 102pt;"><col width="87" style="width: 65pt;"><col width="105" style="width: 79pt;"></colgroup><tbody>[TR]
[TD="class: xl68, width: 591, colspan: 6, align: center"]Location 1 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2019 Budget Goal [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Atcual [/TD]
[TD]Percent [/TD]
[TD="class: xl64"] $ 30,000.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-18[/TD]
[TD="class: xl64"] $ (5,009.49)[/TD]
[TD="class: xl66, align: right"]-325%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jan-19[/TD]
[TD="class: xl67"] $ (97,405.94)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-18[/TD]
[TD="class: xl64"] $ (382.51)[/TD]
[TD="class: xl66, align: right"]-25%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Feb-19[/TD]
[TD="class: xl67"] $ (7,437.63)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-18[/TD]
[TD="class: xl64"] $ 4,004.80 [/TD]
[TD="class: xl66, align: right"]260%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Mar-19[/TD]
[TD="class: xl67"] $ 77,870.46 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-18[/TD]
[TD="class: xl64"] $ (1,776.89)[/TD]
[TD="class: xl66, align: right"]-115%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Apr-19[/TD]
[TD="class: xl67"] $ (34,550.35)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-18[/TD]
[TD="class: xl64"] $ (1,975.52)[/TD]
[TD="class: xl66, align: right"]-128%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]May-19[/TD]
[TD="class: xl67"] $ (38,412.57)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-18[/TD]
[TD="class: xl64"] $ 2,379.27 [/TD]
[TD="class: xl66, align: right"]154%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jun-19[/TD]
[TD="class: xl67"] $ 46,263.20 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-18[/TD]
[TD="class: xl64"] $ (771.14)[/TD]
[TD="class: xl66, align: right"]-50%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jul-19[/TD]
[TD="class: xl67"] $ (14,994.26)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-18[/TD]
[TD="class: xl64"] $ (439.14)[/TD]
[TD="class: xl66, align: right"]-28%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Aug-19[/TD]
[TD="class: xl67"] $ (8,538.76)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-18[/TD]
[TD="class: xl64"] $ (1,258.08)[/TD]
[TD="class: xl66, align: right"]-82%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Sep-19[/TD]
[TD="class: xl67"] $ (24,462.46)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-18[/TD]
[TD="class: xl64"] $ 3,367.95 [/TD]
[TD="class: xl66, align: right"]218%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Oct-19[/TD]
[TD="class: xl67"] $ 65,487.37 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-18[/TD]
[TD="class: xl64"] $ 543.73 [/TD]
[TD="class: xl66, align: right"]35%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Nov-19[/TD]
[TD="class: xl67"] $ 10,572.44 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-18[/TD]
[TD="class: xl64"] $ 2,859.89 [/TD]
[TD="class: xl66, align: right"]185%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Dec-19[/TD]
[TD="class: xl67"] $ 55,608.51 [/TD]
[/TR]
[TR]
[TD]Total [/TD]
[TD="class: xl64"] $ 1,542.87 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Atcual [/TD]
[TD]Percent [/TD]
[TD]2019 Budget Goal [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-18[/TD]
[TD="class: xl63"] (3,747.70)[/TD]
[TD="class: xl66, align: right"]6%[/TD]
[TD="class: xl64"] $ 38,212.44 [/TD]
[TD="class: xl65, align: right"]Jan-19[/TD]
[TD="class: xl67"] $ 2,282.12 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-18[/TD]
[TD="class: xl63"] (12,889.16)[/TD]
[TD="class: xl66, align: right"]21%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Feb-19[/TD]
[TD="class: xl67"] $ 6,161.91 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-18[/TD]
[TD="class: xl63"] (4,517.71)[/TD]
[TD="class: xl66, align: right"]7%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Mar-19[/TD]
[TD="class: xl67"] $ 2,159.78 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-18[/TD]
[TD="class: xl63"] (2,269.94)[/TD]
[TD="class: xl66, align: right"]4%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Apr-19[/TD]
[TD="class: xl67"] $ 1,085.19 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-18[/TD]
[TD="class: xl63"] 1,676.12 [/TD]
[TD="class: xl66, align: right"]-3%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]May-19[/TD]
[TD="class: xl67"] $ (801.30)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-18[/TD]
[TD="class: xl63"] 4,900.92 [/TD]
[TD="class: xl66, align: right"]-8%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jun-19[/TD]
[TD="class: xl67"] $ (2,342.98)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-18[/TD]
[TD="class: xl63"] (6,631.59)[/TD]
[TD="class: xl66, align: right"]11%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jul-19[/TD]
[TD="class: xl67"] $ 3,170.36 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-18[/TD]
[TD="class: xl63"] (1,078.97)[/TD]
[TD="class: xl66, align: right"]2%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Aug-19[/TD]
[TD="class: xl67"] $ 515.82 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-18[/TD]
[TD="class: xl63"] (10,606.59)[/TD]
[TD="class: xl66, align: right"]17%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Sep-19[/TD]
[TD="class: xl67"] $ 5,070.68 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-18[/TD]
[TD="class: xl63"] (16,889.97)[/TD]
[TD="class: xl66, align: right"]27%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Oct-19[/TD]
[TD="class: xl67"] $ 8,074.57 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-18[/TD]
[TD="class: xl63"] (6,170.90)[/TD]
[TD="class: xl66, align: right"]10%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Nov-19[/TD]
[TD="class: xl67"] $ 2,950.12 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-18[/TD]
[TD="class: xl63"] (4,526.96)[/TD]
[TD="class: xl66, align: right"]7%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Dec-19[/TD]
[TD="class: xl67"] $ 2,164.20 [/TD]
[/TR]
[TR]
[TD]Total [/TD]
[TD="class: xl63"] (62,752.45)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a budget for 2019 and want to distribute it across the year based on historical data (2018) With positive numbers on the numerator and demoniator it is very simple to do this. But when there is a negative from a month or for the year I am having some serious issues. These are not the real numbers but the same type of scenarios. I have tried every google search out there and nothing is getting me close
[TABLE="width: 591"]
<colgroup><col width="87" style="width: 65pt;"><col width="89" style="width: 67pt;"><col width="87" style="width: 65pt;"><col width="136" style="width: 102pt;"><col width="87" style="width: 65pt;"><col width="105" style="width: 79pt;"></colgroup><tbody>[TR]
[TD="class: xl68, width: 591, colspan: 6, align: center"]Location 1 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2019 Budget Goal [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Atcual [/TD]
[TD]Percent [/TD]
[TD="class: xl64"] $ 30,000.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-18[/TD]
[TD="class: xl64"] $ (5,009.49)[/TD]
[TD="class: xl66, align: right"]-325%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jan-19[/TD]
[TD="class: xl67"] $ (97,405.94)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-18[/TD]
[TD="class: xl64"] $ (382.51)[/TD]
[TD="class: xl66, align: right"]-25%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Feb-19[/TD]
[TD="class: xl67"] $ (7,437.63)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-18[/TD]
[TD="class: xl64"] $ 4,004.80 [/TD]
[TD="class: xl66, align: right"]260%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Mar-19[/TD]
[TD="class: xl67"] $ 77,870.46 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-18[/TD]
[TD="class: xl64"] $ (1,776.89)[/TD]
[TD="class: xl66, align: right"]-115%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Apr-19[/TD]
[TD="class: xl67"] $ (34,550.35)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-18[/TD]
[TD="class: xl64"] $ (1,975.52)[/TD]
[TD="class: xl66, align: right"]-128%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]May-19[/TD]
[TD="class: xl67"] $ (38,412.57)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-18[/TD]
[TD="class: xl64"] $ 2,379.27 [/TD]
[TD="class: xl66, align: right"]154%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jun-19[/TD]
[TD="class: xl67"] $ 46,263.20 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-18[/TD]
[TD="class: xl64"] $ (771.14)[/TD]
[TD="class: xl66, align: right"]-50%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jul-19[/TD]
[TD="class: xl67"] $ (14,994.26)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-18[/TD]
[TD="class: xl64"] $ (439.14)[/TD]
[TD="class: xl66, align: right"]-28%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Aug-19[/TD]
[TD="class: xl67"] $ (8,538.76)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-18[/TD]
[TD="class: xl64"] $ (1,258.08)[/TD]
[TD="class: xl66, align: right"]-82%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Sep-19[/TD]
[TD="class: xl67"] $ (24,462.46)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-18[/TD]
[TD="class: xl64"] $ 3,367.95 [/TD]
[TD="class: xl66, align: right"]218%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Oct-19[/TD]
[TD="class: xl67"] $ 65,487.37 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-18[/TD]
[TD="class: xl64"] $ 543.73 [/TD]
[TD="class: xl66, align: right"]35%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Nov-19[/TD]
[TD="class: xl67"] $ 10,572.44 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-18[/TD]
[TD="class: xl64"] $ 2,859.89 [/TD]
[TD="class: xl66, align: right"]185%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Dec-19[/TD]
[TD="class: xl67"] $ 55,608.51 [/TD]
[/TR]
[TR]
[TD]Total [/TD]
[TD="class: xl64"] $ 1,542.87 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Atcual [/TD]
[TD]Percent [/TD]
[TD]2019 Budget Goal [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-18[/TD]
[TD="class: xl63"] (3,747.70)[/TD]
[TD="class: xl66, align: right"]6%[/TD]
[TD="class: xl64"] $ 38,212.44 [/TD]
[TD="class: xl65, align: right"]Jan-19[/TD]
[TD="class: xl67"] $ 2,282.12 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-18[/TD]
[TD="class: xl63"] (12,889.16)[/TD]
[TD="class: xl66, align: right"]21%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Feb-19[/TD]
[TD="class: xl67"] $ 6,161.91 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-18[/TD]
[TD="class: xl63"] (4,517.71)[/TD]
[TD="class: xl66, align: right"]7%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Mar-19[/TD]
[TD="class: xl67"] $ 2,159.78 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-18[/TD]
[TD="class: xl63"] (2,269.94)[/TD]
[TD="class: xl66, align: right"]4%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Apr-19[/TD]
[TD="class: xl67"] $ 1,085.19 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-18[/TD]
[TD="class: xl63"] 1,676.12 [/TD]
[TD="class: xl66, align: right"]-3%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]May-19[/TD]
[TD="class: xl67"] $ (801.30)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-18[/TD]
[TD="class: xl63"] 4,900.92 [/TD]
[TD="class: xl66, align: right"]-8%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jun-19[/TD]
[TD="class: xl67"] $ (2,342.98)[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-18[/TD]
[TD="class: xl63"] (6,631.59)[/TD]
[TD="class: xl66, align: right"]11%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Jul-19[/TD]
[TD="class: xl67"] $ 3,170.36 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-18[/TD]
[TD="class: xl63"] (1,078.97)[/TD]
[TD="class: xl66, align: right"]2%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Aug-19[/TD]
[TD="class: xl67"] $ 515.82 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-18[/TD]
[TD="class: xl63"] (10,606.59)[/TD]
[TD="class: xl66, align: right"]17%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Sep-19[/TD]
[TD="class: xl67"] $ 5,070.68 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-18[/TD]
[TD="class: xl63"] (16,889.97)[/TD]
[TD="class: xl66, align: right"]27%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Oct-19[/TD]
[TD="class: xl67"] $ 8,074.57 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-18[/TD]
[TD="class: xl63"] (6,170.90)[/TD]
[TD="class: xl66, align: right"]10%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Nov-19[/TD]
[TD="class: xl67"] $ 2,950.12 [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-18[/TD]
[TD="class: xl63"] (4,526.96)[/TD]
[TD="class: xl66, align: right"]7%[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]Dec-19[/TD]
[TD="class: xl67"] $ 2,164.20 [/TD]
[/TR]
[TR]
[TD]Total [/TD]
[TD="class: xl63"] (62,752.45)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]