Question

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 isn’t 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, mpg’s, and storage time.

Formula for break-even price = Total cost/Yield

Answer 1

To find the break-even price for Mr. Farmer's wheat, an Excel spreadsheet is created that updates automatically based on storage costs, transportation options, fuel prices, and other variables affecting profitability.

Step-by-step explanation:

To assist Mr. Farmer in understanding when to sell his wheat to make a profit, we need to create an Excel spreadsheet that calculates the break-even price of the stored commodity considering varying storage costs and transportation to different terminals. The formula for the break-even price is Total cost divided by Yield.

We'll need to factor in the storage costs (.00167 per bushel per day), yield (52 bushels per acre), total cost ($189 per acre), transportation options, truck capacity (800 bushels), fuel efficiency (6 miles per gallon), and diesel price ($3.65 per gallon).

As Mr. Farmer inputs different variables such as destination, diesel prices, and storage time, the spreadsheet will automatically update the break-even point. This tool will aid in making informed decisions about the most profitable time to sell the wheat.