Term Loan Schedule

In a financial model we need to make term loan schedule for projections. It is linked to Balance sheet and P&L statement. Here is a sample Term loan schedule for your reference.

Image

link to file:

https://docs.google.com/file/d/0B2FLZ9YjqpJTSEx5ZGdBbEFrQUU/edit?usp=sharing

Advertisements

Multiple column value addition based on criteria

One of my friend at work asked me Multiple column value addition based on criteria with large data, below is essence of data in short.

Image

he wanted a formula to give the sum of values of a bike sold at two different location, conditions being dynamic(bike,location). like Yamaha sold in Agra and Mumbai with bike and location being dynamic.

 I could come up with these two options

1) sumproduct formula

in two different cell write sumproduct formula and the add them

2) Vlookup array formula

single step vlookup array formula (not really single step)

Image

Solution file is link is below

https://docs.google.com/file/d/0B2FLZ9YjqpJTb2M0REZUYzlhYjg/edit?usp=sharing

Quick tip ‘Watch Window’

When you are making a big financial model, where assumptions are in different worksheet and you want to see the effect of change in your profit by changing assumptions in different worksheet, ‘watch window’ comes in handy.

It essentially saves you from the hassle of going to different worksheets time and again.

From F1 : The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets

Go to Formula -> watch window -> add watch and select the cell/s you want to keep watch on

Watch window appears as separate box, you can embed it above the formula bar by going near the top of box, press right click( plus sign appear), drag it above the formula bar.

If you think you left with little space to see excel, adjust the size of box or double click on the head of any tab, like if you double click on  home tab (where home is written), the ribbon will go giving you space to see, reverse action will bring back the ribbon

CIAO!

Dynamic scroll chart

Today I will show you to make dynamic scroll chart, it is particularly useful in making dashboard, where you have large data point to be shown in single chart

Before I make a quick scroll chart, some background work

1)      Put your data in place (e.g.I have 26 data point in cell B3 to C28)

2)      Paste a scroll bar from developer ribbon

3)      Format scroll bar (I want 6 data at a time in chart)

SBC1

cell link is F2

4)      Learn offset formula

OFFSET formula, Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells.

‘=OFFSET(reference,rows,cols,height,width)’

In our case reference = B2, rows=F2, cols=0/1, height=6, width=1

5)      Using ‘name manager’ in formula tab name range: cell B3 to B8 ‘label’ and C3 to C8 ‘data’

6)      In name manager select ‘label’, refers to(bottom):  =offset(Sheet4!$B$2,Sheet4!$F$2,0,6,1)

7)      select ‘data’, refers to: =offset(Sheet4!$B$2,Sheet4!$F$2,1,6,1)

SBC3

I m making my chart in sheet4, that’s why you see sheet4 reference

8)      Insert line chart with data B3 to C8

9)      Select the line of line chart, following formula appears in formula bar ‘=SERIES(,Sheet4!$B$3:$B$8,Sheet4!$C$3:$C$8,1)’

10)   In the formula delete $B$3:$B$8 and put ‘label’, delete $C$3:$C$8 and put ‘data’, do not delete ‘!’ mark, go to end of the formula press enter

11)   My man, you are ready for scrolling, scroll the bar and your data will change.

SBC2

If you select the line of line chart you can see how your data is moving