Auto news on Youtube Nov 21 2018

in the previous lecture we forecasted PP and D and D and a now our focus will be

financial liabilities to be more precise our next task is building a financial

liability schedule let's create a copy of the fixed asset roll forward sheet

and change its name to financial liabilities schedule

there are a few changes that must be made let's delete all numbers first this

ensures we won't use some cells from the previous sheet we will not need the two

gray boxes below the table so I'll delete them

let's change the items in the table the first one will be beginning debt

the second one will be new debt the third one will read principal

repayment and the total row will show us ending

debt very well we know for a fact that no new debt were made in 2015 and 2016

let's fill in historical values by bearing this in mind we can link ending

debt for 2014 2015 and 2016 simultaneously this also gives us

beginning debt given that the ending debt for 2014 was beginning debt for

2015 and the ending debt for 2015 was beginning debt for 2016

okay we can even copy the formula for beginning debt for the forecast period

now we can calculate the amount of principal repayments were made in 2015

and 2016 all we must do is subtract ending and beginning debt for the two

years

excellent during the forecast period things become more complicated the

company negotiated with its lenders to extinguish its ending debt during the

next 10 years the agreed interest rate that will be

paid is 9 percent

and the firm will repay the debt through constant annual payments our forecast

period is shorter than 10 years so we won't see all debt disappear from the

balance sheet good we need to calculate the company's annual payment for the

next ten years Excel makes this easy we can simply use the PMT function

I'll select the loan rate the number of periods in which it will be repaid and

the present value of the amount to be repaid

here's the amount

let's create a mini table that will help us determine what portion of this amount

is for interest expenses and how much the residual debt is that remains after

every repayment

we have ten periods

the four rows of the table should be payment

interest expense debt repayment

and residual debt we said the agreed loan amortization plan involves constant

payment so the payment will be equal to the same amount in all ten periods

interest expenses will be equal to the amount of residual debt at the beginning

of the period multiplied by 9% which is the agreed interest rate I'll negate

this number by putting a minus in front of the formula

the difference between the amount paid to the lender and the amount due for

interest expenses is the amount of principle repayment

and finally outstanding debt plus repayment please note we have a negative

sign here gives us residual debt outstanding at the end of the year the

next year we need to carry out the same calculations

now I can copy the formulas for the rest of the years

you can see we've worked correctly given that the loan is amortized in year 10

awesome let's fill in the table above with the values calculated for principle

repayment

now we can easily link these values with the P&L and balance sheet here we've

calculated interest expenses and this allows us to calculate the P&L until EBT

and then outstanding debt we have can be linked with the output balance sheet

the balance sheet is almost complete in our next lesson we'll build an equity

schedule stay tuned we've done an excellent job so far

For more infomation >> Building a Financial liabilities schedule - Duration: 6:25.

-------------------------------------------

Building a flexible model with Vlookup Columns - Duration: 3:23.

right so far we've seen two ways that would allow you to build dynamic models

and create multiple scenarios would you be interested to see a third way if

that's the case you've opened the right video this is what we are going to do

here vlookup can be combined with several other functions a useful

combination of vlookup is the one with columns which serves as a counter for

the number of columns let's show how this can be applied in our example the

lookup value we'll need is the scenario we've selected I'll anchor this cell

reference the lookup table where we will look is

in the entire range from B to M the function will look for lookup value and

whenever it finds it it will give us a result from that row the third input it

needs is a column number but I'm sure you'll agree this isn't a

dynamic function is it if every time we use vlookup we have to pause and count

column numbers so it would be very inefficient this is where columns comes

into play proficient users use columns every time they use vlookup the function

works in a very straightforward way I only have to select the part of the

table we've arrived at and Excel will count the number of columns in this

range simple as that if we fix cell references properly the

next time we paste the function it will have one column more given that I've

fixed only the starting column reference easy right

let's use this function for the two items below

perfect as you can see we've obtained the same result as the previous two

videos dynamic scenarios great let's use selected case values to fill in the P

