Course Notes Day 1

The Power Modeling Bootcamp: Course Notes
The Power Modeling Bootcamp: Advanced Excel in 10 days
Introduction & Formatting

Why Excel and Why Modeling?
Excel and financial modeling go hand in hand.

A financial model’s main goal is to reduce complexity and uncertainty. It can be very detailed or
rather short, but should always aim to be easy to understand and work with.
It is a tool that has gained substantial popularity in the modern corporate world.
Professional Formatting
Name of the spreadsheet - Arial 12, Bold; Dark blue

Highlighted text – White font color, Bold, Dark blue fill;
This formatting refers to the forecast period 2017 - 2021
Header - Bold, Italics, Header - Bold,

Dark blue bottom line; Dark blue letters, Dark
Displays the currency blue bottom border
and the unit of line;
measurement Represents the
forecast period
Subtotal - Bold,
Thin black top border
line;
Total – Bold, Thin top border line, thicker bottom border line; Shows total values Displays the subtotal
values in the table
Note: Remember – words are aligned to the left and numbers are aligned to the right
Professional Formatting
Cell Styles
After you select a cell, in the Cell

Styles section you can create a
New Cell Style.
Cell Styles
A small dialog box opens up. The characteristics of this

formatting are shown here. You can see the default formatting
of numbers, cell alignment, font, and so on. You can choose
which ones should be applied by ticking or unticking some of
these boxes.
By clicking on Format, all aspects of a cell’s formatting can be

readjusted.
Now, all you have to do is name this style. We recommend you

use names that are intuitive.
Once you have decided on a name, click OK.

Cell Styles
The next time when you open Cell

Styles, you can see a new section
called Custom. And within Custom,
you will see the new cell style you
created.
You only need to create new cell styles

once. And once you do that, you can
use them in all new spreadsheets.
Format Painter
Format Painter copies the formatting of a

selected cell or a group of cells, and Excel
is ready to apply it to other cells in the
spreadsheet.
Paste Special
An alternative way to do that is to

copy the cells whose format you
would like to carry over, select the
range that must be formatted, and
use Paste special.
You must either expand the Paste

icon or use a keyboard shortcut.
Format Cells
Right click on a cell and press

Format cells. Adjust the
settings in the dialog box.
When the values you are

working with are large, you can
remove the figures behind the
decimal separator (Decimal
places: 0). Since we are talking
about money, the thousands
separator is indispensable.
Make a snappy check of the

sample value. It shows how
the numbers will look like after
you press OK.
Format Cells
As you probably know, Excel doesn’t work

only with numbers. Its cells can contain
information about text, date, time, currencies,
percentages, values, fractions, and other
types of data.
Format Cells
These shortcuts can save you time when you need to change the cell formats quickly. Each combination
starts by holding the Control and Shift keys. And then the third key is a number from 1 to 6. Remember them
as Ctrl, Shift plus one of the numbers from 1 to 6.
Custom Numbers
Rules:
1) # - a digit
2) Symbols in “ “
Exceptions:
~ ! $ % ^ & .
Conditional Formatting
Conditional Formatting highlights cells from a given table

according to a rule that reflects the information shown in these
cells. Conditional formatting can serve as a quick rule of
thumb that helps you visualize and read data.
We can find it in the Home Tab. Note the different

visualization tools it offers– data bars, color scales, icon sets.
All of these can be applied to improve data readability.
For example, in Icon Sets, we can create the so-called traffic

lights formatting. we advise you to stick to a slightly more
professional approach: open the More Rules dialog box and
specify the rules and the threshold values yourself.
If a number is above a certain threshold, it will be

colored in green. If it is lower than the threshold, but
still higher than a lower bound threshold, it will be
colored in yellow. And then if it is lower than both
thresholds, it will be colored in red. If you specify no
criterion to be used to format the data, Excel will do
that for you automatically.
The approach we propose is to use numbers, which

makes things much easier. Just type in the numbers
as decimals and everything will be perfect.
Now the formatting is much more meaningful – you

see the positive values in amber and those above ten
percent in green. Everything below zero is red. This
means you consider a change larger than ten percent
good, between zero and 10 percent just ok, and
anything below zero as negative.
Finally, if you want to get rid of a rule, it will

suffice to expand the already well-known
conditional formatting menu and then clear the
rule. The quickest way to do that would be to
clear the entire sheet.
Filter by Color
To filter by color, you will have to create a filter.
Then once we’ve done this, you can select the type
of filter that will be applied to the rows of the table. If
you only want to filter rows colored in yellow, in the
middle of the drop-down list that opens, you can
select Filter by Color. Press the yellow rectangle
and only the highlighted cells of the table will be
displayed.
Alternatively, you could filter by No Fill, which would

mean you would see all the cells in the table that
have no color applied to them.
Excel Macros
An Excel Macro is a set of instructions that can

be triggered by a shortcut, toolbar or an icon in
a spreadsheet.
Click Record Macro. Give it a name. Press

“OK”.
From this moment on, every action you do is

recorded.
A piece of advice: use macros which work on a

single sheet.
Do not record macros that involve operations on

multiple sheets as this carries risks for errors.
Excel Macros
It can be any type of action – typing formulas, text, formatting

a sheet… anything.
Once you record it, you’ll be able to replicate the same
sequence of actions on a new sheet.
Once you have done that, you can click on the Stop
Recording button.
Every time you create a new sheet you’ll be able to format it
easily with the macro you’ve recorded. Go to the Macros
button and select the macro that you saved. Press the Run
button.
Excel is doing the operations you carried out while recording.
You can use macros for any kind of repetitious operations in

order to save time and be more efficient.

Course Notes Day 1

Uploaded by

Copyright:

Available Formats

Course Notes Day 1

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Course Notes Day 1

Uploaded by

Copyright:

Available Formats

The Power Modeling Bootcamp: Course Notes

The Power Modeling Bootcamp: Advanced Excel in 10 days

Introduction & Formatting

Excel and financial modeling go hand in hand.

Name of the spreadsheet - Arial 12, Bold; Dark blue

Header - Bold, Italics, Header - Bold,

After you select a cell, in the Cell

A small dialog box opens up. The characteristics of this

By clicking on Format, all aspects of a cell’s formatting can be

Now, all you have to do is name this style. We recommend you

Once you have decided on a name, click OK.

The next time when you open Cell

You only need to create new cell styles

Format Painter copies the formatting of a

An alternative way to do that is to

You must either expand the Paste

Right click on a cell and press

When the values you are

Make a snappy check of the

As you probably know, Excel doesn’t work

Conditional Formatting highlights cells from a given table

We can find it in the Home Tab. Note the different

For example, in Icon Sets, we can create the so-called traffic

If a number is above a certain threshold, it will be

The approach we propose is to use numbers, which

Now the formatting is much more meaningful – you

Finally, if you want to get rid of a rule, it will

To filter by color, you will have to create a filter.

Alternatively, you could filter by No Fill, which would

An Excel Macro is a set of instructions that can

Click Record Macro. Give it a name. Press

From this moment on, every action you do is

A piece of advice: use macros which work on a

Do not record macros that involve operations on

It can be any type of action – typing formulas, text, formatting

You can use macros for any kind of repetitious operations in

You might also like