Major charting challenge - need help

[Gav !!]

HI Folks,

I hope I have posted right to the charting group as I usually post to the
programming group but I am not not sure whether this needs to use code yet
and it involves difficult charts so .....

I need to know if this can be done and how it would be possible and / or
with or without code. Have tried starting in a few different ways and keep
hitting a brick wall when the series has to change for the new weekly
totals.

The scenario

I have 10 areas i want to monitor on worksheet1 a3:a12 call them 1 to 10,
across the top b1:ba1 I have a week number 1 to 52, and b2:ba2 I have the
actual date weekly starting with (week 1) 02/08/2003.
The quarters will end at weeks 13,26,39,52. For each quarter I have a
quarterly budget, say it is $1,500,000.00
As the actual figure is updated weekly under each column heading it is
charted ( i have already preconfigured the ranges that will be included in
the charts, so they basically stay blank until a figure is put in). I have
one that charts a yearly buget vs actual and a quarterly budget vs actual.

What I am trying to achieve is to have a chart which is updated weekly
showing the increase or decrease of what is needed to achieve the goal. So
each week would become a new series sort of like a stacked bar and the
actual being a line flowing through this somehow. I was think of trying to
seperate each of these 10 areas one on a new worksheet each having say the
top 3 lines replicated over from the main worksheet, and then the weekly
dates replicated down the side and grabbing the actual data from the main
worksheet and going across the page. A formula subtracting the sum of the
actuals from the quarterly budget figure then divided by each of the
remaining empty weeks. Each of the remaining cells would show this new
amount required.
So the chart would effectively show what has happened as the actuals are
input and what needs to be generated over the remaining weeks to achieve the
goal.
I hope this makes sense I am at mind blur stage trying to bring this all
together, and have lost my way, am I chasing my tail or can this be done.

On each individual worksheet I was thinking of having the quarterly budget
for each area in a cell, then one formula something like budget - sum of
actuals input / coutblank of actuals left in quarter, and then an if
statement checking against the main worksheet to see if there is an actual
to be brought across and if not use the figure from the above formula to
show what is required to achieve the goal. This would happen down the page
for each week, and then be charted based on what the current week is. Then
somehow I have to get the chart to recognise what week it is up to and chart
that weeks series of the individual areas worksheet to show the actuals and
the remaining amounts required.

Sorry for the length but just wanted to try and explain it right and make
sure I have it right in my head.

All help is much appreciated.

Thanks in advance.

Regards.

Gav !!

 

[Jon Peltier]

Hi Gav -

That's quite a complicated description, and I'm not sure I got it all.
Sounds like you need two series for each of your 10 areas: one tracks
actuals up to now then stops, the other starts now with projected
requirements to the end of the chart. You will need a projection data
range for each area, and have worksheet formulas which recompute the
projections as new actuals are accrued.

Looking over your description, I think you have left out this second
series and data range for each area.

- Jon

 

[Tushar Mehta]

I interpreted your question differently than Jon, but I still am not
sure what you want to plot. Nonetheless, here's what I would do to get
to a reasonable point from where you can decide what you want.

First, leave the data in a single table (list). Most software that can
aid your analysis assume the data are in a format compatible with a
relational table setup. For my test, I had the data below (and
hopefully, the alignment will make it obvious what is in what column).

Column A had the areaID. Column B had the WeekNumber. The budget was
in column C. Using this layout allows for much greater flexibility in
setting budgets for seasonal industries. The actual values were in
Column D. The cumulative actuals were in column E. The difference
between the target and the cumulative total were in column F.

Row 1 had the header (titles). The numbers started in row 2.

Area Week Budget Actual Cumul-Q Delta
1 1 1500000 121779 121779 1378221
1 2 36698 158477 1341523
1 3 60492 218969 1281031
1 4 140713 359682 1140318
1 5 190597 550279 949721
1 6 99718 649997 850003
1 7 179490 829487 670513
1 8 22060 851547 648453
1 9 98952 950499 549501
1 10 50175 1000674 499326
1 11 16284 1016958 483042
1 12 174966 1191924 308076
1 13 1200000 190269 190269 1009731
1 14 75916 266185 933815
1 15 20700 286885 913115
1 16 145913 432798 767202
1 17 59910 492708 707292
1 18 26199 518907 681093
1 19 148068 666975 533025
1 20 19162 686137 513863
1 21 40505 726642 473358
1 22 48091 774733 425267
1 23 151326 926059 273941
1 24 18661 944720 255280
1 25 117137 1061857 138143
1 26 54558 1116415 83585
1 27 1800000 99346 99346 1700654

With E2 selected, create the following named formula (Insert | Name >
Define...)

CurrQStart
=MAX(IF(NOT(ISBLANK(Sheet1!$C$2:$C2)),ROW(Sheet1!$C$2:$C2))-1)

Note the use of absolute-partial addresses. That is why it is
important that E2 be selected before entering the formula.

In E2, enter the array formula
=SUM(OFFSET($D$1,CurrQStart,0,ROW()-CurrQStart,1))
In F2, enter the array formula
=N(OFFSET($C$1,CurrQStart,0,1,1))-E2

Note that you could skip the cumulative actuals column (col. E) and get
the Delta values directly with
=N(OFFSET($C$1,CurrQStart,0,1,1))-SUM(OFFSET($D$1,CurrQStart,0,ROW()-
CurrQStart,1)) But, there is a lot of value in getting the cumulative
values.

