0:12

In the last segment you saw a table showing

Â the calculations for the fertilizer rate that would maximize profit.

Â This time I want to show you how to set up a spreadsheet to do those calculations.

Â I'll be using Excel 2010.

Â So if you're using a different version of Excel or

Â a different spreadsheet entirely then you'll need to make some adjustments.

Â I'll move through it reasonably steadily at a steady pace so I'll probably be

Â moving too fast for you to reproduce what I'm doing in real time.

Â So what I suggest you do is watch it through,

Â listen to it through once and then go back and watch it through again in small segments.

Â So watch a bit, pause it,

Â try and reproduce what I've just done and then continue on.

Â You may need to use a bit of trial and error.

Â And I also recommend that you make use of

Â the Excel Help system which is very good if you are

Â having problems working out how to do

Â a certain thing or rewatch how I've done it on the screen.

Â Okay, let's get started.

Â So there were five columns of information in that table.

Â I'm going to start by entering in the table headings.

Â First one was fertilizer rate.

Â And that's always a good idea to include the units of

Â measurement when you're entering information like this to avoid confusion later on.

Â I'm not going to worry about the fact that these overlap at the moment.

Â I'll fix that up in a minute.

Â Then wheat yield in tons per hectare.

Â Revenue in dollars per hectare.

Â Cost also in dollars per

Â hectare and profit in dollars per hectare.

Â Okay, I'm going to mark all five of those columns and make them a little bit wider,

Â just a bit more than that.

Â And I'm going to mark those five headings and change it so that the text wraps around.

Â So format cells, alignment,

Â wrap text, okay.

Â Good.

Â Now I'm going to enter in the levels of fertilizer rate.

Â Zero, starting with zero.

Â And then I'll go up in units of 10 so this will be a formula that looks at

Â the cell above and adds on 10 and then I'll copy that down.

Â So there, paste it in and I've got a full range of the relevant fertilizer rates.

Â So at least the range of rates that I'm interested in for this exercise.

Â Now I'm going over to the right here,

Â I'm going to enter a number of parameters,

Â so parameters are values or numbers that will

Â stay the same for all the different fertilizer rates.

Â So one of those is the wheat,

Â oops, that doesn't go there. Over here.

Â One of those is the wheat price in dollars per ton.

Â I've got the fertilizer price in dollars per kilogram.

Â I've got then three parameters that relate

Â to the relationship between fertilizer rate and weight yield.

Â So the first one is fairly intuitive and that's

Â just the maximum yield which is in tons per hectare.

Â That's the highest yield we'll be able to get at the best fertilizer rate,

Â at least best in terms of yield,

Â not necessarily in terms of profit.

Â And then two other parameters which determine the shape of the curve,

Â the shape of the relationship between fertilizer rate and wheat yield.

Â I'm just going to call those A and B.

Â Doesn't really matter what they're called.

Â I'm going to make that column wider so that it fits

Â those labels and then I'll enter in the values that will stay the same.

Â So I'm going to use $250 a ton for weight,

Â $1.90 for fertilizer price,

Â a maximum yield of three tons.

Â A will be minus .00, no,

Â sorry .02 and B will be minus .05, sorry, minus .5.

Â Right.

Â Now I can use that,

Â at least I'll be using these three the parameters of

Â the yield relationship to entering

Â the formula for wheat yield which I'll do right here and then copy it down.

Â So wheat yield equals,

Â first of all that equals the maximum yield,

Â so I'm going to enter in a formula now which uses a particular structure for

Â the equation but there is a variety of

Â different equational functional forms you could use.

Â You know, in a sense,

Â I prepared this earlier.

Â I've already worked out in advance using statistics,

Â or getting some information from somebody else's research,

Â what a functional form,

Â what mathematical function will be appropriate to represent this relationship.

Â So to start with I'm going to click over here on maximum yield, so H4.

Â I'm also going to press on F4 to make sure

Â that those dollar signs go in which means that I'll always look at that cell,

Â H4, even after I copy this formula down the column.

Â So it's H4 times and then open bracket

Â one minus then the exponential function E_X_P open bracket.

Â Then the next is the parameter A so I click on the cell there for parameter A and

Â press F4 times the fertilizer rate.

Â Let's go out right here and click on the fertilizer rate.

Â I don't need to press F4 this time because that will change when I copy

Â this formula down the column then minus,

Â sorry, plus the parameter B.

Â It's already got a minus sign there.

Â It's already a negative number.

Â Press F4 to put the dollar signs in.

Â Close the brackets for the exponential function.

Â Close the brackets for the larger bracket that gets multiplied by H4.

Â And that's it.

Â So there's my wheat yield if I don't apply any fertilizer.

Â Now I'm going to copy that and then paste it down

Â the column and you can see that

Â the yield increases the higher that the fertilizer rate goes.

Â But as we saw in the previous segment,

Â with the previous segments of this week,

Â the yield increases at a decreasing rate so it gets,

Â if we graphed this as a curve you'd see it getting flatter and flatter.

Â Okay, let's put in the revenue now,

Â a function for revenue.

Â So revenue is just the yield times the sale price.

Â So equals the yield,

Â no need to press F4 because it's going to change as I copy it down,

Â times the price which is either here at H2,

Â this time I do press H4, sorry,

Â I did press F4 because I want to,

Â I want that number to stay the same as I copy this formula down.

Â Press Enter. There's more revenue at that initial low yield.

Â I can just drag that down that's one way of copying it down.

Â And you can see that the revenue goes up as the yield goes up.

Â It's in proportion to the yield, of course.

Â Now let's put in the cost formula.

Â The cost is simply the fertilizer rate times the cost of fertilizer.

Â So equals the fertilizer rate,

Â no need to press F4,

Â times the fertilizer price but this time I do press F4,

Â press enter, copy it

Â down and then I'll calculate the profit which is the difference between revenue and cost.

Â So equals revenue minus cost,

Â press enter, copy that down. Okay.

Â And you can see that we've been able to identify the maximum profit.

Â There it is. $506 per hectare.

Â That's the highest profit level that's possible within, you know,

Â in this particular circumstance given

Â that particular relationship between fertilizer rate

Â and weight yield which would apply in

Â a particular region for particular soil type and so on.

Â The most that the farmer can make is $506 a hectare

Â if the fertilizer rate used is 80 kilograms per hectare.

Â If the rate was a hundred the profit would be

Â $498 per hectare so the yield's a little bit higher.

Â It's 2.75 compared to 2.63 but the profit is a little bit lower.

Â It's 4.98.

Â So $498 per hectare instead of 506 and that's because the cost has gone up by more,

Â gone up by what's that, $38.

Â But the revenue has gone up by only about $30 so the profit went down by about $8.

Â So there you go.

Â So you could use this type of approach to calculate

Â the optimal rate of any input provided you know the price of the output,

Â the cost of the input,

Â and the mathematical formula for the relationship between the input and the output.

Â So good luck with that.

Â