I need some help with a report that I am trying to do in Access. Hopefully what I've written below helps explain what I am trying to do. I need help with Column F and a formula for K. There are about 10 different reports, all with the same formulas, for 8 different sales representatives. Currently the reports are done in Excel where every month that I do them I have to change the formulas based on what month it is, making it very time consuming. I am trying to automate the reports.
E = running sum of planned sales
F = running sum of actual sales, UNLESS month has not yet been reported, (ie September), in which case it would equal E*(1+H), where E is for the prior month. So for September the formula would be E(August)*(1+H(August))
H = (F-E)/E
K = sum through August (will vary depending on the month)
Monthly Cumulative
Sales Percentage A) Plan (B) Actual (C) Variance (D) Plan (E) Total Actual (F) Total Variance (G) Total Variance % (H)
6% Oct $ 1,226,000.00 $ 1,245,799.00 $ 19,799.00 $ 1,226,000.00 $ 1,245,799.00 $ 19,799.00 2%
7% Nov $ 1,302,000.00 $ 1,373,082.00 $ 71,082.00 $ 2,528,000.00 $ 2,618,881.00 $ 90,881.00 4%
7% Dec $ 1,400,000.00 $ 1,410,409.00 $ 10,409.00 $ 3,928,000.00 $ 4,029,290.00 $ 101,290.00 3%
6% Jan $ 1,188,000.00 $ 1,256,457.00 $ 68,457.00 $ 5,116,000.00 $ 5,285,747.00 $ 169,747.00 3%
8% Feb $ 1,480,000.00 $ 1,491,149.00 $ 11,149.00 $ 6,596,000.00 $ 6,776,896.00 $ 180,896.00 3%
9% Mar $ 1,690,000.00 $ 1,743,403.00 $ 53,403.00 $ 8,286,000.00 $ 8,520,299.00 $ 234,299.00 3%
9% Apr $ 1,670,000.00 $ 1,485,244.00 $ (184,756.00) $ 9,956,000.00 $10,005,543.00 $ 49,543.00 0%
11% May $ 2,112,000.00 $ 1,887,037.00 $ (224,963.00) $12,068,000.00 $11,892,580.00 $ (175,420.00) -1%
10% Jun $ 1,978,000.00 $ 2,077,943.00 $ 99,943.00 $14,046,000.00 $13,970,523.00 $ (75,477.00) -1%
10% Jul $ 1,851,000.00 $ 1,833,893.00 $ (17,107.00) $15,897,000.00 $15,804,416.00 $ (92,584.00) -1%
8% Aug $ 1,599,000.00 $ 1,669,233.00 $ 70,233.00 $17,496,000.00 $17,473,649.00 $ (22,351.00) 0%
7% Sep $ 1,384,000.00 $ - $18,880,000.00 $18,855,880.95 $ (24,119.05) 0%
100% $18,880,000.00 $17,473,649.00 $(1,406,351.00)
Percentage Sold (J) 93% Pace % (L) 100%
Planned Percentage (K) 93% Pace (M) $18,855,880.95
E = running sum of planned sales
F = running sum of actual sales, UNLESS month has not yet been reported, (ie September), in which case it would equal E*(1+H), where E is for the prior month. So for September the formula would be E(August)*(1+H(August))
H = (F-E)/E
K = sum through August (will vary depending on the month)
Monthly Cumulative
Sales Percentage A) Plan (B) Actual (C) Variance (D) Plan (E) Total Actual (F) Total Variance (G) Total Variance % (H)
6% Oct $ 1,226,000.00 $ 1,245,799.00 $ 19,799.00 $ 1,226,000.00 $ 1,245,799.00 $ 19,799.00 2%
7% Nov $ 1,302,000.00 $ 1,373,082.00 $ 71,082.00 $ 2,528,000.00 $ 2,618,881.00 $ 90,881.00 4%
7% Dec $ 1,400,000.00 $ 1,410,409.00 $ 10,409.00 $ 3,928,000.00 $ 4,029,290.00 $ 101,290.00 3%
6% Jan $ 1,188,000.00 $ 1,256,457.00 $ 68,457.00 $ 5,116,000.00 $ 5,285,747.00 $ 169,747.00 3%
8% Feb $ 1,480,000.00 $ 1,491,149.00 $ 11,149.00 $ 6,596,000.00 $ 6,776,896.00 $ 180,896.00 3%
9% Mar $ 1,690,000.00 $ 1,743,403.00 $ 53,403.00 $ 8,286,000.00 $ 8,520,299.00 $ 234,299.00 3%
9% Apr $ 1,670,000.00 $ 1,485,244.00 $ (184,756.00) $ 9,956,000.00 $10,005,543.00 $ 49,543.00 0%
11% May $ 2,112,000.00 $ 1,887,037.00 $ (224,963.00) $12,068,000.00 $11,892,580.00 $ (175,420.00) -1%
10% Jun $ 1,978,000.00 $ 2,077,943.00 $ 99,943.00 $14,046,000.00 $13,970,523.00 $ (75,477.00) -1%
10% Jul $ 1,851,000.00 $ 1,833,893.00 $ (17,107.00) $15,897,000.00 $15,804,416.00 $ (92,584.00) -1%
8% Aug $ 1,599,000.00 $ 1,669,233.00 $ 70,233.00 $17,496,000.00 $17,473,649.00 $ (22,351.00) 0%
7% Sep $ 1,384,000.00 $ - $18,880,000.00 $18,855,880.95 $ (24,119.05) 0%
100% $18,880,000.00 $17,473,649.00 $(1,406,351.00)
Percentage Sold (J) 93% Pace % (L) 100%
Planned Percentage (K) 93% Pace (M) $18,855,880.95
