Cost Calculations Using an Excel Spreadsheet.mp4

Cost Calculations Using an Excel Spreadsheet.mp4

Here we are in beautiful Baja Tustin at
the McNeil International Film Studios and I’m here
to talk about how to do an Excel spreadsheet that calculates the
costs for a firm. This is micro economics – so here’s how
it goes. I’ve given you a a series of quantities and then the total cost. Here is cost. I’m just going to fill them in. total total total those are the 3 totals; total cost, total fixed, total variable. Then average; average average and then good old marginal. Sorry, this is quantity. Then total cost, total fixed cost, total variable cost, average cost, average fixed, average variable, and marginal cost. Then total fixed cost, cost, cost. There we go. Now I’ve got the labels all done and I’ve shortened the columns a little bit, I’ve made them narrower. The way you do that is, up here, you select them all and then grab any of them right at the top there and move them, that will narrow it down and make it a little wider. … a little bit wider. There. Now, Total cost – let’s see, I’ve got this that’s got all of the shorthand and then the names, and then these are the formulas: total cost is equal to total fixed plus total
variable. Average cost is total cost, at whatever quantity you are dividing by. Average fixed cost, you take the total fixed cost and divide it by the quantity you’re talking
about. Average variable cost you take the total variable cost of
producing whatever qualities you wanna calculate and then divide by that
quantity. Marginal cost is the change in total cost divided by the change in quantity. It’s the cost of that particular unit, not some averaged cost. OK, so let’s start filling them in here. If you have total cost and you are producing zero units, thats the fixed cost, so 150. And once I’ve got that, since fixed cost
don’t change with output, you just take this little box, that little box in lower right hand corner and drag it down and that gives you the (I don’t like all those
dollar signs, sorry), so I have to… currency, make it a number. OK, then total variable cost, we know that total cost is equal to total fixed plus total
variable. So I’m gonna calculate total variable by
subtracting total fixed cost from total cost. So I put in a formula here; total cost, whoops, a formula starts with an equal sign – there’s
an equal sign, then total cost, just click on the box that is
total cost, then the subtract sign and total fixed cost, and then return because my formula is complete. Yes, you can’t touch anything else because you’ll include that in the formula. OK, so I’ve got this formula in for total variable costj. That little blue box down in the lower
right-hand corner of that cell, just drag it down. There we go. And you can see there they all are. (Once again I need to get rid of these currency signs. I’m going to just make it a general number.) There – and I missed one here so I just take it and it will just carry it forward. There we go. Now, average cost is undefined at zero. So I’m just going to put something here to
say it’s undefined. There. This is average fixed and average variable. There, and now we want to calculate the average cost
when we produce two units. So we total cost of two units – (I’m going to make a formula) equals, the total cost of producing 2 divided by – the divided by
sign here – a quantity of two and then return to close that formula. Now I can
take and just drag these – select the lower box, drag it down, that repeats the formula. There it is. I’ll get rid of the dollar signs in a minute. Average fixed cost: we take the total fixed cost of whatever quantity we’re talking about here, 2 in this case. So the total fixed cost equals – I’m going to make it a formula – total fixed is that cell and then I’m going to divide by the quantity. and return to like that formula and there it is. Divide one hundred fifty
dollars of total cost by a quantity of 2, and seventy-five dollars is
your average fixed cost for each of those 2 units. So I’m going to take that little box and drag that formula for all of these. Now, for average variable cost, you take the total variable cost of producing this quality, so now we’re going to do this 2 quantity again, the total variable cost
is $22 dollars. So I am going to make a formula. Average variable cost is going to equal the total variable cost of this quantity, 2 units, divided by sign, the quantity, return, and there it is. Select it, and pull it down for the formula. There we go. And finally, marginal cost. Marginal cost
is also undefined 0 units of output. Now, marginal cost is the change in total cost divided by the change in the quantity produced. These are all the formulas down here. We want marginal cost. It’s the
change in total cost divided by the change in quantity. The quantity changes from 0 to 2 and the
total cost increases from $150 to $172. So let’s put that formula in. Parentheses sign, oops, equals first, now perenthesis sign. To get the difference in total cost we take that total cost and we subtract the previous total cost. That will give us the
change in total cost. Close the prentheses and then we
divide by the change in quantity so get my prentheses back – this one, minus that one. That will give us the change in
total quantity. Close parentheses, done. So the total cost increased from
$150 to $172, that’s an increase in total cost of $22, divided by a change in the quantity of 2. So 22/2 is indeed 11. And once again I can just
copy is formula straight on down. There it is, the marginal
cost for all of them. Now I want to get rid of all these extra dollar signs so I go over here to number. I want to format a number. I’m just going to make it a number. Will it work? Yip. And over here I have a problem there are all centered and I want it all lined up nicely on the left side so I will click that one. There we go. And now they are all lined up
beautifully. I don’t know why this is all bold. So, get rid of the bold. And there we have it. We have all of the
calculations for a firm – this is a firm in pure competition. This is the simplest look at Excel that
you’re gonna get. I have tried to do it in just a few minutes, so
that’s nice. There is Excel training, online as well. You’ll learn a lot more than me and once you
learn all the tricks you can come back and show me a couple of them. But basically these are the costs and
how you calculate them using Excel. It saves a lot of calculator time. And then I’ll tell you what’s going to
happen next. This is the first homework of this type where you have to calculate costs. In the next homework, I will give you the costs and say okay, given these costs for a firm in pure
competition, and this is the marginal cost. And the rule
about what to do always has to do with the marginal cost versus the marginal
benefits. So let’s say that the marginal benefit
of producing and selling each additional unit is, we’ll say, forty seven dollars. If this is the marginal benefit, $47 for each unit that you produce and sell, how many should you produce and sell?
The answer is, you produce and sell as long as the marginal benefit of an action exceeds marginal cost. So $47 exceeds all
of these marginal costs. You are going to produce all these. Keep producing, keep producing, keep producing, keep producing; not that one. You’re done that quantity. Yes, that quantity. You will produce 20 units, you will not produce the 21st because the marginal cost of the twenty-first unit
is $57 you’re only getting $47 Dollars for it. So, (the rule is) if the marginal benefit exceeds the marginal cost, do it, and if the marginal cost, like the 21st unit here, exceeds the marginal benefit, don’t do it. If
you follow that rule you will maximize your profit or
minimize your losses. This is subject to one little wrinkle that get to in the next section. So that’s how you do it. That’s it. Bye.

10 thoughts on “Cost Calculations Using an Excel Spreadsheet.mp4

  1. i am working on a school project and i have to do a start up cost spread sheet for a bakery ive chosen and i have no idea how its done.This is what the directions say: part of your pitch will require that youve done the math on not only how to raise the capital you need, but how you budget your expenses. on the spread sheet you will show projected growth rate or  in other words what kind of retrun can the investor hope for. Whatever method that you choose to raise capital this needs to be shown on the spread sheet………. yeah no idea what to do PLEASE HELP its really big on my grade and i cant seen to understand even after all the videos i watch. THANKS looking forward to your reply.

  2. Why did you work backwards on estimating variable cost? You should have gone from fixed to variable to total then to the averages… Doesn't help how you estimated total without understanding a breakdown on variable…
    Bad job overall on your video

Leave a Reply

Your email address will not be published. Required fields are marked *