These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, April 14, 2011

Winder Farms Daily Dairy Report Macro

Executive Summary

Winder Farms, which began as a home delivery milk service 125 years ago, is no longer just a milkman. They now consider themselves to be a farmer's market on wheels. They deliver over 150 farm fresh products to people's doorsteps all over Utah and in Las Vegas. They are a family business, with the fifth and sixth generation of Winders till involved in the ownership and operation of the business. Winder Farms used to be known as Winder Dairy, but a few years ago they changed their name and sold the dairy. They were losing money on the dairy and decided to purchase their milk from a local co-op of farmers instead.

They purchase milk using futures contracts and continually look at updated prices in order to budget out their future expenses for milk. CME Group publishes a Daily Dairy Report that lists the futures and options prices of class III milk, dry whey, nonfat dry milk, and butter for the next 12 months. Each month the controller would manually create a spreadsheet with a table listing the prices for each future and option by day. This was tedious and took a lot of time, but the Daily Dairy Report is published in PDF format and it was easier to manually enter the data in than to try and get the data to copy and paste into excel nicely. The goal of my project was to use vba to automate the process. Excel will take the data from each day and make a spreadsheet for each type of future and then cycles through the month's Daily Dairy Reports and enter the prices from each day into the sheets. Then from each table of data it computes a daily average of the next twelve months and makes a chart showing a graph of the price movement.

The PDF write up of the project can be found here:
http://files.gove.net/shares/files/11w/amac23/Executive_Summary.pdf

The Excel file can be found here:
http://files.gove.net/shares/files/11w/amac23/Project.xlsm

No comments:

Post a Comment

Blog Archive