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.