and L lines we have above revenues in 2017 can be obtained by multiplying 2016

revenues by 1 plus forecasted revenue growth

the same is valid for 2018

2019 2020 and 2021

cogs will be given by revenues times projected percentage of revenues for

that year

the same holds true for OPEX

and this gives us P&L figures at the EBIT a level in our next lessons we will

go deeper and we'll forecast the firm's entire P&L balance sheet and cash flow

and the best part is this is a dynamic model that allows us to switch between

scenarios in the blink of an eye

For more infomation >> Building a flexible model with Vlookup Columns - Duration: 3:23.

-------------------------------------------

Where do I start to build a podcast? 001 PODCAST PRODUCERS PODCAST with Neil Mossey - Duration: 10:10.

Hello, I'm Neil Mossey, welcome to the Happy Hut and thank you for clicking on this.

Welcome to the very first episode of the podcast producers podcast.

I feel like this should be music playing at the moment or something...

That will come in time with other episodes but this is the first one and I am very

excited. And by excited I mean I'm terrified. I've got this going out on YouTube, on my

YouTube channel... Hello YouTube, if you're watching there.

And if you're listening on the audio podcast there's a link in the description to where

the YouTube videos are. And if you're watching on YouTube there's

a link in the description for where the audio podcast is and where the website is.

So really pleased that you're here. If you're watching on YouTube at this point I'm probably

going to stop looking at the camera now just so I can give my all to this as an audio podcast,

but you're in good hands. I've actually already recorded the first episode

but I wanted to record and upload this thing first. I wanted to record an introduction

partly to test to make sure that everything works but mostly I just wanted to get an episode

in the can and online to get the Podcast Producers Podcast going.

My background is in TV, radio and online production. Well, the things I've produced and written

- maybe there's one or two of them that you might have heard of if you are in the UK but

it's a lot of stuff - I've recently made a couple of podcasts and

one of them has turned out wonderfully. I'm really really proud of it. It's... it's

a closed series - it's just a small number of episodes. I'm really pleased with how that's

come out. But the other podcast is an open and ongoing

series. And I've hit this complete wall with it.

I can only describe it as a podcast wall. I can't get past it, so I've started reaching

out to people I know who've been making podcasts and what's been really comforting

is that the people I've been talking to - it looks like they have a podcast producers wall

as well they have obstacles that they've had to get around and they're actually grappling

with different problems that I haven't even thought of yet.

Yesterday I went to have a chat with one my really closest longtime friend Stuart and

he is involved in a podcast that has recently been released. We were talking about some

of the things that's come up, and how he's actually got this podcast made and we were

talking for for a while and we're going so deep into talking about podcasts and and how

he make them that I asked him if I could record the conversation.

I got my phone out and put it on the table - it's in a coffee shop - busy coffee shop

- I'm just recording it on my phone with no microphone and I'd never do that.

I'd never set out to record a podcast just on my phone with no microphone in a busy coffee

shop, on a table, and uh but we kept talking for an hour we didn't stop talking. And it

was a really good chat and I want to share that chat, so I'm going to release that as

the first interview episode. It will be the next episode because I want to share this

complete journey with you and I'm going to do that by making it better as I go along.

I am going to try and video them too so that you'll be able to see... you'll be able to

see the environment that I'm recording in. You might see some of the equipment or some

of the other things that we'll see. I'm not sure if that's a good idea. I'd like

to put all of the podcasts on YouTube anyway in audio form. I'm not sure if it's a good

idea having the camera because what I like about podcast is the intimacy and I'm worried

that the that the camera is going to get in the way of that - but it's just an experiment

so we'll see we'll see how that goes. I think my biggest fear is that this is a

podcast about podcasts and I'm worried that it comes off the eye that the best idea I

could come up with for a podcast is a podcast about podcast. It feels really route one but

it absolutely comes from a genuine place. I do really want to share the process and

share other podcast producers talking about the process in real time.

When I got home yesterday I sat down and I wrote down what I want the podcast producer

