06-10-2020 03:10 AM
06-10-2020 03:10 AM
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.
06-10-2020 08:46 AM
06-10-2020 10:23 AM
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 !
06-11-2020 04:02 AM
@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.
06-11-2020 08:21 AM
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...
06-11-2020 09:22 AM
@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.
06-12-2020 03:34 AM
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?
06-12-2020 04:53 AM
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.