Spread a Number over multiple cells and calculate growth

New Contributor

Hello,

Please I need help in achieving the below automatically as I managed to achieve it manually via Goal Seek but I want to use a formula so that it is is automatic calculation.

 

I am trying to link two tables and I am not sure the correct way to do it. 

 

In Table 1, I have the yearly revenues that I am expecting to generate.

 

I want to to link table 1 to table 2 where Table 2 will read the revenue for a specific year and spread it over the months. I do not want to spread it equally as I need to show monthly growth to my supplier and calculate the monthly growth.

 

I will start generating revenue in July 2022 as per below table so I understand that maybe the value for July needs to be input manually

 

Also the value for January 2023 will be taken from the December 2022 value and multiplied with the growth for 2023 year and continue and the same for 2024 ......

 

 

 

 

 

I will start generating revenues in July 202

 

Table 1   
Year202220232024
Revenues        1,000,000       2,000,000       3,640,000

 

 

Table 225.0%23.3%16.38%
125.0%123.3%116.4%
123
202220232024
RevenuesRevenuesRevenues
 
  Jan             169,115       1,970,741
  Feb             208,518       2,293,498
  Mar             257,102       2,669,113
  Apr             317,006       3,106,245
  May             390,867       3,614,968
  Jun             481,938       4,207,006
  Jul              45,000          594,227       4,896,005
  Aug              56,236          732,680       5,697,844
  Sep              70,277          903,391       6,631,003
  Oct              87,825       1,113,878       7,716,990
  Nov            109,754       1,373,407       8,980,833
  Dec            137,158       1,693,405     10,451,661
3 Replies

@karim1974 Attached a workbook that follows the logic of what I believe is what you explained. With considerably lower monthly growth %% and a higher first month revenue, I could approximate the yearly revenue targets.

If this makes no sense alt all, kindly clarify.

 

 

@Riny_van_Eekelen 

 

Thank you so much for your input. This is close but not 100% what I am looking for.

if I consider your form, I only want to be able to input manually the July value and then the model should automatically consider the revenue of 2022 and spread the values and calculate automatically the growth rate 

 

so by Inputing July value manually the model needs to consider the $1,000,000 Revenue of 2022 and substract the value of July and spread unequally the remaining into August to December months and generate automaltically the growth that was calculated to go from July to December to reach the 1,000,0000

 

The year after 2023, the model will consider the December 2022 value and the revenue value for 2023 in table 1 and repeat the process from January to December

 

so this model will only take one manual input which is the July 2022 value...all other values and yearly growth rate are calculated automatically.

 

 

@karim1974 Well, good luck! I'm not going to be able to help you with that. Sorry.