Using a formula to populate actuals on a monthly basis in Excel

Ok i think I understand, they way I would do this which is probably the simplest to setup is just do 12 IF formula and add them all together.

So the way this would work is, IF the actual column if filled, take that amount, if not take the forecast.

I’ll not write out the whole formula but you could see the pattern:

=IF(BT5="",BS5,BT5)+IF(BW5="",BV5,BW5)

Hopefully that method would work for you.

Please let me know if you need any further assistance.

Kind Regards,

Elise

Hi there, thanks for your response! I have taken some screenshots so hopefully this will help.

Here is a snapshot of what my monthly views will look like. Each month will have one "LE" column (for example, BS), which reflects whatI have forecasted to spend on a particular resource for the month. Once the month is over, I receive the actuals - i.e. what was actually spent for that particular resource which I will then populate into the "act" column for that month (for example BT).

On a monthly basis, I need to be able to reflect for the current year what the overall estimated spend is. This means I need to add up the actuals (act columns) that have come in to date, and the forecasts (LE columns) for future months. This would populate in a column like below (FI) for each resource. 

I can do this manually, but was hoping for a solution that would allow me to "switch over" from the LE to Act columns as required. For example, if I populate the below cell 1B with April, then FI (above) will update to sum up all of the "Act" columns up to and including April, and all of the "LE" columns for May onwards. 

"More efficiently" is probably a good description of using a PivotTable.

.

A PivotTable is used to automatically generate formulas. A basic pivot table takes raw data and generates sums.  But it can also be used to generate other calculations.

After the PivotTable is setup, it is a 2 click operation to import new data. 

.

If you need / want to setup the pivot to display monthly values side by side, that is an option. And/Or you can setup a PivotTable that presents the data tabulated "vertically, Jan-Dec one above the other with actual vs predicted side by side.

And/Or you can setup the Pivot to display only month(s) selected from a "slicer".

.

Once you have the data setup, usually using PowerQuery, generating variations on PivotTables only takes a minute or two. It usually takes more time to research how to do it, than to do it.

.

Given a little time and effort (or a bunch of money) you can build or buy a full set of financial reports that use the same set of raw data to be generated from.

.

If you are interested in learning more about PivotTables, and the associated features, I have links to learning materials. And we can walk you through some initial examples of what can be done with your data if you provide us with some simple sample data and a manually typed example of the results you want generated.

.

Here are some articles with examples of what you can do with PivotTables

.

MSPTDA 26: Budget vs Actual in Power Pivot & Power BI Desktop, DAX & Data Model2019 05 23

https://www.youtube.com/watch?v=RxcgFfP8d1U 10min50

In this video learn about how create a Data Model with Relationships and DAX Measures to compare month product budgeted amounts to actual amounts. Then calculate variances.

1. (00:24) Introduction & look at Data Modeling Problem
2. (03:20) Data Modeling with Relationships
3. (05:15) Diagram and pictures of how Filter Context works in our Data Model
4. (07:00) DAX Measures
5. (08:35) Important Data Modeling with Hiding Columns
6. (09:10) Data Model PivotTable
7. (09:50) Attributes that can NOT slice our Report
8. (10:28) Conclusion

.

**Budget V/s Actual Profit Loss Report using Pivot Tables**           2014 09 29

https://chandoo.org/wp/budget-v-actual-profit-loss-reports-6/

This is continuation of our earlier post **Preparing Quarterly and Half yearly P&L using grouping option**. You can also do budget v/s actual comparison using Pivot Tables.

***For this we have to add one more column to our data.***I have added column Data Source to the end of data table. Existing data is marked as Actual and I have added more data rows which are marked as Budget.

.

Budget vs Actual analysis in Power BI

https://plumsolutions.com.au/budget-vs-actual-analysis-in-power-bi/  (90min video)

https://plumsolutions.com.au/wp-content/uploads/2019/04/budget-vs-actual-webinar-files.zip

Learn how to create a multi-dimensional model for your budget vs. actual performance data and how to create interactive reporting on it. Compare retail performance of Aussie Wines (fictional company) over last 12 months and analyse results in Power BI.

.

Financial Modelling using Excel We are going to learn how to build a financial model to do project evaluation using Excel. ****The 7 parts of this tutorial are,

   1.    Introduction to Financial Modellinghttps://chandoo.org/wp/financial-modeling-introduction/

   2.    Building a layout for Project Evaluation Model – Best practices

   3.    Building Inputs and Assumptions Sheet

   4.    Building Projections for Project Evaluation

   5.    Modelling the Cash Flow Statement and Projections

   6.    Putting it all together – Final Project Evaluation Model

7.    Scenario Analysis for the Project Valuationhttps://chandoo.org/wp/scenario-analysis-for-the-project/

.

**Financial modelling is creating a complete program/ structure, which helps you in coming to a decision regarding investment in a project/ company.**Now this could be on a simple piece of paper or in excel. The advantage with excel is that, even if you have calculation speed and accuracy like me (this is one place where I am like Einstien!), then also you would be able to come to the right conclusion!

.

.

Budgeting with Power BI – PASS Austria     2018 04 23

https://www.sqlbi.com/tv/budgeting-with-power-bi-pass-austria/           56min

https://www.youtube.com/watch?v=lByie8Qy22c

Budgeting is one of the most challenging scenarios in the Power BI arena. For example, you might need to summarize sales in previous years and to allocate the budget forecasts. You have to work with data at different granularities and to find a way to author DAX code to compute the forecasts at the desired granularity.

When it comes to budgeting, each company is a unique scenario. In this session, you will see some common techniques to use when building a budget model with Power BI, including previous year allocation, multiple-step budgeting with linked back tables, handling of budget on products that do not yet exist.

.

Calculate percent variance        ABS

https://exceljet.net/formula/calculate-percent-variance https://exceljet.net/formula/calculate-percent-variance

If you need to calculate percent variance you can use a simple formula that divides the difference between new number and the original number by the original number.

This formula can be used to calculate things like variance between this year and last year, variance between a budgeted amount with an actual amount, and so on.

.

Mastering Variance Reports in Power BI  (51min) (Webinar downloaded)2018 04 12-https://zebrabi.com/variance-reports-power-bi-webinar/https://zebrabi.com/power-bi-variance-reports/****Presenting sales vs. plan, cost vs. budget or comparing income statements to PY in a clear and understandable way is the most critical part of a professional business report or BI dashboard.

In this webinar, we will analyze and explain **the most effective methods for presenting variances in Power BI dashboards and reports.**Learn how to create impactful and informative variance reports in Power BI. Start with a simple variance report and work your way towards more complex visualizations with forecasts and small multiples. You'll also learn how to create reports with hierarchical data and profit and loss statements. The blog post also includes a number of handy tips and tricks that will help you become a Zebra BI wizard in no time.

.

Show The Difference From Previous Years With Excel Pivot Tableshttps://www.myexcelonline.com/blog/show-difference-previous-years-excel-pivot-tables/****Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation. You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you are tracking to the previous months, years, days… In the example below I show you how to show the Difference From the previous YEAR

ET PT PivotTables Power Query BI.docx . Show The Percent of Difference From Previous Years With Excel Pivot Tableshttps://www.myexcelonline.com/blog/show-percent-difference-previous-years-excel-pivot-tables/****I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer. One is using Formulas, but that will take time to set up and you are exposed to errors! The other method is the Pivot Table way, which is quick and reduces the risks of making any errors….ah yeah I almost forgot, it is also easy to add new data to your variance analysis!

.

******************* Optional Reading *******************

Here are some more detailed articles about using PivotTables. I have many more links if you are interested, including to some books (free and paid).

.

Forecast vs Actual Variance -Budget Forecast

https://www.contextures.com/excelbudgetvariance.html

Get this free workbook, then enter budget forecast amounts and actual amounts. View or print the report sheets, where formulas show the year to date totals, and calculate the variance between the forecast and actuals. This example uses a simple budget -- enter forecast amounts for each month, then fill in the actual amounts at month end. There are no macros in the sample file -- the navigation buttons use hyperlinks.

.  *  Workbook Menu Sheet

.  *  Enter the Forecast Amounts

.  *  Check the Forecast Totals

.  *  Enter the Actual Amounts

.  *  Show Year to Date Results

.  *  Colour Code the Columns

.  *  Calculate the Variance

.  *  Use Navigation Hyperlinks

.

.

Financial Modelling with Danielle Stein Fairhurst from PlumSolutions Podcast 012      2016 12 08****https://www.myexcelonline.com/podcast/012-financial-modelling-danielle-stein-fairhurst-from-plumsolutions/

Danielle Stein Fairhurst is an MBA and an expert in Financial Modelling.  She is the owner of www.PlumSolutions.com.au, a company dedicated to helping Financial Modellers via her online courses, training seminars and consulting gigs.

.

.

Create Efficient Variance Reports 56min 2016 04 05

https://zebrabi.com/create-variance-reports-webinar/

Learn the most efficient ways to report on variance analysis

Whether it is sales-vs-budget or costs-vs-budget, every company uses variance analysis to track the actual performance against their goals.

In this webinar you’ll learn:

.  *  How to design top 3 most efficient variance analysis reports

.  *  How to use Zebra BI’s page templates to create pixel-perfect reports for PowerPoint or printouts

.  *  In-depth explanations of all types of variance charts

.  *  How to handle extreme values (outliers) in your data

.  *  How to efficiently link your visual reports to your data connections

.

11 Ways of Tracking Monthly Variances in FP&A (Financial Planning & Analysis)-Free Webinar 71min

https://zebrabi.com/monthly-variances-webinar/

Learn best practices for visualizing monthly, quarterly, YTD and other variances

Comparing actual values with previous year, budget and forecast on a monthly/quaterly/year-to-date basis is a core activity for all FP&A, controlling and other financial professionals.

There are so many options for displaying and analyzing these variances that many users can't decide which one to use, so they stick to using plain tables and default Excel charts.

In this webinar you'll learn about all the different visualizations of variances - from the basic to the most advanced.

You'll also learn about all the latest time-tested Excel templates to start designing standardized best-practice variance reports for your company.

In this webinar you’ll learn:

.  *  Proven methods for visualizing variances: integrated variance, hills&valleys, Z-chart, small multiples

.  *  How to visualize current month vs. year-to-date variances

.  *  Using waterfall charts for YTD/FY + monthly/quaterly variances

.  *  Using Zebra BI templates to quickly create variance reports

.

4 SMART Ways to use Custom Formatting instead of Conditional Formatting in Excel (positive numbers ; negative numbers ; zeros ; text)https://www.xelplus.com/smart-uses-of-custom-formatting/ (text version)

Pt 1 (text)- Custom Formatting is MUCH faster for large data setsCH 1: Custom Formatting- The Basics

CH 2: Up/Down Arrows for Deviations

CH 3: Up/Down Arrows & Percentage Deviations

.

Pt 2 (text)- Smart Uses of Custom Formatting

https://www.xelplus.com/smart-uses-of-custom-formatting-part-2/

CH 4: Any Symbol of your choice for Deviations

Alternative method using standard font

CH 5: Up/Down Arrows for Higher Variances

Cases where both thresholds are positive

CH 6: Excel Custom Formatting with Thresholds & Icons: Quick & easy

Setting a dynamic threshold value for custom formatting

Setting a range for custom formatting

.

Pt 1 (vid) - showing variances using symbols - up/down arrows or any symbol of your choice using Custom Formatting

https://www.youtube.com/watch?v=tGY70sdpaLc (16min11)

CH 1: Custom Formatting- The Basics

CH 2: Up/Down Arrows for Deviations

In this video, I show you a simple way of showing variances using symbols - up/down arrows or any symbol of your choice - with Excel's custom formatting. All you need to know is the rule behind custom formatting - i.e. first argument is how positive numbers should be formatted, then how negative numbers should be formatted, followed by how zero values should be shown and last text. Custom formatting can be confusing, but if you remember this simple rule, you're all set.

.

Pt 2 (vid)- Add thresholds to your deviations using custom formatting.https://www.youtube.com/watch?v=faPSXNkU1Y0 (17min08)

CH 3: Any Symbol of your choice for Deviations

CH 4: Use custom formatting to only show higher or lower deviations

.

Pt 3 (vid)- Custom Formatting with Thresholds & Icons: Quick & easyhttps://www.youtube.com/watch?v=qKpxEl-_IQ8 (17min20)

ou'll learn an Excel custom formatting hack which enables you to to show icons for deviations. For example the up & down arrows or icons with different colors based on a threshold. Great thing is, you can have up to 4 different thresholds. You can even define them in excel cells so they're dynamic. If you change the value in the cell, the custom formatting applied to the cells will be updated accordingly.

.

.

Actual vs Budget or Target Chart in Excel – Variance on Clustered Column or Bar Chart                      2013 08 19

https://www.excelcampus.com/charts/variance-clustered-column-bar-chart/

Learn how to display the variance between two columns or bars in a clustered chart or graph. Includes a step-by-step tutorial and free workbook to download. Great for displaying budget vs actual variances.

This post will explain how to create a clustered column or bar chart that displays the variance between two series. Actual vs Budget or Target.

.

Actual vs Targets Chart in Excel

https://www.excelcampus.com/charts/actual-vs-target-chart/

https://youtu.be/E5JTaRJ9VpY (12min41)

Learn how to create a column or bar chart that compares two or more numbers. Actuals versus budget, goal, forecast, target, etc.

When the boss wants to know if we've hit our goals for the month (or quarter, or year, etc.) it's always a good idea to give him or her a visual report in the form of a chart that shows each goal in relation to the actual numbersachieved.

While you can compare these numbers side by side, it looks a whole lot cleaner to overlay these numbers, especially when there are lots of target goals to fit onto the chart.

In this tutorial, I'll explain how to format a column or bar chart to achieve a cleaner look (and impress the boss).

.

6 Best charts to show % progress against goalhttps://chandoo.org/wp/best-charts-to-show-progress/

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts. So lets talk about best charts to show % progress against a goal. .  #1: Conditional Formatting Icons + % values .  #2: Conditional Formatting Data Bars .  #3: In-cell bar charts .  #4: Pies .  #5: Color scales or heat maps .  #6: Thermometer charts .

.

Budget vs Actual: Zero Complexity Model in Power BI 2019 06 18Reza Rad

https://radacad.com/budget-vs-actual-zero-complexity-model-in-power-bi

When I explain the Star Schema and best practices of data modeling in Power BI, Often I get questions such as what if we want to have budget and actuals in the same data model? what if there are two tables with different granularity? We cannot solve it with simple data modeling and relationships, this needs complex DAX calculation! My answer is: That can be also solved with a zero complexity model. In this article, I’m going to explain through an example, how two tables with different granularity (like budget and actual) can be modeled easily using Power BI.

.

Calculations and DAX 2019 06 24    Reza Rad

https://radacad.com/budget-vs-actual-model-in-power-bi-calculations-and-dax

In the previous article, I explained how you can build a data model for budget vs. actual, where the grain of the two fact tables is different. The model works perfectly as a star schema. As long as you slice and dice data in the level of granularity that both tables support, then you don’t need anything else. However, if you want to go to lower grain than what the table supports, then you would need more calculations, and DAX can handle that easily. In this article, I’m going to explain some calculations using DAX that helps to go to a lower grain that what the fact table supports.

.

Financial Planning & Analysis 7 Part series to consolidate tabs and files to generate a Consolidated Profit & Loss statement

Simon Hurst brings together all the techniques covered in the five previous parts of the series and shows how to combine trial balance totals in three separate workbooks.

.

Pt1 Overall introduction to how the Power Tools work- Data cleaning                   2016 11 17

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-with-power-tools

Simon Hurst revs up his Excel Zone mini-series by using Power BI to tackle dodgy dates, numbers that don't add up and duplicates.

This is the first part of a short series that will examine how these tools can replace a whole set of more traditional spreadsheet techniques.

.

Pt2- Healing Excel's Achilles heel: Dynamic Ranges - data from multiple tables 2016 12 09

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-healing-excels-achilles-heel

One area that often causes an issue in Excel is the ability to cope with ranges of cells that need to expand to accommodate additional information – usually adding rows to a table of data. You might have several tables of information that you want to combine into a single table that you can use as the basis for a PivotTable or the use of Excel summary functions.  In this example, we have the sales results for three countries held in Excel Tables in three separate worksheets. We want to combine them into a single Table to use as the basis for a series of PivotTables that analyse the entire set of data. Just to make things a little bit more complicated, some of our columns don't have consistent names.

.

Pt3- Consolidate data in files and folders     2016 12 30

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-consolidate-data-in-files-and-folders

*In the third part of a series of articles, Simon Hurst looks at how Excel's Power Query/Get & Transform tools can be used for a great deal more than just acquiring data from external sources.*We will look at creating a connection to a single workbook to automatically append all the relevant Tables in that workbook, and then look at a connection to a folder which will allow Tables to be consolidated even if they are in different workbooks.

.

Pt4- VLOOKUP() v merging tables– adding product categories               2017 02 03

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-part-4-vlookup-v-merging-tables

in this part we are going to consider how to merge two tables so that we end up with the same number of rows as in the first table, but with the ability to include columns from both tables in the final output. This is the equivalent of using Excel lookup and reference functions such as VLOOKUP() and the MATCH(), INDEX() combination.

In the previous examples we started with basically the same data in different tables and we combined them into one long table by keeping the same columns but appending all the rows. This time we will be considering how to combine tables when we want to include all the rows in one of the tables and add some of the columns from the other table.

.

Pt5: VLOOKUP() v merging tables – approximate lookup ****           2017 03 02

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-vlookup-v-merging-tables-approximate-lookup

The part four merge was based on there being an exact match for each of our sales invoice values. This time, we are again going to merge two tables so that our invoice table includes an additional column, but we are going to assume that our reporting periods are not exact calendar months and we, therefore, need to match each invoice date with a Periods table. This table will include each period end and start date.

We need to create the equivalent of an Excel approximate VLOOKUP() type of match, where a value matches the largest item in a sorted table that is less than or equal to that value.

.  *  Approximate VLOOKUP()

.  *  Approximate lookup in Power Query

.  *  Expand merged tables

Before we start, this approach is more complicated than the techniques we have covered to date and, given that the approximate form of VLOOKUP() doesn't suffer from the same performance issues as the exact form, taken in isolation it would almost certainly be easier just to use VLOOKUP() rather than Power Query for our example. However, it does demonstrate some useful additional techniques, and it could well be valuable as part of an overall Power Query solution.

.

Pt6- Automate management accounting step-by-step          2017 04 11

https://www.accountingweb.co.uk/tech/excel/supercharge-excel-part-6-automate-management-accounting-step-by-step

This time we will go through the process of combining trial balance totals in three separate workbooks and then summarising the totals using a separate coding chart before going on to create a flexible set of management accounts.

.

Pt7- Automate management accounting part 2       2017 04 11

Simon Hurst concludes his two-part series on automating management accounts. The plan In the first part, we went step-by-step through the...

.

Hi, I'm Elise, an independent advisor and I'd be happy to help with your issue.

It is difficult to provide advice without some more context on the structure of the data you hold and how this relates to the description you have given of updating it. If you could provide some samples of what you are updating it would be easier to give some advice.

Kind Regards,

Elise