How to evenly spread out and sum cost items across multiple months based on criteria?

ETH20k

New Member
Joined
Mar 26, 2022
Messages
2
Element 1: Dynamic Table
  • The table has a "start", "end", "cost" & "category" column with multiple row entries.
  • e.g. From "Apr.22" until "Jun.22", I have a TOTAL of "3'000 USD" as "marketing cost".
Element 2: Cash Flow Model
  • I want to feed the table data into a cash flow model, based on criteria of category, date and amount
  • The Cash Flow (CF) Model shows the monthly CF, whereas each column holds one month and first column shows the cashflow category, e.g. marketing, etc.
Question:
How do I aggregate the sum of all table rows and equally allocate the monthly cost to the right month and criteria of the CF Model.
  • My difficulty is to divide and allocate the total amount across the relevant months in an aggregate.
Thank you.
RedditCFS.xlsx
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
A26, there was a space after "Production labor " !!!
RedditCFS.xlsx
ABCDEFGH
1
2COSTS
3DateEndSubcategoryCost USDmonthly cost
4apr/22apr/22Raw Materials$ -74.000,00-$74.000,00
5apr/22apr/22Raw Materials$ -15.000,00-$15.000,00
6apr/22mei/22Payroll$ -18.000,00-$9.000,00
7apr/22mei/22Payroll$ -14.000,00-$7.000,00
8apr/22mei/22Payroll$ -12.000,00-$6.000,00
9apr/22apr/22Production Labor$ -30.000,00-$30.000,00
10apr/22apr/22IT Development$ -7.000,00-$7.000,00
11apr/22apr/22IT Development$ -20.000,00-$20.000,00
12apr/22apr/22IT Development$ -5.000,00-$5.000,00
13apr/22jun/22Marketing$ -55.000,00-$18.333,33
14apr/22apr/22Marketing$ -35.000,00-$35.000,00
15mei/22jun/22Marketing$ -70.000,00-$35.000,00
16mei/22jun/22Marketing$ -20.000,00-$10.000,00$ -375.000,00
17$ -375.000,00
18
19
20CASH FLOW
21apr/22mei/22jun/22jul/22aug/22
22
23Revenue$ 100.000,00$ 200.000,00$ 300.000,00$ 400.000,00$ 500.000,00
24COGS$ 119.000,00$ -$ -$ -$ -
25Raw Materials$ -89.000,00$ -$ -$ -$ -$ -89.000,00
26Production Labor$ -30.000,00$ -$ -$ -$ -$ -30.000,00
27Gross Profit$ -19.000,00$ 200.000,00$ 300.000,00$ 400.000,00$ 500.000,00
28Gross Margin-19%100%100%100%100%
29Expenses$ -107.333,33$ -85.333,33$ -63.333,33$ -$ -
30IT Development$ -32.000,00$ -$ -$ -$ -$ -32.000,00
31Marketing$ -53.333,33$ -63.333,33$ -63.333,33$ -$ -$ -180.000,00
32Payroll$ -22.000,00$ -22.000,00$ -$ -$ -$ -44.000,00
33Utility & other$ -$ -$ -$ -$ -$ -
34Operating Profit (EBIT)$ -126.333,33$ 114.666,67$ 236.666,67$ 400.000,00$ 500.000,00
35Operating Profit Margin -126%57%79%100%100%
36
Sheet1
Cell Formulas
RangeFormula
F4:F16F4=+[@[Cost USD]]/(DATEDIF([@Date],[@End],"m")+1)
H16H16=SUM(Table2[Cost USD])
H17H17=SUM(H25:H33)
B24:F24B24=-SUM(B25:B26)
B25:F26,B30:F33B25=SUMPRODUCT(Table2[[monthly cost]:[monthly cost]]*(Table2[[Date]:[Date]]<=B$21)*(B$21<=Table2[[End]:[End]])*(Table2[[Subcategory]:[Subcategory]]=$A25))
B27:F27B27=B23-B24
B28:F28B28=IF(B27<>0,(B27/B23),0)
B29:F29B29=SUM(B30:B33)
B34:F34B34=B27+B29
B35:F35B35=IF(B34<>0,(B34/B23),0)
H25:H26,H30:H33H25=SUM(B25:F25)
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to evenly spread out and sum cost items across multiple months based on criteria?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

ETH20k

New Member
Joined
Mar 26, 2022
Messages
2
@BSALV ... this is phenomenal!!! First time I ever posted something and am amazed by the help. Thank you
 

Forum statistics

Threads
1,175,670
Messages
5,898,803
Members
434,731
Latest member
njakfla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top