Need Help With Monthly vs Quarterly Actuals

[hsuperman]

Hi All,

I am working with two sets of data: one is quarterly revenue, and the other is monthly revenue.
The quarterly revenue has the revenue by three criteria: the type of product, the company that bought it, and the agency that the company went through
However, the monthly revenue only has: the company that bought it, and the agency that the company went through
I need to be able to track the monthly revenue by all three criteria.

The issue I am running into is that a company would spend multiple times in a quarter on different products, sometimes through different agencies, and I need to sum the correct lines up to flag the monthly revenue by the product.

Please let me know if you need more information, or if this wasn't clear.
Would really appreciate any help, been trying to figure this out for a couple days.

 

[KenDue]

If I understand your description, you have quarterly information that you want to now break down by month. While you could consolidate monthly information into quarterly, I fail to see how you could accurately break down quarterly information into monthly, unless the quarterly information has multiple line items that are dated individually.

 

[hsuperman]

If I understand your description, you have quarterly information that you want to now break down by month. While you could consolidate monthly information into quarterly, I fail to see how you could accurately break down quarterly information into monthly, unless the quarterly information has multiple line items that are dated individually.

https://docs.google.com/spreadsheets/d/1oHn6sXhrUoAU_ziq1rgr3lFeIBK-5e8gyg1fMu_QUTQ/edit#gid=0

I've made a sample document, with omitted information of course.
As you can see in Sheet 1, there are multiple Q1 lines (2 for this example), with the same advertiser and agency, but a different product. These can vary, and have the same advertiser and product, but a different agency, etc.
In sheet 2, I have the monthly actuals, which as you can see, do not have the product tags. However, the monthly actuals tell me that there were two different spends in Q1 under the same advertiser/agency, one for 672268.12, and one for 10927.48. If I add up the first two lines in Sheet 2, I get the same revenue for Line 3 in Sheet 1.
There are many examples like this, and I am trying to figure out a formula, or a combination, to utilize in a spreadsheet with well over 24,000 rows.
TYIA

 

[KenDue]

Without an actual date of each transaction, it looks like a lot of trial and error in picking out the correct month in which a transaction belongs... in other words, not something a formula can determine reliably. Do you have original raw data from which to work instead of monthly and quarterly consolidated information?

Sorry, but I don't have any other ideas that would help you.

 

[hsuperman]

Without an actual date of each transaction, it looks like a lot of trial and error in picking out the correct month in which a transaction belongs... in other words, not something a formula can determine reliably. Do you have original raw data from which to work instead of monthly and quarterly consolidated information?

Sorry, but I don't have any other ideas that would help you.

Perhaps you could assist me with monthly to quarterly?
https://docs.google.com/spreadsheet...gr3lFeIBK-5e8gyg1fMu_QUTQ/edit#gid=1682469009
In Sheet4, I would like to take the monthly revenue and return a deduped, consolidated list of the revenue that was spent in each quarter.
As you can see, Row 8 is a sum of the two revenues in Rows 3 and 4, because they have the same advertiser and agency, and spent in the same quarter.

I would then be using the deduped quarterly spend list and using a percentage attribution to flag them by product.