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

[ETH20k]

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

 

[BSALV]

A26, there was a space after "Production labor " !!!

RedditCFS.xlsx
	A	B	C	D	E	F	G	H
1
2	COSTS
3		Date	End	Subcategory	Cost USD	monthly cost
4		apr/22	apr/22	Raw Materials	$ -74.000,00	-$74.000,00
5		apr/22	apr/22	Raw Materials	$ -15.000,00	-$15.000,00
6		apr/22	mei/22	Payroll	$ -18.000,00	-$9.000,00
7		apr/22	mei/22	Payroll	$ -14.000,00	-$7.000,00
8		apr/22	mei/22	Payroll	$ -12.000,00	-$6.000,00
9		apr/22	apr/22	Production Labor	$ -30.000,00	-$30.000,00
10		apr/22	apr/22	IT Development	$ -7.000,00	-$7.000,00
11		apr/22	apr/22	IT Development	$ -20.000,00	-$20.000,00
12		apr/22	apr/22	IT Development	$ -5.000,00	-$5.000,00
13		apr/22	jun/22	Marketing	$ -55.000,00	-$18.333,33
14		apr/22	apr/22	Marketing	$ -35.000,00	-$35.000,00
15		mei/22	jun/22	Marketing	$ -70.000,00	-$35.000,00
16		mei/22	jun/22	Marketing	$ -20.000,00	-$10.000,00		$ -375.000,00
17								$ -375.000,00
18
19
20	CASH FLOW
21		apr/22	mei/22	jun/22	jul/22	aug/22
22
23	Revenue	$ 100.000,00	$ 200.000,00	$ 300.000,00	$ 400.000,00	$ 500.000,00
24	COGS	$ 119.000,00	$ -	$ -	$ -	$ -
25	Raw Materials	$ -89.000,00	$ -	$ -	$ -	$ -		$ -89.000,00
26	Production Labor	$ -30.000,00	$ -	$ -	$ -	$ -		$ -30.000,00
27	Gross Profit	$ -19.000,00	$ 200.000,00	$ 300.000,00	$ 400.000,00	$ 500.000,00
28	Gross Margin	-19%	100%	100%	100%	100%
29	Expenses	$ -107.333,33	$ -85.333,33	$ -63.333,33	$ -	$ -
30	IT Development	$ -32.000,00	$ -	$ -	$ -	$ -		$ -32.000,00
31	Marketing	$ -53.333,33	$ -63.333,33	$ -63.333,33	$ -	$ -		$ -180.000,00
32	Payroll	$ -22.000,00	$ -22.000,00	$ -	$ -	$ -		$ -44.000,00
33	Utility & other	$ -	$ -	$ -	$ -	$ -		$ -
34	Operating Profit (EBIT)	$ -126.333,33	$ 114.666,67	$ 236.666,67	$ 400.000,00	$ 500.000,00
35	Operating Profit Margin	-126%	57%	79%	100%	100%
36
Sheet1

Cell Formulas
Range	Formula
F4:F16	F4	=+[@[Cost USD]]/(DATEDIF([@Date],[@End],"m")+1)
H16	H16	=SUM(Table2[Cost USD])
H17	H17	=SUM(H25:H33)
B24:F24	B24	=-SUM(B25:B26)
B25:F26,B30:F33	B25	=SUMPRODUCT(Table2[[monthly cost]:[monthly cost]]*(Table2[[Date]:[Date]]<=B$21)*(B$21<=Table2[[End]:[End]])*(Table2[[Subcategory]:[Subcategory]]=$A25))
B27:F27	B27	=B23-B24
B28:F28	B28	=IF(B27<>0,(B27/B23),0)
B29:F29	B29	=SUM(B30:B33)
B34:F34	B34	=B27+B29
B35:F35	B35	=IF(B34<>0,(B34/B23),0)
H25:H26,H30:H33	H25	=SUM(B25:F25)

 

[Peter_SSs]

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]

@BSALV ... this is phenomenal!!! First time I ever posted something and am amazed by the help. Thank you

 

[BSALV]

you're welcome.