Do I have to create each month

[pulary]

Hi all,

I am trying to make an interactive dashboard where the end user can select the year, month, entity name, and project or a combination of all these, that will display a data table and graph accordingly. The problem I am having is the data is not done by month, but rather I am given a total revenue and a length of time (duration). What I am trying to figure out is do I have to create data for each month. My guess is that I will need to break down by month, which is fine, but I am hopeful there is an easier way to do it versus a manual process.

Thanks!

Financial Analyst Excel Test.xlsx
	A	B	C	D	E
1	Entity	Project Name	Estimated Start	Estimated Revenue	Duration (months)
2	BCR	Solar 1	7/1/22	$28,500,000	25
3	BCR	Solar 2	6/1/22	$3,600,000	4
4	BE	Solar 3	6/1/22	$4,200,000	5
5	BCR	Solar 4	7/1/22	$40,000,000	24
6	BCR	Solar 6	8/1/22	$20,000,000	12
7	BCR	Solar 7	1/1/23	$21,000,000	23
8	BCR	Solar 8	8/1/22	$42,000,000	24
9	BE	Solar 9	11/1/22	$11,200,000	8
10	BCR	Solar 10	6/1/22	$4,000,000	13
11	BCR	Solar 11	5/1/22	$11,000,000	12
12	BE	Solar 12	7/1/22	$11,000,000	5
13	BE	Solar 13	6/1/22	$2,200,000	5
14	BE	Solar 14	8/1/22	$95,000,000	12
15	BE	Solar 15	6/1/22	$110,000,000	8
16	BE	Solar 16	8/1/22	$15,000,000	6
17	IME	Solar 17	7/1/22	$330,000,000	16
18	BE	Solar 18	8/1/22	$23,000,000	3
19	IME	Solar 19	8/1/22	$62,000,000	6
20	IME	Solar 20	2/1/23	$1,121,000,000	36
21	IME	Solar 21	8/1/22	$90,000,000	15
22	BE	Solar 22	8/1/22	$32,000,000	12
23	BE	Solar 23	11/1/22	$30,000,000	12
24	BE	Solar 24	11/1/22	$30,000,000	12
Original Inputs

 

[Ezguy4u]

I am going to suggest showing us what you would like the output to look like. The above is the input, show us what the output looks like.

 

[pulary]

Here is what I would like the data to look like and the manual process that I am trying to avoid.

Financial Analyst Excel Test.xlsx
	A	B	C	D
29	Entity	Project Name	Estimated Start	Est Month Rev
30	BCR	Solar 1	Jul-22	1,140,000.00
31	BCR	Solar 1	Aug-22	1,140,000.00
32	BCR	Solar 1	Sep-22	1,140,000.00
33	BCR	Solar 1	Oct-22	1,140,000.00
34	BCR	Solar 1	Nov-22	1,140,000.00
35	BCR	Solar 1	Dec-22	1,140,000.00
36	BCR	Solar 1	Jan-23	1,140,000.00
37	BCR	Solar 1	Feb-23	1,140,000.00
38	BCR	Solar 1	Mar-23	1,140,000.00
39	BCR	Solar 1	Apr-23	1,140,000.00
40	BCR	Solar 1	May-23	1,140,000.00
41	BCR	Solar 1	Jun-23	1,140,000.00
42	BCR	Solar 1	Jul-23	1,140,000.00
43	BCR	Solar 1	Aug-23	1,140,000.00
44	BCR	Solar 1	Sep-23	1,140,000.00
45	BCR	Solar 1	Oct-23	1,140,000.00
46	BCR	Solar 1	Nov-23	1,140,000.00
47	BCR	Solar 1	Dec-23	1,140,000.00
48	BCR	Solar 1	Jan-24	1,140,000.00
49	BCR	Solar 1	Feb-24	1,140,000.00
50	BCR	Solar 1	Mar-24	1,140,000.00
51	BCR	Solar 1	Apr-24	1,140,000.00
52	BCR	Solar 1	May-24	1,140,000.00
53	BCR	Solar 1	Jun-24	1,140,000.00
54	BCR	Solar 1	Jul-24	1,140,000.00
55	BCR	Solar 2	Jun-22	900,000.00
56	BCR	Solar 2	Jul-22	900,000.00
57	BCR	Solar 2	Aug-22	900,000.00
58	BCR	Solar 2	Sep-22	900,000.00
59	BE	Solar 3	Jun-22	840,000.00
60	BE	Solar 3	Jul-22	840,000.00
61	BE	Solar 3	Aug-22	840,000.00
62	BE	Solar 3	Sep-22	840,000.00
63	BE	Solar 3	Oct-22	840,000.00
64	BCR	Solar 4	Jul-22	1,666,666.67
65	BCR	Solar 4	Aug-22	1,666,666.67
66	BCR	Solar 4	Sep-22	1,666,666.67
Data