podcast to be, and it came out like this: I want it to be prolific

I want to meet with as many podcast producers as possible

I want to publish as much material as possible and wherever possible I want to

make this podcast for free I want to see if it's possible to start making

a podcast with no money or as little money as possible.

I mean, I know I've got the camera, and I've got a microphone.

I'm doing that because I don't have a a good audio recording device so even using the camera

for me was my little workaround for making a podcast.

I want to do this in a way where anyone can use what they've got to hand to put their

podcast idea online. Podcast producers are - I think - are anyone

involved in making a podcast even if you're just thinking about doing it, if you're listening

to this you're a podcast producer - whether or not you've actually got a podcast yet.

You're a podcast producer. I'm hoping that if I can keep this going long

enough that anyone will be able to share their ideas or to share tips or to put questions

that other people - other podcast producers are facing - and that will all help each other

out. I want to talk to as many people who have

a podcast going to help them find a new audience for their podcasts and finally I really want

to try and get as many people as possible to make their own podcast and if I could just

stumble through this process and share everything with you I'm hoping that you'll see that you'll

be able to do this. There's so much - as you know - because you've

looked this up there's so much that you need to get together and any one person will have

certain skills you might be good at talking to people or booking guests or you might be

good at operating the audio equipment or you might be really good on the computer.

So you might be really good at editing the audio and putting it on a website.

You might be really good at social media, so you might be really good promoting

your podcast. But there are so many aspects just to get this thing out of your head and

online as a completed podcast stream... I think there are very few people who can

do all of that and I think that between us between however many people watching this

- I think nobody's listening to this podcast - I think no one will listen to this episode.

That's what's getting me through actually recording it and uploading it - but if

anyone is going to listen to it they're going to have different skill sets and they're

gonna have different ideas and they're going to have different experiences and

I think the more that we can share those the better.

So that's it - thanks so much for joining me for the very first podcast producers podcast,

with Neil Mossey episode 001 "Where do I start to build a podcast?"

If you've got this far - if you have listened - I don't believe anyone is but if you have

listened to this I would love it if you were able to say hi to me.

There's ways to contact me in the show notes on the audio podcast and on the YouTube video.

I've put all my contact details in the description but you can also leave a comment or give me

a thumbs up. Also I'm taking a guess that you listen to

loads of podcasts so you know the importance of subscribers. So if this is a journey that

you might want to come with me on - if it's a journey that you want to share with me - it'd

be lovely to see you there as a subscriber but anyway I hope this helps.

It feels presumptuous of me to ask you if you've got any questions but if you have got

any questions I'd love to hear them and let's do this!

Podcast Producers Podcast. Hello, please help my daddy get 1000 subscribers,

just click on his face thanks bye!

For more infomation >> Where do I start to build a podcast? 001 PODCAST PRODUCERS PODCAST with Neil Mossey - Duration: 10:10.

-------------------------------------------

Using the PMT function in order to create a complete loan schedule - Duration: 6:17.

right so in this lesson we will address one of the fundamental topics in finance

bank loans and the way their payments are structured I will show you how to

perform calculations typically made for you by bank employees it is a great idea

to be prepared before your conversations with bankers as this will put you in a

stronger negotiating position in addition you will see how interest rate

should be calculated when payments are due monthly furthermore you can

distinguish between capital and interest payments something most people cannot do

we will consider a bank loan that involves constant monthly payments

throughout its entire duration imagine a person wants to buy a house that costs

$300,000 he wants to assess whether this is feasible after talking to some

friends and seeing several mortgage loan advertisements he concludes the current

market rates are between 2% and 4% so he makes his preliminary calculations by

using a three percent rate fine he wants to repay the loan in ten years

by making equal monthly payments throughout this period here is the

million dollar question how much will his fixed monthly payment be for him to

extinguish the loan in ten years Microsoft Excel can fully answer this

question let's type the inputs on this sheet the number of periods during which

the loan will be repaid is 120 given that the person will make equal monthly

payments for 10 years the interest rate he will have to pay for borrowing the

