Claude + Excel for Finance | Episode 5 Data Visualization

Finance For The Future
12 followers
AI Generated·May contain errors
Most finance professionals can build charts but very few can build charts that actually influence
decisions because most Excel charts are static. They show what happened in the past but they
don't help you explore what is happening now or what could happen next and building better charts
in Excel that usually means more time, more formatting and more precision. Waterfall charts
are complicated. Gauge charts are impossible to build in Excel directly and interactive charts
take usually hours to build properly. But what if you could just describe what you want and let
Flodd and Excel build it for you. In this episode, I am going to show you how to use
Flodd inside Excel to build three powerful chart types that are frequently used in FBNS.
First, a waterfall chart to clearly explain the movement from budget to actual. Second,
a gauge chart, one of the hardest charts to build manually which is used frequently for API tracking
and third, a dynamic chart where you can change the scenarios and instantly see the impact on
the KPIs. The goal here is not just to create charts, it is to move from static reporting
to interactive financial storytelling. So let us start with the first use case, which is building
a waterfall chart using Flodd inside Excel. Hi there, most finance professionals know how
to calculate balances. Very few know how to visualize them properly and when leadership
cannot see the story, the analysis loses impact. In today's episode, I am going to show you how to
create waterfall charts inside Excel using Flodd and if you want to practice along with me, you can
download the Excel file from using Flodd inside Excel course from the links in the description.
So let us get started. First, let us try to make the chart using Excel manually. Okay, so here we
have the data. We have our EBITDA number for 2023 and also we have our EBITDA for 2024 and you can
see the walk between the 2023 EBITDA to 2024 EBITDA. So to create a waterfall chart manually in
you can select the waterfall chart option and click on okay. As you can notice, we have the
waterfall chart here. Let me get rid of the grid lines and also if you can notice the end
total is not set properly. So I will select that particular bar only and then right click on it and
set it as total. Now if you can see, you have a waterfall chart which shows your EBITDA number
for 2023 and how it increases and decreases and gets to the 2024 EBITDA. Okay, I will make the
color of the bars on the either side blue. Let me make those blue color. Okay, and typically the
ones that are positive are shown as green. You just select the ones that are positive, you should
make that green and the ones that are negative, they are shown as red and you don't need the
legend. You could say EBITDA for 2023, 2024. Okay, now let us try to do the same thing using
Flodd inside Excel. So I'm going to my home menu in Excel and I'm selecting the Flodd add-in and
on the right hand side if you notice, you can see the Flodd. Let me clear the past historical chart
and I'm giving it a new prompt. Create a waterfall chart using the given data set. I am adding
another instruction that ensures that the end point is set as total. What I've noticed is when
I create waterfall charts using AI prompts, it creates a chart properly but it doesn't set the
end point as a base for a total which is the final step in the waterfall chart creation manually. So
let's see if it is able to do that. I have not been able to do that in the past. Okay, so now it
says I'll start by reading the selected data to understand the data set and also check Flodd for
existing entries. It says I can see the data set clearly. Let me now create a waterfall chart.
So if you can notice the waterfall chart has been populated. It says the waterfall chart is ready
and you can see the EBITDA bridge. However, if you can notice the end point is not set as total
which is what I had anticipated. So let me just right click on it and set this as total and now
the waterfall chart is what you would usually have created using Excel manually. Today you saw how
Flodd built a clean revenue bridge with just a single prompt and it was able to create a chart
almost perfectly but the total still had to be set manually. That final set as total step still
required human intervention. Maybe that's the last frontier. If any one of you managed to crack that
final automation step, let me know. On a quick side note, if you are serious about growing your
career in FP&A, then do check out our certified global FP&A professional program. CGFPA is a
six-month accredited certification in FP&A designed to help you become real business partners. If that
sounds something you would be interested in, links are in the description. Also, there are a bunch
of free resources that you can download on FP&A including books and free courses. You will find
all the links in the description. Creating these charts in Excel is extremely painful. There is no
built-in option. You have to create a hack around it. You start with creating a donut chart, then
overlay a pie chart over it, use some helper columns, make a lot of angle adjustments and then
after 20 to 30 minutes, you might get it right. In this use case, I am going to show you how you can
create a fully functional gauge chart using Plod inside Excel using just one prompt. But before we
jump into Plod, let us first understand what we have. This data set includes performance categories
like poor, fair, good, great and excellent, each representing a segment of the gauge. Then, we have
a pointer data which is a value, a very small needle and a remaining portion. This is the
classic structure required to build a gauge chart in Excel. Now, here is a challenge. Excel does not
have a gauge chart button. So, traditionally, you would create a donut chart for the segments,
add a pie chart for the needle and then overlay them. Hide the bottom half, rotate the chart format
and adjust the colors manually. Okay, so I am inside my Excel and I am going to my home menu and
using the Plod ribbon inside Excel or the Plod add-on inside Excel, I am giving it a simple
prompt. Create the donut chart and the pie chart required for the gauge chart. Use proper colors
and angles so when we overlay the pie chart over the donut, it appears a proper gauge chart. Let
me hit enter and let's see what it does. It says that I start by reading the data to understand
the gauge chart requirement and then build the donut and pie chart. It says now I understand
the setup and the gauge chart is built by overlaying a pie chart on a donut chart. It says
I will create a donut chart with five equal installments which is poor, fair, good, great and
then I will create a pie chart and it is giving the details of it. Let me set up the chart data
properly for the gauge chart. If you notice, the donut chart has already been populated
and now it is populating the pie chart which is the pointer part of the gauge chart. It says that
now I need to customize both the charts with proper colors and rotation angles and make the
bottom segment transparent. Let me apply all the formatting. You can notice it has already
populated the needle as well required for the chart. It says now both the charts are created.
Now, let me also make the data labels invisible on both the charts to ensure that the pie chart
has no background which is what actually we also do when we create this manually.
Gauge charts are commonly used for KPIs, performance tracking, target versus access
but most finance people avoid them because they are too complex to build.
Now, that complexity is gone. Remember, this is not just about charts. It is also about removing
friction. When building visuals becomes easy, finance professionals can then focus on what to
measure, what to track and what to improve. So, in this use case, you saw how to build a dynamic
gauge chart or a speedometer chart all using cloud inside your Excel. Most finance professionals can
build charts but very few can build charts that actually help in decision making because what
most of us do is build charts that are static. They show us what happened in the past but they
rarely help you explore what could happen next. In this example, I am going to show you how to
build dynamic and insightful charts. Before we use cloud, let us understand what do we have here.
So, we have a data set with your assumptions. Revenues, costs, profits across multiple periods.
This is typically the FP&A data. Normally, you would start with inserting a chart, then picking
the chart type, formatting it, adding labels, adjusting colors and after about 10 to 15 minutes,
you will have a chart but it is still static. It will answer the question what happened and not
what we should do next. Why traditional charts have limitations? They are static, hard to modify,
time-consuming to format and most importantly, they are not interactive.
If management asks you what happened if the revenue grows by 10%, you can't answer that
instantly looking at the chart. You will have to go back, update the data and rebuild the chart.
Now, let us change that. So, I am going inside my Excel and then I am going to my home menu
in the Excel level and straight away, I am going to my cloud Excel add-in. Here,
I will give it a quick prompt saying that based on this data set, could you create a dynamic chart
that updates automatically based on the specified scenario. Let's see what happens.
It says that let me start by understanding the data in your spreadsheet. So, it is trying to
read and understand the data structure. Now, let me get the exact formulas and formatting to
understand how the data is structured. It says that I can see the assumptions table row 2 to
row 11. I can see the P&L table which is row 14 to row 27, actual for 2024, budget for 2024 which
is the base version but the best and the worst budget columns appear to be empty. So, first,
it is updating the best and the worst case assumptions. So, if you can notice the base
case, the profit is 22 million. In the best case, it is 2.2 million and in the worst case,
it is 1.5 million. So, this will be okay. Now, it says let me create the dynamic scenario
selector and dynamic chart data selection. Let me add a data validation dropdown for
the scenario selector and build dynamic chart data. So, it is now building the dynamic chart
and also the data required for that to be able to come to life. So, on the right-hand side,
it has started populating some helper data sets so that which it can use to create the chart.
Let me just quickly open what it is. So, these are the key drivers for the actual and the budget
base case scenario. So, you can see the revenue here as per the 2024 actual and the 2024 budget
which is the base case scenario. And when I go here and I change the dropdown from best to
base to best, I can see the charts changing dynamically. So, that is what I meant by
Plot being able to create dynamic charts versus static. Remember that finance is not just about
charts. It is about decisions. Charts are just tools. But if your chart is static,
your thinking also becomes static. With Plot, your charts become faster to build,
easier to modify, more dynamic and hence more insightful. In this example, you saw how to
create a chart using Plot inside Excel, structure it properly and start making it interactive.
What earlier used to take minutes or hours now just takes one prompt.
All right. So, today you saw something very important. Plot did not just help us build
charts. It changed how we use charts in finance. We started with a waterfall chart turning numbers
into a real story. Then a game chart simplifying performance into a single visual. And finally,
we created a dynamic chart where you could interact with the data and explore scenarios
in real time. Now, that is a real shift because charts were never the problem. The problem was
the effort that it took to build them and the limitations once they were built. Now, that
effort is almost gone with the help of Plot inside Excel, which means the value now shifts to what
you are trying to understand. What story are you trying to tell? And most importantly, what
decisions are you trying to support? Most finance teams are still building charts, exporting them
to PowerPoint and then presenting them as static visuals. Meanwhile, tools like Plot are helping
you build charts instantly, which are dynamically updated and think visually with your data. This
doesn't replace your Excel. In fact, it upgrades how you use it. If you want to practice these
use cases, check the links in the description below. And if you are serious about learning
how to use Plot inside Excel for a period, we will also be covering this in our live
GDFS sessions and the AI for finance in a few months. Also, don't forget to check out a bunch
of free resources, including books and free courses. All the links are in the description
below. Remember, the future of finance is not just analyzing numbers. It is thinking with them.
I'll see you in the next episode.
