Hello this video today is going to show you how to use the Excel Fill Handle.
Along with the drop-and-drag editing function,
the Excel Fill Handle is a fantastic tool, which can be used in strategic planning or financial planning.
As this financial model will show, we will firstly go into the forecast Assumptions worksheet
and create a new section called "Sales Volume Assumptions".
Here I will illustrate the power of the Excel Fill Handle.
Firstly, I am going to create some months, January,
I'm going to type in February,
then March.
The great thing about the Excel Fill Handle - it can automate this
input of data.
So by taking the one cell, January,
the Excel Fill Handle will seamlessly and automatically fill down the page
by the respective months.
We can now create our breakdown by month, of our percentage sales volumes in this hypothetical strategic plan.
Again I'm going to show you the power, this time with quarters.
So using quarter one, again I can fill down and the Excel Fill Handle will seamlessly create the drop down.
In this second example, I am going to show how to copy Cell Styles or Cell Formats.
Firstly we are going to create a "New Style"
to illustrate this.
It is going to be a simple input percentage ("inputPercent")
cell Style,
which will be to zero decimal places
and secondly the "Fill" will be in a light blue.
Now we can select this new cell Style like so,
and instead of having to
create this every time; we can simply use the Excel Fill Handle to fill down the page.
And we can now start typing in our respective sales volume percentages by month.
Clearly this saves us immense time and ensures our financial model is best practice, in terms of being formatted in a consistent fashion.
Again we can use this in the Quarters section; so by dropping-dragging our formats for our cell styles, we can achieve the same in our quarter
sales volume breakdown.
Now secondly I am going to show you how to achieve this with Cell Formatting.
We are going to expand our Depreciations, by "IT Capital" and "Capital Extensions";
now at the moment
the cell (Formatted Cells) do not
go to go down to these individual cells.
As we can see here, when we type in "Five" years.
So using the Excel Fill Handle, we can achieve this
functionality seamlessly.
So now we type in "5 year(s)" - fantastic! "10 year(s)" and our Cell Format is standard.
Finally we can copy array formulas of outputs using the Excel Fill Handle.
These first two business units, Acme and Delta;
we can firstly create our
IF Statement,
which will be able to
calculate the output based on whether, firstly it is an actual
number from our Actual financials.
Remember it is imperative to keep our formatting standard; in terms of referencing the columns
systematically,
and secondly by, if the value is False; we apply our Forecast Growth of our
income stream.
By creating a best practice financial model, we can now use the Excel Fill Handle to fill down the page like so,
and again just checking that our formulas are correct.
We can now use the Fill Handle to fill across,
which saves us immense time; instead of having to individually type the formula.
Again demonstrating this
for the Delta business unit.
By creating this formula functionality, our best practice financial model will enable us to use the Excel Fill Handle
in a very seamless and time-consuming way, as well as error-free.
So we can now use the Fill Handle to fill down,
and then fill across.