Prices for commodities obviously change on a daily basis. Sometimes we have expenses (storage) that change on a daily basis as well. Your objective is to create a working Excel document that calculates a break-even price on a stored commodity as variables change. Mr. Farmer is having trouble understanding when he should sell his wheat to make a profit. His marketing plan has always been to sell when he needs money to pay bills which isnt working for him. He has hired you to help him with this process. Here is his known information;

He finished harvesting his wheat on July 16, 20XX and has had it on farm storage since that day.

Wheat is stored on farm but he calculates that it still costs him .00167 per bushel per day for things like electricity, drying, repairs, etc.

His yield for wheat was 52 bushels per acre on his last crop.

His total cost (variable and fixed) was $189 per acre on his last crop.

He has the option to take his wheat to any of the following terminals; Salina, Ogallah, Goodland, Kansas City or Dodge City. His wheat is currently stored in Hays, KS

His semi-truck can haul 800 bushels of wheat.

His semi-truck averages 6 miles per gallon.

Diesel price is currently $3.65 per gallon.

You need to organize and develop an efficient working spreadsheet that will address a break-even point for his crops. This spreadsheet should take into consideration storage and transportation. These two things will change the break-even point on a daily basis and a destination basis. The break-even point on this spreadsheet should change as he puts in different destinations, prices for diesel, truck sizes, mpgs, and storage time.

Formula for break-even price = Total cost/Yield