New Contributor

# Spread a Number over multiple cells and calculate growth

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 Year 2022 2023 2024 Revenues 1,000,000 2,000,000 3,640,000

 Table 2 25.0% 23.3% 16.38% 125.0% 123.3% 116.4% 1 2 3 2022 2023 2024 Revenues Revenues Revenues 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

# Re: Spread a Number over multiple cells and calculate growth

@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.

# Re: Spread a Number over multiple cells and calculate growth

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.

# Re: Spread a Number over multiple cells and calculate growth

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