Cell Formulas
Range	Formula
D30:D54	D30	=$E$2
D55:D58	D55	=$E$3
D59:D63	D59	=$E$4
D64:D66	D64	=$E$5

I would like this for all 24 projects so I can create a waterfall and then complete an interactive graph. Still trying to decide on the best approach.

Thanks all for the help!

Phillip

 

[Ezguy4u]

pulary here is my solution to your question. Now this usually generates more questions than answer so let the discussion begin. I don't know how much you know about vba so copy and paste the program to a module or the Original vba sheet. Also be sure to save workbook as macro embedded. Also, be sure to add a worksheet and name it Data. Sometimes the A plus students review my programs and instead of my 20 lines of code, they do it 2 lines of code. So maybe we will get lucky. Anyway, I believe this program does what you want but if not let us know.

Sub Prog1()

Dim Row1 As Long
Dim Row2 As Long
Dim CelCnt As Long
Dim i As Long
Dim Rev1 As Currency
Dim MyDate1 As Date

Row1 = 2
Row2 = 2
CelCnt = 0
Sheets("Original").Select

Application.ScreenUpdating = False
Do Until IsEmpty(Cells(Row1, 1))

Cells(Row1, 1).Select

CelCnt = Cells(Row1, 5) + CelCnt

MyDate1 = Sheets("Original").Cells(Row1, 3)

For i = Row2 To CelCnt + 1

With Sheets("Data")
    .Cells(i, 1) = Sheets("Original").Cells(Row1, 1)
     .Cells(i, 2) = Sheets("Original").Cells(Row1, 2)
     .Cells(i, 3).NumberFormat = "mmm-yy"
     .Cells(i, 3) = MyDate1
      MyDate1 = DateAdd("m", 1, Sheets("Data").Cells(i, 3))
      Rev1 = Sheets("Original").Cells(Row1, 4) / Sheets("Original").Cells(Row1, 5)
     Sheets("Data").Cells(i, 4) = Rev1
End With

Next i

Row2 = i
'Sheets("Original").Select

Row1 = Row1 + 1

Loop

Application.ScreenUpdating = True
Sheets("Data").Select
Sheets("Data").Range("A1").Select

End Sub

22-04-16 work8.xlsm
	A	B	C	D
