course on decision making with the spreadsheet, today is the second lecture. In this lecture I am going to explain the relationship among cost, revenue, profit and break-even analysis and some guidelines for making good spreadsheet models. The agenda for this lecture is. Relationship among cost, revenue and profit and break-even analysis using excel and guidelines for making good spreadsheet model.
You may ask what is the importance of studying or knowing about cost, revenue and profit. Most of the linear programming model either it can be maximizing the profit or it can It can be minimizing the cost. So it is better to understand that the relationship between profit and cost that is what we are going to see in this lecture. Most basic quantitative models arising in business and economic applications are those who are involving the relationship between volume variables.
Here the meaning of volume is number of quantities. such as production volume or sales volume and cost, revenue and profit. When you look at in the right hand side, this was the previous example which I have explained my first lecture that you see maximizing the profit okay equal to 10x.
The objective function has that need objective function need to be maximized. that has to be minimized. So in this lecture I am going to explain the relationship between your cost and profit.
If you know the relationship between profit, volume and cost, a manager can determine the projected cost, revenue and or profit associated with established production quantity or a forecasted sales volume. This picture says you see when you say profit is equal to revenue minus cost. When you see the cost, the cost can be classified into two category. One is fixed cost and variable cost. What is the fixed cost?
In x-axis, unit is there. This is called volume. In y-axis, quantity. In x-axis, volume is there.
You see even though the volume is increases the cost is fixed for example $10,000. So this is an example of fixed cost. What is the variable cost? The cost varies with number of quantities produced. When the number of quantities increasing your cost also increasing.
So this is an example of variable cost. What is the fixed cost? A portion of the total cost that does not depend on the production volume.
This cost remains the same no matter how much is produced. So the cost is independent of number of quantities produced that is called fixed cost. But when you come back when you go back to the variable cost the portion of the total cost that is dependent on And varies with the production volume.
The cost if it is directly proportional to the number of unit produced that cost is called variable cost. Some of the examples between fixed and variable cost. See fixed cost the rent for office space or storefront that has nothing to do with number of unit produced.
That is an example of your fixed cost. Weekly payroll. that is independent of quantity produced that is your fixed cost. Equipment depreciation that is independent of whether you use the machine or not, there should be some cost for depreciation that is called fixed cost. On the right hand side, I have given some example for variable cost, delivery and shipping charges.
When the quantity increases, the delivery charges will be more, shipping charges will be more and sales communications, sorry mistake, sales commissions, when number of product sold is more, there will be more commission. the amount to spend on advertising and publicity. These are all example of variable cost.
Suppose that in setup cost for a production is say 3000 dollar. Setup cost is those cost incurred to configure a machine for a production run. This setup cost is a fixed cost that is incurred regardless of the number of units eventually produced.
Variable labor and material cost are 2 dollar for each unit produced. So the cost volume model for producing x units of the product can be written as C cost of x equal to C of x is equal to 3000 plus 2x. Here 3000 is your fixed cost the 2x is called your variable cost.
The x is the production volume in units, the c of x is total cost for producing x units. When you look at this equation, when x increases the total cost of production will increase. But this 3000 dollar which is independent of number of unit produced. Once your production volume is established, the model In equation C of x equal to 3000 plus 2x can be used to compute the total production cost.
For example, the decision to produce x equal to 1200 units would result in a total cost of C of 1200 equal to 3000 plus 2 into 1200 it is 5400 dollar. Then another concept called marginal cost. Marginal cost is defined as the rate of change of total cost with respect to production volume that is it is the cost increase associated with a 1 unit increase in the production volume.
In the cost model we see that the total cost C of x will increase by 2 dollar for each unit increase in the production volume. So, here the marginal cost is 2 dollar that is how much cost is increased by each unit of production. So, that cost is called your marginal cost. Now we will go to the second topic that is called revenue, how the revenue and volumes are connected. Investment want information on the projected revenue associated with selling a specified number of units.
Suppose I am selling X number of units, I want to know what is the revenue associated with the units. Thus, a model of the relationship between revenue and volume is needed. Suppose that each product sells for $5. So this $5 is called a marginal revenue.
So, this can be written as r of x equal to 5x where x is sales volume in units where r of x is total revenue associated with the selling x units. So marginal revenue is defined as the rate of change of total revenue with respect to sales volume that is it is the increase in total revenue resulting from 1 unit increase in sales volume. So, in our problem the marginal revenue is 5 dollar, what is the meaning of this 5 dollar?
If one unit is sold the revenue is 5 dollar. In this case the marginal revenue is constant and does not vary with the sales volume. We have seen already what is the profit, profit equal to revenue minus cost.
Now you have seen what is the revenue function, R of x equal to 5x. Then C of x equal to 3000 plus 2x. So, when you subtract this revenue minus cost that is a 5x minus. So, 5x minus 3000 minus 2x, so minus 3000 plus 3x. So, this is your profit function minus 3000 plus 3x.
So, what we have seen? We have studied separately what is the cost function, we have studied what is the revenue function. When you subtract revenue minus cost, you are getting the expression for your We can now determine the total profit associated with any production volume x because we got now profit function. For example, suppose that a demand forecast indicates that 500 unit of the product can be sold. So the decision to produce and sell 500 units result in projected profit of minus 1500 dollar.
In other words a loss of 1500 dollar is predicted. How we got this? We got the profit function in that you substitute x equal to 500 units.
So P of 500 equal to minus 3000 plus 3 into 500. So we are getting minus 1500 dollar is the negative. So it is loss. What will happen to our profit function?
So instead of x you have to substitute 1800 in the profit function. When you substitute 1800 so you are getting minus 3000 plus 3 into 1800 you are getting 2400 dollar. You see previously we got a negative profit now we are getting a positive profit right when x equal to 500 your profit was negative. When x equal to 1800 your profit is 2400. As a manager I wanted to know suppose if I want to know 0 profit how much unit should I produce. So answering this question is nothing but your break-even point analysis.
So what is the break-even analysis? How many units I need to produce? So that situation or that point is called your breakeven point. So what we are going to do in this function minus 3000 plus 3x we are going to profit equal to 0 then we are going to find the value of x.
So if the breakeven point is known a manager can quickly infer that the volume above the breakeven point will result in your profit whereas. A volume below the breakeven point will result in a loss. So the breakeven point for a product provides a valuable information for a manager who must make a yes or no decision concerning production of the product. So the breakeven point will tell you whether that quantity will yield you profit or not.
where there is no profit situation. So when the profit function is 0, the corresponding quantity is called your breakeven quantity. Now I will explain how to find out the breakeven quantity. So the breakeven point can be found by setting the total profit expression equal to 0 and solving the production volume. Using the equation, we have profit equal to minus 0. 3000 plus 3x using the equation means our profit function.
So when you simplify this so 3x will be 3000 so x equal to 5000 units. So if you produce this many number of quantities you will get a 0 profit. So after 1000 unit you will be starting you will be start starting to earn the profit with this information.
We know that the production and sales of your product must be greater than 1000 units before a profit can be expected. When you look at this profit not profit when you look at this picture in x axis the quantity that is a production volume in y axis revenue and cost in terms of dollar. So, here this line shows your revenue function. This line shows your cost function okay. What is the fixed cost?
Fixed cost will be like this. So but this line is your total cost. This is your total revenue.
Total revenue minus total cost will get a profit function. If you equate that profit function equal to 0, you will be getting this is your 1000 units. This point is called your breakeven point.
So what we are understanding beyond this 1000 units, we will be start making profit. Below this 1000 units, you will be incurring loss. This breakeven analysis can be done with the help of excel. There is a function called goal seek.
So where will you get this goal seek function you have to go to data there there will be a what if analysis. When you click in this what if analysis you see the second option is called goal seek. In the excel model in the left hand side I have given the inputs what is the input fixed cost I have entered variable cost is entered selling price per unit is given.
Then, the production volume, this is the place where we need to get the answer. So, what is the total cost? Total cost is fixed cost plus variable cost. What is the fixed cost?
B1 plus variable cost 2, the value which we are going to get in B8, that is your total cost. What is the total revenue? That is a 5, that is a B5.
the x quantity that is where you are going to be 8. So what is the total profit or loss? So total cost that is b total revenue b12-b10. So when you click data you will get a goal seek function. Here the set cell is we have to make this this cell b14 the value which you have to set it to 0. The changing cell is where we need to get the answer. So, when you press ok, you will getting the answer.
So, the 1000 unit is your breakeven unit. So, that is a point where you will get the zero profit. Dear students, now I will explain how to use Excel for doing breakeven analysis. I have entered the data, fixed cost, variable cost per unit and selling price per unit and production volume. This is the cell B 17 where I need to get the answer.
So, then I have explained total cost function. Total cost function is 3000 plus 2 x and and the total revenue, total revenue is 5 multiplied by x. So, what is the total profit function here I have written total revenue minus total cost that is B 21 minus B 19. Now, you have to go to data cell, then there is a option called what-if analysis, go for goal seek, set cell, the set cell is you have to select your profit cell and the value should be 0. The changing cell is where we need to get the answer that is B 17, when you press ok, you are getting 1000. So, this 1000 is your breakeven quantity.
At this point the profit is 0. Beyond 1000 units you will make making money, below 1000 units you will incur loss. Now I will explain the process for modeling with spreadsheet. There are 4 stages, one is a plan, build, test. In the planning stage what you have to do you have to visualize where you want to finish that is the first step. Then you have to do some calculation by hand so that you will get a confident whether the logic is correct or not.
Then you have to sketch out the spreadsheet where should be the input, where should be the output and so on. Then the second stage is Build the model, start with a small scale model. Then, the third stage is the testing stage, try different trial solutions to check with the logic that is a testing stage.
Then you should go for analyze, evaluate the proposed solution or the optimized value with the solver. If the solution shows inadequacy, again you should go for planning stage. In the testing stage or in the evaluation stage, testing stage or in the analyzing stage, if there any if there is any problem again you the testing stage and the analyzing stage, if it is getting the right answer then you can go for expanding the model to a full scale.
These are the process of modeling with the spreadsheet. Now, I will explain some of the guidelines for building good spreadsheet model. What is the first one?
Enter the data first. All the data should be laid out on the spreadsheet before beginning to formulate the rest of the spreadsheet model. There should not be a confusion where is the data, where is the model. The second one is the following.
and clearly identify the data. Here what is the meaning of this one is you have to provide the labels in the spreadsheet that clearly identify all the data. Next one, enter each piece of data into one cell only. Every formula using the same piece of data should refer to the same single data.
Then separate data from formulas, formula should refer to data cells for any needed numbers every time the formula should refer suppose data you have entered formula you have entered every time this formula should refer to the data cell. Avoid using numbers directly in formulas okay suppose if you are writing if you are writing some formulas. You do not write the exact value on the formulas always refer the data cell because in further stage if you want to modify the value of the data so the formula get updated automatically. Then keep it simple make the spreadsheet as easy to interpret as possible. Use range names.
Same range formula. much easier to interpret. When you are making range when you are giving a name for the range space are not allowed in range names.
When range names has more than one word you can use capital letters to distinguish that start of each new word in the in a range name. Use underscore character. Dear students now. I will explain how to give the name for the range of cell. I have entered the say for example marks.
So I want to give a name for this range of cells. So go to define name. I am writing mark 1 right. See when you select this you see that this will be look at this on the left hand side.
it is under name box it is coming marks 1. Suppose here I have given weight age for example 0.25, 0.3, 0.2, for example 0.5. For this cell I am going to give some other name select define name weight. Suppose if I want to use an excel function equal to some product I am going to find the weighted sum, some product instead of selecting cell I can simply type mark 1 comma weight 1. Now, you can see the advantage of this naming the range of cells instead of selecting the cell you can give the name. marks 1 comma weight 1 and you enter you will get the 85.55 this is the sum product. Look at inside the bracket it is a marks 1 comma weights 1. So, when you give the range of names a particular cell it is easy to call that cells easily if you are using in the excel formula.
I have explained when you go to formula cells there is option called define name here you can give the names ok. There is a name manager if you want to modify it when you click this you can change the or edit the name of the range. Use relative and absolute references to simplify copying the formulas.
For this if you want to make an absolute references in your excel if you press F4 there will be a dollar symbol will appear. Then, that will become an absolute references. In the Excel's fill command, provide a quick and reliable way to replicate a formula into a multiple cells. After selecting that bottom right corner, if you double click, all the formulas will be filled. This is the formula for filling.
Suppose you select that particular cell, drag it, then press control D, it is a down fill. shortcut formula for down fill, you select a cell then you select some cell on the right select a number then select that cell on the right hand side then you press control R the right side cells will be filled. We look at there is a fill option here, there is we can go for right fill, up fill, left fill. So this will simplify your task. Then use borders, shading and colors.
to distinguish between cell types. For example, which is the objective function cell, which is the changing cell, you can give a different color so that it is easy to recognize. Make it easy to spot all the cells of the same type.
Dear students, in this lecture, we have seen the relationship among cost, revenue and profit. Why this is important? Every linear programming problem Always we have to maximize the profit or we have to minimize the cost.
It is important to know how the cost element and the profit element are calculated. Then I have explained what is the breakeven analysis, how to arrive that breakeven point using excel. After that I have explained some of the guidelines for making good spreadsheet model. The next class.
We will talk about very important portion of this course that is formulating an LP problem.