Transcript Bernhardt_A - Ag Risk & Farm Management Library

Milk Marketing
Manager
A Spreadsheet for the ExPost Evaluation of Milk
Marketing Strategies
Kevin Bernhardt, UW-Extension and Margo Rudstrom, West
Central Research and Outreach Center, Univ. of MN
Farm & Risk Management Team
Today’s Presentation
• Ex Post not Ex Ante
• Milk Marketing Manager Spreadsheet –
What is it, What does it do
• Demonstration
• Some Results
• Teachables
• Cautions
• Q&A
Farm & Risk Management Team
Today’s Presentation
• Ex Post not Ex Ante
• Milk Marketing Manager Spreadsheet –
What is it, What does it do
• Demonstration
• Some Results
• Teachables
• Cautions
• Q&A
Farm & Risk Management Team
Milk Marketing Manager –
What is it, What does it do
• Calculates what the results would have been if
passive Class III marketing strategies had been
implemented from 2000-2006
– Total revenue
– Price
– Variability
• Allows the testing of variable strategy options
including PUTS, Forward Contracts and
combinations
• Allows the testing of market timing
Farm & Risk Management Team
Strategy Options
• PUTS
– variable strike prices and premiums by month
– variable marketing window
• Forward contracts
–
–
–
–
three different trigger levels per month
variable levels of contracting at each trigger
different forward contract specifications
marketing window
• Other
– desired benchmark price
– how often marketing takes place
Farm & Risk Management Team
Today’s Presentation
• Ex Post not Ex Ante
• Milk Marketing Manager Spreadsheet –
What is it, What does it do
• Demonstration
• Some Results
• Teachables
• Cautions
• Q&A
Farm & Risk Management Team
Today’s Presentation
• Ex Post not Ex Ante
• Milk Marketing Manager Spreadsheet –
What is it, What does it do
• Demonstration
• Some Results
• Teachables
• Cautions
• Q&A
Farm & Risk Management Team
22
20
Average
70th %tile
80th %tile
90th %tile
Maximum
18
16
14
12
10
1
2
3
4
5
6
7
8
9
10
11
12
$25.00
$20.00
$15.00
$10.00
$5.00
$0.00
J-00
J-01
J-02
J-03
J-04
Monthly Announced Price
J-05
J-06
Overall Average
FC 100% at $10.00 Trigger
20,000
$80,000
$70,000
10,000
$60,000
J-06
J-05
J-04
J-03
J-02
J-00
$40,000
J-01
0
$50,000
(10,000)
$30,000
(20,000)
$20,000
$10,000
(30,000)
$0
J-00
J-01
J-02
J-03
J-04
J-05
J-06
(40,000)
M onths
M onths
Cash
Marketing Gain/Loss
With Mrkt
TR Low to High Range pe r M onth With and
W/O M ark e ting
$150,000
$100,000
$75,000
$50,000
$65,000
J-06
J-05
J-04
J-03
J-02
J-01
J-00
$0
$55,000
($50,000)
$45,000
($100,000)
$35,000
($150,000)
$25,000
($200,000)
M onths
ov
N
S
ep
Ju
l
M
ay
M
ar
Ja
n
$15,000
Farm & Risk Management Team
Cumulative Marketing Gain/Loss
$11.00 PUT for $.15
10,000
$80,000
$70,000
8,000
$60,000
6,000
$50,000
$40,000
4,000
$30,000
2,000
$20,000
$10,000
$0
J-00
0
J-01
J-02
J-03
J-04
J-05
J-06
J-00
(2,000)
J-01
J-02
With Mrkt
J-04
J-05
J-06
J-05
J-06
Months
Months
Cash
J-03
Marketing Gain/Loss
Desired Revenue
TR Low to High Range per Month With and W/O Marketing
$75,000
$65,000
$55,000
$45,000
$35,000
$45,000
$40,000
$35,000
$30,000
$25,000
$20,000
$15,000
$10,000
$5,000
$0
J-00
$25,000
J-01
J-02
J-03
J-04
Months
$15,000
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
Farm
& Risk
Management Team
Cumulative
Marketing
Gain/Loss
$12.00 PUT for $.25
10,000
$80,000
$70,000
8,000
$60,000
6,000
$50,000
$40,000
4,000
$30,000
2,000
$20,000
$10,000
$0
J-00
0
J-01
J-02
J-03
J-04
J-05
J-06
J-00
(2,000)
J-01
J-02
With Mrkt
J-04
J-05
J-06
J-05
J-06
Months
Months
Cash
J-03
Marketing Gain/Loss
Desired Revenue
TR Low to High Range per Month With and W/O Marketing
$40,000
$35,000
$75,000
$30,000
$25,000
$65,000
$20,000
$55,000
$15,000
$10,000
$45,000
$5,000
$35,000
$0
($5,000)J-00
$25,000
J-01
J-02
J-03
J-04
Months
$15,000
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
& Risk
Management Team
CumulativeFarm
Marketing
Gain/Loss
$13.00 PUT for $1.75
15,000
$80,000
$70,000
10,000
$60,000
$50,000
5,000
$40,000
$30,000
0
J-00
$20,000
J-02
J-03
J-04
J-05
J-06
J-05
J-06
(5,000)
$10,000
$0
J-00
J-01
J-01
J-02
J-03
J-04
J-05
J-06
(10,000)
Months
Months
Cash
With Mrkt
Marketing Gain/Loss
Desired Revenue
TR Low to High Range per Month With and W/O Marketing
$75,000
$65,000
$55,000
$45,000
$35,000
$180,000
$160,000
$140,000
$120,000
$100,000
$80,000
$60,000
$40,000
$20,000
$0
J-00
$25,000
J-01
J-02
J-03
J-04
Months
$15,000
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
Farm
& Risk
Management Team
Cumulative
Marketing
Gain/Loss
FC 10%, 15% and 25% at 50th, 70th and 90th %-tile
$80,000
6,000
$70,000
4,000
$60,000
2,000
$50,000
0
$40,000
(2,000)J-00
$30,000
(4,000)
J-02
J-03
J-04
J-05
J-06
J-05
J-06
(6,000)
$20,000
(8,000)
$10,000
$0
J-00
J-01
(10,000)
J-01
J-02
J-03
J-04
J-05
J-06
(12,000)
Months
Months
Cash
With Mrkt
Marketing Gain/Loss
Desired Revenue
TR Low to High Range per Month With and W/O Marketing
$30,000
$75,000
$20,000
$65,000
$10,000
$55,000
$0
$45,000
J-00
($10,000)
$35,000
($20,000)
$25,000
($30,000)
J-01
J-02
J-03
J-04
Months
$15,000
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
CumulativeFarm
Marketing
Gain/Loss
& Risk
Management Team
FC 10%, 15% and 25% at 50th, 70th and 90th %-tile
Plus an $11.00 PUT for $.15
15,000
$80,000
$70,000
10,000
$60,000
5,000
$50,000
$40,000
0
$30,000
J-00
(5,000)
$20,000
$10,000
$0
J-00
J-01
J-02
J-03
J-04
J-05
J-06
J-05
J-06
(10,000)
J-01
J-02
J-03
J-04
J-05
J-06
(15,000)
Months
Months
Cash
With Mrkt
Marketing Gain/Loss
Desired Revenue
TR Low to High Range per Month With and W/O Marketing
$70,000
$60,000
$75,000
$50,000
$65,000
$40,000
$30,000
$55,000
$20,000
$45,000
$10,000
$35,000
$0
J-00
$25,000
J-01
J-02
J-03
J-04
Months
$15,000
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
Farm & Risk
Management Team
Cumulative Marketing
Gain/Loss
FC 10%, 15% and 25% at 50th, 70th and 90th %-tile
Plus an $12.00 PUT for $.25
15,000
$80,000
$70,000
10,000
$60,000
5,000
$50,000
$40,000
0
$30,000
J-00
(5,000)
$20,000
$10,000
$0
J-00
J-01
J-02
J-03
J-04
J-05
J-06
J-05
J-06
(10,000)
J-01
J-02
J-03
J-04
J-05
J-06
(15,000)
Months
Months
Cash
With Mrkt
Marketing Gain/Loss
Desired Revenue
TR Low to High Range per Month With and W/O Marketing
$60,000
$50,000
$75,000
$40,000
$65,000
$30,000
$55,000
$20,000
$45,000
$10,000
$35,000
$0
J-00
($10,000)
$25,000
J-01
J-02
J-03
J-04
Months
$15,000
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
& Risk
Management Team
Cumulative Farm
Marketing
Gain/Loss
Some Results
Strategy
TR Gain
Price
St Dev, TR SD, Price
11.00 PUT for $.15
24,737 12.37
7,974
2.28
12.00 PUT for $.25
34,422 12.40
8,085
2.31
13.00 PUT for $1.75
46,205 12.44
4,666
1.33
FC 10, 15 & 25% at 50th,
70th, & 90th %-tile
23,521 12.21
6,991
2.00
1,217 12.29
6,426
1.84
10,902 12.32
6,530
1.87
8,531
2.44
FC and 11.00 PUT
FC and 12 PUT
No Marketing, Strictly
Cash Result
NA
12.29
Farm & Risk Management Team
FC 10, 15 & 25% at 50th, 70th & 90th %-tile
4,000
2,000
Contract purchases
stopped 15 days prior
to market month and
triggers checked daily.
[-$23,521 mrkt gain]
0
J-00
(2,000)
J-01
J-02
J-03
J-04
J-05
J-06
J-05
J-06
(4,000)
(6,000)
(8,000)
(10,000)
Months
Marketing Gain/Loss
Contract purchases
stopped 90 days prior
to market month and
triggers checked every
10 days
[-$6,890 mrkt gain]
4,000
2,000
0
J-00
(2,000)
J-01
J-02
J-03
J-04
(4,000)
(6,000)
(8,000)
(10,000)
Months
Marketing Gain/Loss
Farm & Risk Management Team
Marketing Gain
FC 10, 15 & 25% at 50th, 70th, & 90th %-tile
Daily Check of
Markets
Weekly Check
of Markets
($23,521)
($16,015)
Every Two
Week Check of
Markets
($13,695)
FC cut off 15
days prior
($23,521)
FC cut off 60
days prior
($16,907)
FC cut off 90
days prior
($9,989)
Farm & Risk Management Team
Today’s Presentation
• Ex Post not Ex Ante
• Milk Marketing Manager Spreadsheet –
What is it, What does it do
• Demonstration
• Some Results
• Teachables
• Cautions
• Q&A
Farm & Risk Management Team
Teachables
• How PUTS, Forward Contracts or a
combination of both work
– Visual of results
– Specifications
– Historical premiums
• Historical prices
• Historical price variability
• Historical price benchmarks and
seasonality
Farm & Risk Management Team
Teachables
• Impact of employing simple passive
strategies:
– Easy to implement
• Other than up front time, virtually no management
time is required
– Accomplishes the goal of reducing price
risk/variability
• The more aggressive your marketing, the more
price variability is reduced
– Not a way to achieve a higher price
– Result in periods of gain and periods of losses
• And yes, they are losses!!
Farm & Risk Management Team
Teachables
• Impact of how often you check markets
• Impact of how close to contract month you
take marketing actions
• Impact of changing your PUT marketing
window
• Marketing for a higher price is a
wholly different exercise than
marketing to reduce price risk!!
Farm & Risk Management Team
Think of Marketing in Two Levels
Reduce Price Risk
- Easy to do (passive)
- Effective
- Have to maintain long-run
focus
Increase Price
- Difficult (active)
- Time consuming
- New set of skills required
- Have to maintain long-run
focus
Cautions (Even Danger!)
• This is a Teaching Program not a
management decision-making program
– Research indicates that trading systems
based on historical data are no guarantee of
future performance.
• Park and Irwin. The Profitability of Technical Trading Rules in US
Futures Markets: A Data Snooping Free Test (May 2005).
Farm & Risk Management Team
Park and Irwin, 2005
• “Nonetheless, the evidence provided by
this study suggests a great deal of caution
should be used in presenting to farmers
any form of technical analysis as an
effective method of predicting price
movements”
Farm & Risk Management Team
Program Status
• Program is large
– Three linked spreadsheets, 24.4 megabytes
• Too much to email
• Works well on CD
• Need to make it user friendly
• (this summer’s project)
• Stand-alone software perhaps in the future
• If you want it, let me know!
Farm & Risk Management Team
Program Status
• If you want it, let me know!
Kevin Bernhardt
608 342-1365
[email protected]
Farm & Risk Management Team
Farm & Risk Management Team