1	Entity	Project Name	Estimated Start	Estimated Revenue
2	BCR	Solar 1	Jul-22	$1,140,000.00
3	BCR	Solar 1	Aug-22	$1,140,000.00
4	BCR	Solar 1	Sep-22	$1,140,000.00
5	BCR	Solar 1	Oct-22	$1,140,000.00
6	BCR	Solar 1	Nov-22	$1,140,000.00
7	BCR	Solar 1	Dec-22	$1,140,000.00
8	BCR	Solar 1	Jan-23	$1,140,000.00
9	BCR	Solar 1	Feb-23	$1,140,000.00
10	BCR	Solar 1	Mar-23	$1,140,000.00
11	BCR	Solar 1	Apr-23	$1,140,000.00
12	BCR	Solar 1	May-23	$1,140,000.00
13	BCR	Solar 1	Jun-23	$1,140,000.00
14	BCR	Solar 1	Jul-23	$1,140,000.00
15	BCR	Solar 1	Aug-23	$1,140,000.00
16	BCR	Solar 1	Sep-23	$1,140,000.00
17	BCR	Solar 1	Oct-23	$1,140,000.00
18	BCR	Solar 1	Nov-23	$1,140,000.00
19	BCR	Solar 1	Dec-23	$1,140,000.00
20	BCR	Solar 1	Jan-24	$1,140,000.00
21	BCR	Solar 1	Feb-24	$1,140,000.00
22	BCR	Solar 1	Mar-24	$1,140,000.00
23	BCR	Solar 1	Apr-24	$1,140,000.00
24	BCR	Solar 1	May-24	$1,140,000.00
25	BCR	Solar 1	Jun-24	$1,140,000.00
26	BCR	Solar 1	Jul-24	$1,140,000.00
27	BCR	Solar 2	Jun-22	$900,000.00
28	BCR	Solar 2	Jul-22	$900,000.00
29	BCR	Solar 2	Aug-22	$900,000.00
30	BCR	Solar 2	Sep-22	$900,000.00
31	BE	Solar 3	Jun-22	$840,000.00
32	BE	Solar 3	Jul-22	$840,000.00
33	BE	Solar 3	Aug-22	$840,000.00
34	BE	Solar 3	Sep-22	$840,000.00
35	BE	Solar 3	Oct-22	$840,000.00
36	BCR	Solar 4	Jul-22	$1,666,666.67
37	BCR	Solar 4	Aug-22	$1,666,666.67
38	BCR	Solar 4	Sep-22	$1,666,666.67
39	BCR	Solar 4	Oct-22	$1,666,666.67
40	BCR	Solar 4	Nov-22	$1,666,666.67
41	BCR	Solar 4	Dec-22	$1,666,666.67
42	BCR	Solar 4	Jan-23	$1,666,666.67
43	BCR	Solar 4	Feb-23	$1,666,666.67
44	BCR	Solar 4	Mar-23	$1,666,666.67
45	BCR	Solar 4	Apr-23	$1,666,666.67
46	BCR	Solar 4	May-23	$1,666,666.67
47	BCR	Solar 4	Jun-23	$1,666,666.67
48	BCR	Solar 4	Jul-23	$1,666,666.67
49	BCR	Solar 4	Aug-23	$1,666,666.67
50	BCR	Solar 4	Sep-23	$1,666,666.67
51	BCR	Solar 4	Oct-23	$1,666,666.67
52	BCR	Solar 4	Nov-23	$1,666,666.67
53	BCR	Solar 4	Dec-23	$1,666,666.67
54	BCR	Solar 4	Jan-24	$1,666,666.67
55	BCR	Solar 4	Feb-24	$1,666,666.67
56	BCR	Solar 4	Mar-24	$1,666,666.67
57	BCR	Solar 4	Apr-24	$1,666,666.67
58	BCR	Solar 4	May-24	$1,666,666.67
59	BCR	Solar 4	Jun-24	$1,666,666.67
60	BCR	Solar 6	Aug-22	$1,666,666.67
61	BCR	Solar 6	Sep-22	$1,666,666.67
62	BCR	Solar 6	Oct-22	$1,666,666.67
63	BCR	Solar 6	Nov-22	$1,666,666.67
64	BCR	Solar 6	Dec-22	$1,666,666.67
65	BCR	Solar 6	Jan-23	$1,666,666.67
66	BCR	Solar 6	Feb-23	$1,666,666.67
67	BCR	Solar 6	Mar-23	$1,666,666.67
68	BCR	Solar 6	Apr-23	$1,666,666.67
69	BCR	Solar 6	May-23	$1,666,666.67
70	BCR	Solar 6	Jun-23	$1,666,666.67
71	BCR	Solar 6	Jul-23	$1,666,666.67
72	BCR	Solar 7	Jan-23	$913,043.48
73	BCR	Solar 7	Feb-23	$913,043.48
74	BCR	Solar 7	Mar-23	$913,043.48
75	BCR	Solar 7	Apr-23	$913,043.48
76	BCR	Solar 7	May-23	$913,043.48
77	BCR	Solar 7	Jun-23	$913,043.48
78	BCR	Solar 7	Jul-23	$913,043.48
79	BCR	Solar 7	Aug-23	$913,043.48
80	BCR	Solar 7	Sep-23	$913,043.48
81	BCR	Solar 7	Oct-23	$913,043.48
82	BCR	Solar 7	Nov-23	$913,043.48
83	BCR	Solar 7	Dec-23	$913,043.48
84	BCR	Solar 7	Jan-24	$913,043.48
85	BCR	Solar 7	Feb-24	$913,043.48
86	BCR	Solar 7	Mar-24	$913,043.48
87	BCR	Solar 7	Apr-24	$913,043.48
88	BCR	Solar 7	May-24	$913,043.48
89	BCR	Solar 7	Jun-24	$913,043.48
90	BCR	Solar 7	Jul-24	$913,043.48
91	BCR	Solar 7	Aug-24	$913,043.48
92	BCR	Solar 7	Sep-24	$913,043.48
93	BCR	Solar 7	Oct-24	$913,043.48
94	BCR	Solar 7	Nov-24	$913,043.48
Data

