Problem 2 - For this next problem, we will employ cost volume profit analysis. Farmer Bob has just harvested his crops and is contemplating retirement. A sudden shift in grain prices has also given him doubts about the viability of his enterprise. In order to stay in farming, he reckons that he needs to make $150,000 after taxes to make it worth his efforts and to earn an appropriate return on capital. Farmer Bob currently farms 2,000 acres of land and only plants corn.

Farmer Bob gathers some figures to help him perform a profitability analysis. Currently, the price of corn on the open market is $3.05 per bushel. In recent years, Farmer Bob has averaged about 167 bushels of production per acre when harvesting the corn. Most of the costs of the operation are fixed, even the planting costs (e.g. seed, fertilizer), because Farmer Bob has a fixed amount of land to employ and always plants all of the acreage. However, the weather and other factors typically influence how much grain he harvests and many of the harvest costs (e.g. transportation, hired hands, increased maintenance and fuel, utilities related to drying the grain, etc.) are variable based upon the number of bushels produced. These have historically averaged $0.32 per bushel. Overall, the fixed costs of the farming operation (e.g. land rent, equipment leases, planting costs) have averaged $675,000 per planting and harvest season. Farmer Bobs tax rate is 30%.

A. Perform a cost volume profit analysis for Farmer Bobs farm. What is his expected level of after-tax profitability if he achieves 167 bushels per acre in the harvest? Is this high enough for Farmer Bob to want to keep farming?

B. How many bushels per acre must Farmer Bob harvest in order to break even and earn zero profit?

After performing cost volume profit analysis, Farmer Bob also wants to explore the notion of risk, as farming is a high risk business. In order to do so, he wants to perform a Monte Carlo Simulation to see the range of potential outcomes for his farm. In order to do so, he must first identify which variables are random (i.e. that are not set in stone and follow some form distributional pattern around an expected value). The first random variable is the selling price of corn. Looking over historical price data, Farmer Bob calculates the standard deviation of the price of corn to be $0.65. The second identified random variable is the bushels per acre that are harvested. In looking through his historical harvest records, Farmer Bob calculates the standard deviation of bushels harvest per acre to be 11. The third identified random variable is the variable costs to harvest, per bushel. In looking back through his financial statements, the standard deviation of this has been $0.04 per bushel. The fourth identified random variable are the fixed costs of the farming operation. Their historical standard deviation for the fixed costs has been $30,000.

C. Farmer Bob has asked you perform a Monte Carlo Simulation using the above information. Perform a simulation using excel that does 10,000 random draws for each random variable (it is best to organize these variables side by side into 10,000 rows) using both the expected value and the standard deviation. (assuming a normal distribution) The following excel function will be helpful in doing this.

= (NORMINV(RAND(),expected or average value of random variable, standard deviation of random variable)

Once a random draw is established for each random variable in each row, then compute the overall after-tax CVP level of after-tax profits for each row. Once you have the spreadsheet completely set-up, you may need to copy and paste the values into a new spreadsheet to do your final answers as the spreadsheet will re-seed itself with every change that is made.

D. What is your overall average level of profitability? Is it close to your answer in part A? If so, why do you think that is?

E. What is the standard deviation and the range of profitability outcomes (hint: you can use excel functions to compute the standard deviation, minimum, and maximum values). What might these values be useful for?

F. What is the likelihood that profit could fall below $0? $150,000? (hint: to measure this, write two if then functions in each row that assign a value of one if the profitability outcome falls below these thresholds (zero otherwise). For each, then sum up all of the ones and divide by 10,000 to assess the likelihood or probability of not meeting the threshold)

G. What will happen to the likelihood of failure (e.g. not making $150,000) if the standard deviation of the price of grain or any of the other random variables, for that matter, were to increase? If your intuition does not give you an answer, play around with your spreadsheet that is completely formula driven to see what happens when the standard deviation (i.e. risk) of random variables increases.

H. If you were Farmer Bob, would you worry about risk? What are some things Farmer Bob could do, from a management standpoint, to lower his exposure to these random variables or sources of risk?