Now, plot whatever you want. I plotted the cumulative and delta in
stacked column on the primary axis and set the cumulative pattern to
'none. The actuals for the week were plotted as a line on the
secondary axis. Since I had a few deltas that were negative -- a very
natural state of affairs for any comparison of forecast vs. actuals --
the primary y-axis had negative values, while the secondary y-axis did
not. So, I adjusted the axis min/max. values to ensure that the zeros
on the primary and secondary axis were aligned with the x-axis.

Even if I say so myself, nice graph.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Gav !!]

Jon and Tushar

Thanks for your replies, I apologise for making this confusing. What I am
trying to achieve is : Imagine that you have a querterly budget. Break this
figure down into weeks so that you now have a weekly budget or goal to
achieve. Now after the first week whatever your actual figure is that is
achieved, for say the other 11 weeks in the quarter you will have either
more to achieve each week for the next eleven weeks if you didn't reach your
first weeks budget or less to achieve each week if you did achieve it. What
I am trying to do is almost like a goal setting type chart showing what is
required and then put the actuals in as a line over top as they happen. So
say after 8 weeks and budget was $10k per month to achieve qtr results (
120K over 12 weeks eg..).but after 8 weeks you had only achieved 50K instead
of 80k then this means that now 70k / 4 weeks is now the required weekly
goal/budget to achieve your quarterly result. The line ( actuals ) will just
chart nothing but that as they happen, whereas I was hoping to show the
required weekly target as a sort of bar or dot showing the increase or
decrease of what is required over each week. So if the week dates were your
x axis and Y was $'s you would have a smoothed line flowing from week date 1
to week date 12 ( actuals ). At week date one you would have one plot or bar
value as there is only one value to achieve, but as you get to week 12 you
would have 12 series, one for each week fluctuation up or down.

I hope this makes more sense.

I have had another crack at it and have sort of got a version of it, but
still doesn't look exactly like I want it. I did have to create 2 series for
every date one showing actuals and one showing the remaining target figure

Thanks again for your reply it is much appreciated and any further
assistance is greatly appreciated.

 

[Jon Peltier]

I'll share my private response to Gav with the group.

I put week numbers 1 to 13 into E4:Q4
I put the quarterly budget into B5
In cells E5:Q5 I put constant (level) amounts, using the formula =$B$5/13
I reserve cells E6:Q6 for actual weekly numbers
Cell B6 has the total to date, using =SUM(E6:Q6)
Cell B7 has the balance
Cells E7:Q7 have the following formula, which splits the balance evenly
among the remaining weeks:
=IF(E6<>"",NA(),$B7/(13-COUNT($E$6:$Q$6)))
As the actuals fill up E6:Q6, these numbers adjust.

I put some labels into certain cells.
A5: Budget
A6: Accrued
A7: Balance
D5: Constant
D6: Actual
D7: Target
D4: [blank cell]

I made a simple line chart using the range D4:Q7, which gave me a
Constant line at 1/13 the budget, an Actual line from week 1 to week i,
and a Target line from week i to week 13.

I think our friend Gav merely needs to apply this reasoning to his
larger bit of data.

- Jon

 

[Gav !!]

Thanks Jon

I was getting closer but that definately looks like the solution I am after,
I will have a crack at it now.

Once again thank you all for your help.

Regards

Gav !!

I'll share my private response to Gav with the group.

I put week numbers 1 to 13 into E4:Q4
I put the quarterly budget into B5
In cells E5:Q5 I put constant (level) amounts, using the formula =$B$5/13
I reserve cells E6:Q6 for actual weekly numbers
Cell B6 has the total to date, using =SUM(E6:Q6)
Cell B7 has the balance
Cells E7:Q7 have the following formula, which splits the balance evenly
among the remaining weeks:
=IF(E6<>"",NA(),$B7/(13-COUNT($E$6:$Q$6)))
As the actuals fill up E6:Q6, these numbers adjust.

I put some labels into certain cells.
A5: Budget
A6: Accrued
A7: Balance
D5: Constant
D6: Actual
D7: Target
D4: [blank cell]

I made a simple line chart using the range D4:Q7, which gave me a
Constant line at 1/13 the budget, an Actual line from week 1 to week i,
and a Target line from week i to week 13.

I think our friend Gav merely needs to apply this reasoning to his
larger bit of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Jon and Tushar

Thanks for your replies, I apologise for making this confusing. What I am
trying to achieve is : Imagine that you have a querterly budget. Break this
figure down into weeks so that you now have a weekly budget or goal to
achieve. Now after the first week whatever your actual figure is that is
achieved, for say the other 11 weeks in the quarter you will have either
more to achieve each week for the next eleven weeks if you didn't reach your
first weeks budget or less to achieve each week if you did achieve it. What
I am trying to do is almost like a goal setting type chart showing what is
required and then put the actuals in as a line over top as they happen. So
say after 8 weeks and budget was $10k per month to achieve qtr results (
120K over 12 weeks eg..).but after 8 weeks you had only achieved 50K instead
of 80k then this means that now 70k / 4 weeks is now the required weekly
goal/budget to achieve your quarterly result. The line ( actuals ) will just
chart nothing but that as they happen, whereas I was hoping to show the
required weekly target as a sort of bar or dot showing the increase or
decrease of what is required over each week. So if the week dates were your
x axis and Y was $'s you would have a smoothed line flowing from week date 1
to week date 12 ( actuals ). At week date one you would have one plot or bar
value as there is only one value to achieve, but as you get to week 12 you
would have 12 series, one for each week fluctuation up or down.

I hope this makes more sense.

I have had another crack at it and have sort of got a version of it, but
still doesn't look exactly like I want it. I did have to create 2 series for
every date one showing actuals and one showing the remaining target figure

Thanks again for your reply it is much appreciated and any further
assistance is greatly appreciated.