I have a cost that may be spread out over various months. I need to make a report that shows what % of cost and what amount of cost is attributed to each month.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

e.g Cost is $50,000

Starts Jan 15th ends April 22nd.

What amount and % would be attributed to Jan, Feb, Mar, April.

No idea where to start.

Would I need to make 12 fields for each month that may be used?

Photo of Insight

Insight

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Insight

Insight

  • 0 Points
OK, Im thinking Ill need 12 fields, 1 for each month.    But how would I get each month field to know if it is within the date range?
I had a similar situation for s client where sales forecast needed to be entered ( eg, yay, I landed a $100,000 sale, but the actual sales would be spread out over a number of months.

I created a sales spread child table to each sales record. So 1 Sale has many Sales spreads.

The user would enter the Sale, and then on a grid edit form on the sales record, enter sales spread child records with the forecast date and % of total. I then put up a warning message on the Parent Sake record if the spread % did not total to 100%.

Then you can run reports off the Spreads table to see the monthly Sales / Expenses.
Photo of Insight

Insight

  • 0 Points
The Manual idea has got me thinking but for a start there must be an easy way to acheive this with formulas:

Start Date - Jul - 12 - 2016
End Date - Oct - 9 - 2016

Jan 0
Feb 0
Mar 0
Apr 0
May 0
Jun 0
Jul - 19
Aug - 31
Sep - 30
Oct - 9
Nov - 0
Dec - 0

From the start and end date automatically calculate days 'used' in those months.  The others are automatically 0.     Then I could work out the percentages and values using other formula fields.
But what if the span crosses a year end?
Photo of Insight

Insight

  • 0 Points
I kind of put that to the back of my mind but I did think of it.  I think we might end up sticking to a manual XL Spreadsheet :(

This conversation is no longer open for comments or replies.