money is 3 percent annually to obtain the monthly interest rate as usual one

should divide the annual interest rate by 12 this is what we will do here okay

very good and finally the amount borrowed is $300,000 we have all the

inputs that would allow to calculate the monthly payment for the

loan the Excel formula for this calculation is PMT as in payment I will

type it in the formula bar and select each input we just entered okay

our formula is ready the borrower must make a monthly payment of two thousand

eight hundred ninety six dollars for ten years to repay the loan heavy monthly

expense right let's go a step further and disaggregate what portion of each

payment is interest principle and how much is the remaining debt I'll create

five columns period payment interest principle and residual

debt

okay the first column will indicate the number of the respective payment 1 2 3 4

and so on - 120

we already calculated the amount of each of the fixed payments here it is I'll

link to the cell and then fix its reference to be able to copy it to the

last row of the table okay perfect our next task is to

disaggregate this payment in two parts interest rate and principle in the first

period the amount of interest that will be accrued is based on the whole debt

that is drawn and given that no payments have been made we have 300,000 times the

interest rate

which equals 750 the difference between the payment and the amount of interest

payments is the amount of principle that is repaid

in the last column we can calculate the residual debt that remains after this

payment

you

good let's do the same thing on the row below I'll multiply the interest rate by

the residual debt fixing the cell references of the interest rate

then I'll take the difference between the payment made and the amount of

interest

this is the principal repaid in this period

okay let's calculate the amount of residual debt by taking the difference

between the amount due before this period and the principal repaid during

this period

here's our residual debt after period two

we can copy these formulas to the last row of the table

you

you

as you can see the residual debt at the end of the loan life is zero so we

worked correctly the debt will be repaid after ten years this is how we can

calculate an entire loan schedule and exercise that allows us to plan better

and understand exactly how much and for what we are paying thank you for

watching

For more infomation >> Using the PMT function in order to create a complete loan schedule - Duration: 6:17.

-------------------------------------------

Building a flexible model with Choose Match - Duration: 1:45.

so the combination of offset and match is one way to make forecast scenarios

dynamic we learned how to use plenty of functions so now we can exercise some

other combinations too what about the choose function it's very

popular for scenario building let's see how it can be applied in our case I will

type choose the first argument of the function is its index value which is a

number one two three four etc then the next few arguments we need are the

scenarios which are selected so for this cell we want to select the three values

below which is the function that would allow us to obtain a number based on the

name of the scenario we've selected yes we need the match function the lookup

value is up here I will anchor its reference and then the

lookup array is here let's fix its column references

the third argument is zero as usual perfect match we'll look at the selected

scenario and then provide its number within the selected range of three cells

the number will be from one to three and that's exactly what chews needs a number

from one to three that helps it decide whether to select

scenario one two or three this is how we can build dynamic models with choose and

match

thanks for watching

For more infomation >> Building a flexible model with Choose Match - Duration: 1:45.

-------------------------------------------

Create a Code The Best Way to Organize Your Data and Work Efficiently with It - Duration: 1:55.

when we have a large extraction we need to be sure that we will be able to

manage it efficiently a good way to do that is by creating a code which

summarizes the important information about each line item moreover it has to

be different from the codes of the other lines what we see in the data is that we

have many rows with the same code C column F the difference between them

lies in column C under partner company they are the same P&L account but

against different partner companies it would be excellent if we build on our

current code and create a new one which contains the information regarding both

the P&L account and the partner company we can do this by putting together the

two fields using the and function should be sufficient for this task we have plus

f5 and C 5 and now the result is a bigger and stronger code which would

allow us to manage the information efficiently let's do this for all rows

I'll copy the formula down until the last row ok so this is how we will have

a unique code which will contain contemporaneously the information

regarding the P&L account and the partner company one last thing to do is

to copy all the newly created codes and paste special over them with values the

shortcut for paste special is alt e and s this operation was necessary because

it allows us to copy our code in other locations next we'll see how to organize

data from three different years next to each other thanks for watching