Allocating Contract Costs by Fiscal Month

**mps1228** · 08-15-2018, 08:07 PM

Hi all,

I am trying to find a way to allocate contract costs across fiscal months given a total spend amount, contract start date and end date, and cost per day. What makes it tricky is the fact that the start dates fall at various different points during the month and end in the same fashion. Also, contract costs that extend beyond the end of the fiscal year are not to be included in the calculation.

I have tried using EOMONTH and found it to be ineffective due to the fiscal months not starting/ending in the typical fashion.
Attached is an example of the format I am using to make the calculations. Please let me know if there is anything else I can clarify about the situation.

Thanks!

**JeteMc** · 08-16-2018, 12:48 AM

Hello mps1228 and Welcome to Excel Forum.
If I understand the remark "contract costs that extend beyond the end of the fiscal year are not to be included in the calculation" correctly then it seems the formula for Cost/Day could be: =IF(D4<E4,B4/G4,B4/F4)
The formula for the range J4:U6 could be:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**FlameRetired** · 08-16-2018, 02:05 AM

Another approach. In J4 filled down and across. It gives different returns in the 3rd row from JeteMc's.

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 08-16-2018, 02:44 AM

mps1228,

It appears that you are using two different rules to determine days in FM vs Total days /Fiscal days. When I calculate days in each FM I find that I have to include both the start and end dates. But when totaling each row I must subtract 1 from those totals to arrive at what you have.

I.e. Each FM's days are Start/End inclusive where Total days /Fiscal days are start date exclusive / end date inclusive.

Do I have all that correct? If so I would like to change my formula to allow for both and account for End date being greater than EOFY.

Formula:

Please Login or Register  to view this content.

This changes the accounting by one day each.

**mps1228** · 08-17-2018, 04:31 AM

Thank you both for your responses!

FlameRetired: Good point about the start dates, I hadn't considered that.

JeteMc: To clarify, what I meant was that costs that extend beyond the end of the fiscal year are not meant to be displayed, but the total contract cost including those "extra" days is still used to calculate cost per day.

Nonetheless, I tried each of your formulas and they both work quite well. They do not provide entirely accurate totals, but they are close enough that I feel comfortable using either one.

I appreciate the help!

**JeteMc** · 08-17-2018, 08:39 AM

You're Welcome and thank you for the feedback. If you could provide an example of inaccuracy in the totals, we may be able to make an adjustment. Otherwise please take a moment to mark the thread as 'Solved' using the thread tools link in the ribbon above your first post. Either way, I hope that you have a blessed day.

Allocating Contract Costs by Fiscal Month

LinkBack

Thread Tools

Rate This Thread

Display

Allocating Contract Costs by Fiscal Month

Re: Allocating Contract Costs by Fiscal Month

Re: Allocating Contract Costs by Fiscal Month

Re: Allocating Contract Costs by Fiscal Month

Re: Allocating Contract Costs by Fiscal Month

Re: Allocating Contract Costs by Fiscal Month

Thread Information

Users Browsing this Thread

Similar Threads

Allocating costs - one row to mulitple rows

Need help with allocating Revenue to months only for Year 2014

Need help with calculating project costs with the costs different each month.

Allocating Costs by Dynamic Weighted Average

[SOLVED] Allocating a given date to a date range bucket: futures contract expiry

Recognizing Revenue month over mont for a multi month contract

[SOLVED] Create Fiscal Year - Lookup Date in Fiscal Month Date Range and Return EOM Date

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions