Build a DYNAMIC Actuals vs Projections Chart in Excel Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- Build a DYNAMIC Actuals vs Projections Chart in Excel 🤩 Save time each month with excels automation Adding "Actuals" vs "Projections" to your dashboards is CRUCIAL when forecasting & showcasing your work to others. I used to do this manually each month by creating a shape in Excel…adding some text, and moving the marker to the breaking period from when Actuals end and Projections start. Well today, I’m excited to share a way you can do this automatically, saving you tons of time 🤓 ➡️ STEP 1: STRUCTURE YOUR DATA CORRECTLY If you’ve ever created an Excel chart, you’ll know that data structure is key. That means getting your data in a structure in which your data points are ideally in a TABULAR format, with dates & values going vertically. If you have data going horizontally (like most financial reports), you can use a simple lookup function to pull in your values into this new structure ➡️ STEP 2: ADD A NEW COLUMN AND CONCATENATE DATES WITH THE WORD “ACTUAL” OR “PROJECTED” Now let’s add “Actual” or “Projection” in our date section. Add a dropdown for your latest month of actuals, and then add a column in your table to calculate whether you are in an actuals month or a projections month. Use a concat function or “&” operator to join this value with the date, and use the TEXT function to format the date for “MMM-YY”. ➡️ STEP 3: ADD A DYNAMIC MARKER FOR "← ACTUAL | PROJECTION →" The last step is to add our dynamic marker that will adjust the position depending on what month is our breaking point between actuals and projections. To do that, we’ll add a new column that will populate the MAX value of our range only in the last month of actuals. This will help with the position of our marker so that it shows at the top of our values. Add one more column that will display the text “ ← ACTUAL | PROJECTION →” when we are in this month. OK now here’s the key…when you add this new MAX column to your chart, click add error bars. Then edit the error bars, and choose “Minus” and “No Cap”. Then choose custom, and specify values for the range, and reference the MAX range. Now you have an error bar from top to bottom in a nice thin line. Add a callout to the MAX column series, and make it equal to the text column that reads “← ACTUAL | PROJECTION →” Remove the fill from the bar chart so that the error bar only remains, and you’re done! === There are a lot of steps to doing this correctly…and it’s not so easy to explain it all with just text and 1 picture on LinkedIn 😂 ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.
I agree
Experienced Finance Director | ACA | FP&A Expert | Transforming Businesses with Financial Data-Driven Insights | AI for Finance enthusiast
2moGreat post, Josh! Thanks for sharing this valuable technique. Another tip to enhance your Actuals vs Projections chart is to incorporate conditional formatting to highlight any significant variances between actuals and projections. This can help draw immediate attention to areas that need further analysis. Additionally, using data validation to create dynamic dropdowns for different scenarios or versions of projections can make your dashboard even more interactive and insightful. Keep up the fantastic work in making Excel more efficient for financial professionals! 🚀