22-04-16 work8.xlsm
	A	B	C	D	E
1	Entity	Project Name	Estimated Start	Estimated Revenue	Duration (months)
2	BCR	Solar 1	7/1/2022	$28,500,000.00	25
3	BCR	Solar 2	6/1/2022	$3,600,000.00	4
4	BE	Solar 3	6/1/2022	$4,200,000.00	5
5	BCR	Solar 4	7/1/2022	$40,000,000.00	24
6	BCR	Solar 6	8/1/2022	$20,000,000.00	12
7	BCR	Solar 7	1/1/2023	$21,000,000.00	23
8	BCR	Solar 8	8/1/2022	$42,000,000.00	24
9	BE	Solar 9	11/1/2022	$11,200,000.00	8
10	BCR	Solar 10	6/1/2022	$4,000,000.00	13
11	BCR	Solar 11	5/1/2022	$11,000,000.00	12
12	BE	Solar 12	7/1/2022	$11,000,000.00	5
13	BE	Solar 13	6/1/2022	$2,200,000.00	5
14	BE	Solar 14	8/1/2022	$95,000,000.00	12
15	BE	Solar 15	6/1/2022	$110,000,000.00	8
16	BE	Solar 16	8/1/2022	$15,000,000.00	6
17	IME	Solar 17	7/1/2022	$330,000,000.00	16
18	BE	Solar 18	8/1/2022	$23,000,000.00	3
19	IME	Solar 19	8/1/2022	$62,000,000.00	6
20	IME	Solar 20	2/1/2023	$1,121,000,000.00	36
21	IME	Solar 21	8/1/2022	$90,000,000.00	15
22	BE	Solar 22	8/1/2022	$32,000,000.00	12
23	BE	Solar 23	11/1/2022	$30,000,000.00	12
24	BE	Solar 24	11/1/2022	$30,000,000.00	12
Original

 

[pulary]

Thank you so much! This is exactly what I was looking for!

 

[Ezguy4u]

pulary, Thanks for the feedback.

 

[pulary]

I have a followup question. Since I am running excel for Mac, I am not able to use the filter chart as I would on a Windows based verison, but I am trying to take the data below and make an interactive chart. I would like to be able to select specific entities or a combination of entities as well specific projects or a combination of projects and have the chart update.

Financial Analyst Excel Test V2.xlsm
	A	B	C	D	E	F
1	Entity	Project	Month	Column1	Column2	Revenue
27	BCR	Solar 2	Jun-22	6	2022	900,000
28	BCR	Solar 2	Jul-22	7	2022	900,000
29	BCR	Solar 2	Aug-22	8	2022	900,000
30	BCR	Solar 2	Sep-22	9	2022	900,000
31	BE	Solar 3	Jun-22	6	2022	840,000
32	BE	Solar 3	Jul-22	7	2022	840,000
33	BE	Solar 3	Aug-22	8	2022	840,000
34	BE	Solar 3	Sep-22	9	2022	840,000
35	BE	Solar 3	Oct-22	10	2022	840,000
36	BCR	Solar 4	Jul-22	7	2022	1,666,667
37	BCR	Solar 4	Aug-22	8	2022	1,666,667
Data

Cell Formulas
Range	Formula
D27:D37	D27	=TEXT(C27,"M")
E27:E37	E27	=TEXT(C27,"YYYY")

Thank you all for your help!

 

[pulary]

I have another follow up question. When I try to use the VBA code in MS Excel on a windows version I get an error at the MyDate1=Sheets("Original").Cells(Row1,3) line.

Any idea why?

Thanks!

Phillip

 

[Joe4]

I have another follow up question. When I try to use the VBA code in MS Excel on a windows version I get an error at the MyDate1=Sheets("Original").Cells(Row1,3) line.

Any idea why?

Thanks!

Phillip

What exactly does the error message say?
Has your data changed at all (columns or rows shifted, or type of data in each column changed)?

 

[pulary]

I am getting a Run-Time Error '13'.

I did change the tabs name to Inputs and Data, but I have changed the names in the code. I deleted MyDate1=Sheets("Original").Cells(Row1,3) and it moves everything to the 'Data' tab, but it does nothing,