I need to calculate the total # of calls, texts, & emails facilitated on a company's network each month from Jan 16 - Jan 17.
The issue: the data set given to me only contains Summary invoices, which show total volumes grouped by the type of usage over each individual invoice term. Since invoices cross multiple months, I need to calendarize them to appropriately split up the usage across each month.
I'm given 2 worksheets.
The 1st worksheet contains all 90 invoices (24 Call, 36 Text, 30 Email), the Total Usage (based on Usage Type) for that invoice, and the invoice's period start and end date. Here's a preview.
The 2nd worksheet gives the 3 Usage Curves (Calls, Texts, Emails) by average hourly usage by Usage Type from 1/1/16 to 1/31/17. So there are 24 rows for 1/1/16, 24 for 1/2/16, and so on until 1/31/17 to represent each hour of each day in each month. Here's a preview.
Using the 3 hourly usage curves, I need to calculate with formulas the total # of calls, texts, & emails in each month. I think I understand the concept behind calendarization, but I get confused by the last line of the exercise which says:
Looking at Invoice 2 in the first worksheet, 959.36 of the total usage of 2519 was attributed to Jan 16 and the remaining 1559.64 was attributed to Feb 16.
To get to 959.36 for Jan, they used this formula:
which is basically:
[Sum of text usage from 1/20 to 1/31] / [Sum of text usage from 1/20 to 2/20] * [Total Usage on Invoice 2]
To get to 1559.64 for Feb, they used this formula:
which is basically:
[Sum of text usage from 2/1 to 2/20] / [Sum of text usage from 1/20 to 2/20] * [Total Usage on Invoice 2]
None of their formulas make sense to me because Invoice 2 starts on 1/5 and ends on 2/5. So why would the calculation for the Jan month take the sum of usage from just 1/20 to 1/31, when the bill started much earlier?
Likewise, why is the calculation for the Feb month using dates from 2/1 to 2/20 when the invoice's end date is 2/5?
As you can tell, I'm a beginner at Excel and have trouble grasping what I need to do to make it easier to calculate these values. A big part of me thinks the way they did it for those 2 months is clearly wrong and that I should just restart the calendarization another way. But I have no idea, that's why I'm here for guidance.
Can anyone provide basic guidance as to either 1) how the sample calculations were calculated and whether the dates used are correct or 2) if the sample calculations are incorrect, then how do I approach the problem at all?
Basically, any sort of help would be much appreciated, I have a long night ahead of me haha. If it helps, here are the worksheets & edited prompt.
The issue: the data set given to me only contains Summary invoices, which show total volumes grouped by the type of usage over each individual invoice term. Since invoices cross multiple months, I need to calendarize them to appropriately split up the usage across each month.
I'm given 2 worksheets.
The 1st worksheet contains all 90 invoices (24 Call, 36 Text, 30 Email), the Total Usage (based on Usage Type) for that invoice, and the invoice's period start and end date. Here's a preview.
The 2nd worksheet gives the 3 Usage Curves (Calls, Texts, Emails) by average hourly usage by Usage Type from 1/1/16 to 1/31/17. So there are 24 rows for 1/1/16, 24 for 1/2/16, and so on until 1/31/17 to represent each hour of each day in each month. Here's a preview.
Using the 3 hourly usage curves, I need to calculate with formulas the total # of calls, texts, & emails in each month. I think I understand the concept behind calendarization, but I get confused by the last line of the exercise which says:
It's these 2 calculations that completely throw me off, because either there's a mistake in the data for those sample calculations, or I'm completely confused as to how to calendarize the data for those 2 Invoices.
Looking at Invoice 2 in the first worksheet, 959.36 of the total usage of 2519 was attributed to Jan 16 and the remaining 1559.64 was attributed to Feb 16.
To get to 959.36 for Jan, they used this formula:
Excel Formula:
=SUM(Part1_Usage_Curves!$E$461:$E$748)/SUM(Part1_Usage_Curves!$E$461:$E$1228)*$D6which is basically:
[Sum of text usage from 1/20 to 1/31] / [Sum of text usage from 1/20 to 2/20] * [Total Usage on Invoice 2]
To get to 1559.64 for Feb, they used this formula:
Excel Formula:
=SUM(Part1_Usage_Curves!E749:E1228)/SUM(Part1_Usage_Curves!$E$461:$E$1228)*$D6which is basically:
[Sum of text usage from 2/1 to 2/20] / [Sum of text usage from 1/20 to 2/20] * [Total Usage on Invoice 2]
None of their formulas make sense to me because Invoice 2 starts on 1/5 and ends on 2/5. So why would the calculation for the Jan month take the sum of usage from just 1/20 to 1/31, when the bill started much earlier?
Likewise, why is the calculation for the Feb month using dates from 2/1 to 2/20 when the invoice's end date is 2/5?
As you can tell, I'm a beginner at Excel and have trouble grasping what I need to do to make it easier to calculate these values. A big part of me thinks the way they did it for those 2 months is clearly wrong and that I should just restart the calendarization another way. But I have no idea, that's why I'm here for guidance.
Can anyone provide basic guidance as to either 1) how the sample calculations were calculated and whether the dates used are correct or 2) if the sample calculations are incorrect, then how do I approach the problem at all?
Basically, any sort of help would be much appreciated, I have a long night ahead of me haha. If it helps, here are the worksheets & edited prompt.
