SOLVED

Excel formula

Brass Contributor

Hi,

 

I've attached the excel sheet wherein there are three primary worksheet "Financials", "Budgets" and "Actuals" and one main sheet "Variance". The Financial sheet contains Actuals and Budgets for the year and Variance sheet contains comparative monthly and YTD variances between actuals and budgets. The variance sheet updates figures when month is entered in cell C4. 

 

At present, I've to make two different sheets "Actuals" and "Budgets" out of Financials sheet as I don't know proper solution or function to update figures directly from Financials sheet without creating additional 2 sheets. I need help to derive figures directly from Financials tab without making those 2 extra sheets.

15 Replies

@Dharmendra_Bharwad 

 

Not sure I follow what you want.

 

You have Actual and Budget columns for each financial period in the Financials sheet. Do you want to extract these and where to?

 

Have I understood the question?

 

Peter

Thanks for reaching out. As you can see there're separate Budgets and Actuals tab in sheet & they're coming from Financials tab only. Budgets and Actuals sheets are created separately to work out the difference between Actuals and Budgets as evident from formulas in "Variance" tab. Now, I don't need to have two separate tabs of Actuals and Budgets & I want to have figures coming directly from Financials tab to Variance tab. Does this makes sense?

@Dharmendra_Bharwad 

 

Ok - I think I understand what you want and I think you nearly had the solution yourself!

 

I have added a second sheet (Variance Two) and in the lower half (shaded in yellow) I have extracted the data from the Financials sheet directly.  I have only done the FEB data and not the YTD data - but i think the same principles will apply.

 

hope this helps you !

 

good luck.

 

Peter

@peteryac60 Thank you for working out on this and yes, you've understood what I need to achieve.

 

However, I would appreciate any help extended for the second part as well. I've tried to work out for YTD figures but the formula is not working. 

@Dharmendra_Bharwad 

Have a look at this.

 

If you are happy with it please mark this as a best solution.

 

thanks,

 

Peter

@peteryac60 I would've surely marked it as a best solution but it's not working as earlier.

 

As you can see in earlier sheet, YTD Forecast column should update values depending on date entered into cell C4. Actual values upto the date entered into cell C4 should be taken (+) budget and forecast values for rest of the year. Suppose if date is 30/04/2020, then it should calculate actual values upto 30/04/2020 and Budget and forecast values from 31/05/2020 to 31/12/2020.

 

Column L i.e. 2020 budget should always be constant and it should contain sum of figures from budget and forecast values i.e. except actuals for the whole year. That is to say it'll be constant irrespective of date entered into cell C4.

 

Hope this is clear and let me know if anything is not clear from my side.

@Dharmendra_Bharwad 

 

try this - BTW I found an error on the Financials sheet date row column Q - highlighted in yellow , the year was 2020 for January. I have changed it to 2021.

 

Hopefully this is what you are looking for.

 

Also , a suggestion on the Actual/Budget/Forecast row - rather than manually change maybe you can have a formula - if date is > C4Date then "Forecast", else "Actual"? Just a thought...

 

Peter

@peteryac60 Thank you till now for your kind help. However, the formula is still not complete what I'm looking for. I've made required changes to it so as to include only Actuals upto date as per cell C4 and then budgets and forecast figures from following months as per attached sheet.

 

However, I'm unable to include range year like the figures should only come for the year 2020 and not 2021. There's date range of start and end of year coming from column R. Rows 9 to 12 has reference to column R and thus values comes for particular year only.

 

In short, it would be great if you can work out solution in earlier formula (for figures coming from financials tab and not actuals & budgets) from row 9 to 12 or in current formula for date range.

@Dharmendra_Bharwad 

Hi

I am not clear what you want.

 

In the variance two sheet , in column K (YTD Forecast)  do you want Actual + Budget if date <= C4 date AND also want to add Budget for the date > C4? However, in your row 4  all the columns from April onwards are shown as FORECAST so the Budget>C4 check will not apply?

 

You also seem to want to just to add the number for the year that is in C4. Does this imply that you are going to extend your spreadsheet to include 2021 , 2022 etc.

 

BTW - Do you have to use this format? Or are you open to change? One of the tabs shows a possible format which I have used in the past and makes analysis easier?

 

 

Peter

@peteryac60 

Hi,

 

I know that this it's bit difficult to get it understand. 

 

Yes, I do want to have Actual + Budget if date <= C4 date AND also want to add Budget+Forecast for the date > C4? For April onwards, you've already added formula "SUMIFS(Financials!B9:Q9,Financials!$B$4:$Q$4,$K$6)" to include FORECAST. And, all the figures must be within the year range as per cell C4. Further, I really thank you for suggesting a possible format but I need to work on and stick to current format only.

 

Let's look at like this:

If date in cell C4 is 31/03/2020, then column K shall include actual figures from financials tab upto 31/03/2020 and budget+forecast figures from 30/04/2020. And if date is 30/04/2020, then it should include actual figures from financials tab upto 30/04/2020 and budget+forecast figures from 31/05/2020. And if it's 30/06/2020, then it should include actual figures from financials tab upto 31/05/2020 and forecast figures from 30/06/2020.

 

I hope it's making sense now.

@Dharmendra_Bharwad 

 

To make sure I understand , look at expected result sheet - are the figures in green the correct result that you would expect for column K.

 

What would you expect for column L. If you can do an expect result for this it would help.

 

thanks!

@peteryac60 

Yes, I require the amounts in green as a solution for column K. For column L, I've worked out figures in blue. Basically, it should include figures of budgets and forecasts for the year i.e. all figures except actuals.

best response confirmed by Dharmendra_Bharwad (Brass Contributor)
Solution

@Dharmendra_Bharwad

 

last chance!

 

@peteryac60 

Finally I got it what I wanted to achieve. A great great great thanks to you for listening and achieving the solution. A double thumbs up to you man. You deserve more than MVP to me.

Good luck and thank you again.

@Dharmendra_Bharwad 

 

you are welcome - please mark this as best completed solution.

1 best response

Accepted Solutions
best response confirmed by Dharmendra_Bharwad (Brass Contributor)
Solution