Metrics Calc - Migrating From Formulas to VBA....Scripting.Dictionary Perhaps?

Hi All,

Attached is my Dummy Data: Review Worksheet - Dummy Data v2 0.xlsx

My Input data is in the "Report" tab.

And my desired output is "Unit Performance" tab.

Currently I have employed formulas to get to the desired results.

However, the limitation is that the input data keeps expanding both in terms of months as well as accounts. And therefore extending the formula is error prone and cumbersome.

Can someone suggest how I can achieve this result directly via VBA?

Guidelines as below:

Computing Revenue --> Sum of "Total Revenue" column by Account and by Month

Computing C&B --> Total of Sums of columns "Salary Total", "Sub con Total", "CXU Buffer cost", "CXD Buffer cost", "Group Buffer cost" divided by Revenue computed above as a percentage rounded to zero.

Computing EBIDTA --> Sum of "EBIDTA" column by Account and by Month divided by Revenue computed above as a percentage rounded to zero.

Quarter results are quarter totals divided by quarter revenues.

I was trying to do this by using dictionary objects before I posted my question. But I am a VBA newbie and could not get far. Any other approaches are welcome.

PS: Moderators, please not that I am cross posting here as I did not get any response on another forum. Link is: http://www.ozgrid.com/forum/showthread.php?t=182349
Please alert me if I am in conflict of any forum rules and I will comply.

Thanks.

Can't you just use a pivot table? Seems much easier

Thanks Kyle...I tried that. But was not able to replicate the result.

For example, Travel% is a function of Overall travel cost to revenue against a unique customer in a given month.

How can i achieve via a pivot?

Thanks.

You can do it with Calculated Fields in your PT:

PHP Code: 

Solve Order    Field    Formula
1    C&B    =(ROUND(('Salary Total'+'Sub con Total'+'CXU Buffer cost'+'CXD Buffer cost'+'Group Buffer cost')/(SumRev%),0)/100)
2    SumRev    =ROUND('Total Revenue',2 )
3    Travel    =(('Visa Exp'+Foreign+Inland)/SumRev%)/100
4    Allocated Summary    =(Allocated/SumRev %)/100
5    SG&A    =('Total SGNA'/SumRev %)/100
6    EBIDTA Summary    =(EBIDTA/SumRev %)/100 


The variance is due to the rounding and where it occurs, have a look at the attached:

Thanks Kyle,

This is a great arrow to have in my quiver!

Simple and easy to replicate.

I will apply it to my Real data and check results.

Cheers!

Hi Kyle,

Is there any way to have control on grand totals?

I ask bcos I wish to have the Quarterly numbers after every quarter rather thanhave a running total at the end. Thanks.

Thanks.

Hi Kyle,

Request you to let me know if this is possible(to have grand totals for every quarter, i.e after every 3 months starting April.

Google/Forum search turned up a blank.

Thanks.

Yes, just group your date field by quarter