[FIN 3250] Ch. 5 Pro-forma Balance Sheet - YouTube

(Let's) move to balance you know then okay. So it's going to be balance sheet more or less the same way, okay?

The only thing you have to pay attention to would be the formula and also the depreciation part,

following the same rule.

So let's take a look at the balance sheet, so let's start from accounts receivable

So accounts receivable if you recall from the directions it moves directly with sales

So you will have to write down the the formula that we use before again, okay, so

Start, but it's gonna be a little complicated because we have to kind of go back and forth. Start with an equal sign

Average

We're gonna take the ratio of

accounts receivable divided by sales, okay for 2016 and 2015

So accounts receivable would be here

Divide it by sales. The sales is not located here, so it's probably better you go back to the income statement

Click the 2016 sales part here

Right so you have 2016 sales on the income statement, you click it.

But because you are fixing, you're trying to fix sales because you want to obviously copy and paste

in the near future

So why don't you click F4 twice.

Whenever you're meeting sales,

It's better that you always just put F4 twice in the case you're writing on the pro forma statement

Okay, so right here you have on your formula:

average parentheses and

Accounts receivable divided by sales right and sales you need to press f4 twice

so you have the ratio for 2016 and

Then why don't you

Write down the ratio for 2015 again. You wanted to do that, you have to go back to the balance sheet

Okay, and you want to click

D8 which is 2015 accounts receivable, right?

But then it's kind of hard because the line is too long right, and if you can't somehow figure it out

It's probably better to click anywhere like near that and then just move with your arrow key. Okay, arrow keys

They're located on your keyboard.

You can always move with the cursor on your mouse and also the arrow key

So move your arrow key

so that you can click D8 which is 2015 accounts receivable and

divide it by

Again go back to income statement and click D5 which is 2015 sales right and

then click F4 twice

So you have

the 2015 ratio and

2016 ratio of

accounts receivable divided by sales, okay, so you close the parentheses to take the average and

Then multiply the average itself by the 2017 sales amount. You need a pro forma sales.

Again, this is another sales, then you would want to press F4 twice

to fix the row number

And enter. click enter

So that you have 51,006.

Some of yours might look like

this

51,005.75 like that then it looks a little messy

So why don't you decrease the decimal so that it's just starting with you know, it's just

Not having any decimal numbers, so why don't we move down to inventory okay?

So because you know once you have the right,

correct formula, the beauty of it is that can copy and paste to whatever, wherever it's necessary

so accounts receivable, inventory

Both of them move directly with sales meaning that this formula can be used for inventory as well

so copy and

paste to inventory

Which should have a different number?

Whatever here

Okay, and then since no if you just copy and paste it will copy the format as well. If you want to keep

the original format go to paste option and find the second key second icon

Which says formulas and click it?

okay, and

In terms of the original number just change it to different decimal places so

Decrease decimal so that you'll have just no zero decimal places

All right

Then total current assets. Is everyone ready to calculate the total current assets? So total current assets is gonna

Be just its its subtotal

Subtotal in terms of balance sheet is always the sum of everything like above it

SUM

So start sum

equal sign, sum, open parentheses just

Drag everything above it

Close the parentheses

If you're not really familiar with it you can just add everything, just click Add click Add click yeah, that would do as well

There's not much of time difference, so you could do it as well, so

Let's just move along go to gross fixed assets, right

gross fixed assets

So you have 388,000 for year 2016 and on top of that you newly purchased

An equipment, that's worth 50,000 right. So it's just gonna be the sum of the two so you're gonna add

2016 amount and

The new purchase amount of 50000 you can write down 50000 if you want it

But it's just given to you, available to you at B32 so you can just click it.

You should have 438,000.

So what you basically did was just adding the

Previous amount to the new amount so that you can get the new total.

That's the gross fixed assets and

Accumulated depreciation

That should be whatever depreciation amount you had before like if it's a beginning

plus new

depreciation expense. And the new

depreciation expense is available to you on the income statement, so why don't you go back to the income statement,

click depreciation amounts at B8.

Okay, you just click this part and say okay.

So this just is

mainly adding the new amount to the older amounts and

You should have the net plant & equipment part

That's gross minus depreciation right this is the only part that you were subtracting, not adding.

So gross fixed assets

minus

accumulated depreciation

Should be the net plant and equipment.

It should be 328,160.

Then why don't you calculate total assets. total asset is just simply total current assets plus

net plant & equipment right. You just add these two values. We'll have 454,218.

All right, liability and owner's equity part, it should be even

more straightforward because

I'll show you. The accounts payable and accrued expenses, if you go back to direction what it tells you is

Account payable and accrued expenses they very directly with sales

So what you would have to do is you just copy and paste

What you had for accounts receivable and inventory. You can use exactly same formulas. So copy these two lines

paste

That's it. if you are done, why don't you sum everything

above it so accounts payable and accrued expenses, their sum would total

45,013 okay, that should be total current liabilities and

Long-term debt is available to you and what about total liabilities?

total liabilities will be the sum of total current liabilities plus

long-term debt, okay

so everything here was just very straightforward.

let's see so retained earnings, so let me just write down the formula there

Retained earnings for beginning, okay, which is the 2016 ending balance plus

net income

Which should be available on the income statement go back to income statement,

click the 2017 net income minus

Dividend, I don't know whether you recall it, but projected dividends in 2017 was assumed to be zero

So just click it

So you're adding the

2016 retained earnings and the net income of 2017

minus projected dividends in 2017

and this should give you 66,556.

Okay, this is the new retained earnings balance.

Total owner's equity

It should be the sum of everything above it

So it's gonna be the sum of common stock, additional paid-in capital, and retained earnings

right you just add them all and

You will have your total owner's equity, okay?

And again

It should be total liability and owner's equity

so liability plus

owner's equity

What did I do?

Okay, yeah, so you should have 437,869.