Price Analysis Training2.1. SG - Intro to Graph Analysis.doc

2.1. Introduction to Graphical Analysis

Session-at-a-glance

Content / Approximate Time / Instructional Activity
Exercise 2.1.a. Charts and Graphs – Strengths & Limitations / 60 minutes / Small Group Work and Plenary Feedback
Introduction to Graphical Analysis / 60 minutes / Plenary presentation & Q&A
Exercise 2.1.b. The Marketastan File: Excel Charts for Senior Managers / 60 minutes / Small Group Work and Plenary Feedback
Exercise 2.1.b. Debriefing / 45 minutes / Plenary discussion
Total Time / 225minutes

Session Objectives

By the end of this session, participants should be able to:

  • Describe the advantages of different types of graphs and charts and which is best used with particular types of data.
  • Scale graphs in accordance with units used, and explain the meaning and intent of each of the axes on a graph.
  • Identify the trends (dispersion, volatility, increasing/ decreasing) depicted by a graph.
  • Identify the breaks in a data series and explain whether they are real or indicate problems with the data.
  • Explain how to deal with missing data.

Session Supplies

  • Power-point: 2.1. Intro to Graph Analysis.ppt
  • Workbook Exercise2.1.a. Charts and Graphs – Strengths & Limitations (annexed to this Session Guide as Annex 1)
  • Excel File 2.1. Dealing with Missing Data.xls (on PAT CD in Exercise Data Folder)
  • Workbook Exercise 2.1.b. The Marketastan File: Excel Charts for Senior Managers (annexed to this Session Guide as Annex 2)
  • Excel File 2.1.b. Charts for WFP-Marketastan Senior Managers – Excel File.xls (on PAT CD in Exercise Data Folder)

Key Messages

  • Visual images can provide for clearer communication of complex phenomena; developers must, nonetheless, bear in mind for whom the messages are intended. Excel is helpful in visualizing the effects of changes in quantities of particular variables (if/then scenarios).
  • Graphical analysis can show the relationship between prices, but it does not quantify the degree of this relationship; nor does it give a clear understanding of the direction of relationship (i.e. the direction of price transmission).
  • An apparent relationship between prices on a graph (such as convergence or divergence) does not necessarily indicate a meaningful relationship between those prices.
  • The co-movement of price series in different locations at the same time could be due to common factors affecting those prices – e.g., seasonality, inflation, drought, war, prohibition, trade barriers – rather than to any meaningful causal relationship in trade between the different locations.
  • The interpretation of a graph may need additional information: the relationship between variables (prices) could be lagged, instantaneous, linear, non-linear, symmetric, or asymmetric.

Guidance Materials

--

Facilitator Guidance and session preparation

The facilitator of this session must be skilled in the use of the various charting features of Excel – or have a resource person someone who has such skills.

Be sure to review the two exercises and proposed solutions to those exercises well in advance of delivering the session. Familiarize yourself with the various images contained in Workbook Exercise 2.1.a. You may want to add some of your own graphical images to the exercise if you find participants need more introduction to the various types of charts and graphs that Excel can generate.

Room Setup

Arrange for small group work and for plenary presentation in the plenary conference room.

Session Activities

Exercise 2.1.a. Charts and Graphs – Strengths & Limitations

60 minutes

Use PPTs 1-3 to introduce the session, its objectives and the reasons we work with graphs, but move quickly to Exercise 2.1.a.(as indicated on PPT 4).

Divide the participants into four groups (five if the workshop is 25 or more). Have them open the exercise in the workbook and ask each group to consider its two assigned graphs. Give them about 30 minutes to complete the exercise (assuming you are starting on schedule, the exercise will be conducted in the 30 minutes before lunch and the debriefing after lunch).

After the participants come back from lunch, show PPT 5 and call for their reports. Typical responses include the following:

Figure / Strengths / Limitations/Weaknesses
Group 1 / Fig 32: Clear, simple, message easily grasped, good for the trend data / Fig 32: No Under-five group represented (generally of most concern to WFP/Unicef)
Use of standard deviations can be confusing
Fig 14: / Fig 14: Line not particularly useful for indicating discrete categories – bars may be more appropriate
Crowded image, unclear without much study what the message is
Group
2 / Fig 13: Clear, provides much information in relatively simple format
Good overall view of situation / Fig 13: Can be difficult to get precision from the stacked bar
Fig 16: Use of stacked bars is appropriate here / Fig 16: May be simply too many livelihoods shown
Group 3 / Fig 19: Lots of information for technicians. / Fig 19: Definitely a technician’s chart.
Senior managers would not likely spend time reviewing so much detail; it is not easily grasped what the real message is here.
Fig 20: Lots of information for technicians. / Fig 20: Definitely a technician’s chart.
Senior managers would not likely spend time reviewing so much detail; it is not easily grasped what the real message is here.
Group 4 / Fig 8: In first chart, X and Y axes are clear, well-defined; and use of ovals to indicate peaks helps
Second chart is simple, clear, easily grasped / Fig 8: First graph a bit crowded; a bolder “X” axis would help distinguish between price increases and decreases
Second graph: price index clear but might be helpful to show base year
Fig 12: First bar chart is clear, simple / Fig 12: Second graph almost incomprehensible to average viewer without much study; line colours should be more differentiated; “Y” range is unclear: what would “120%” of sites accessible imply?
Group 5 / Fig 1: Clear, simple, easy to compare differences by year and geographic location / Fig 1: Use of data labels would help
Fig 43: Lots of information for technicians. / Fig 43: Definitely a technician’s chart.
Senior managers would not likely spend time reviewing so much detail; it is not easily grasped what the real message is here.

Wind up the exercise by noting that charts should add value to the report and make it clear what messages are being sent. Note: Just because Excel offers many features does not mean they are always needed: simplicity is key.

Introduction to Graphical Analysis

60minutes

Use PPTs 6-16 to present key aspects – strengthsand limitations – of using graphs to analyse prices and other market conditions. Emphasize the following with regard to the noted PPTs:

PPT 7: Stress the third point, that an apparent relationship between prices on a graph (convergence, divergence) does not necessarily indicate a meaningful relationship between those prices, and we must be careful about assuming so.

PPT 8: There is a need to understand the various factors that may actually be responsible for the apparent relationship. Move to PPT 8 for an example.

PPT 9: First indicate the sharp changes in prices. Then show the list of “other factors” and review how each likely contributed to those changes. Stress that it is critical to understand the environment in which price changes occur, to identify the particular drivers of those changes

PPT 10: Stress the two messages: relying solely on the graph is not sufficient. The analyst must understand the operating environment as well.

PPTs 11 – 14: Use the images here to note key aspects of graphical analysis: increasing or decreasing price trends, price dispersion, and price volatility.

PPT 15: Review each point (accompanied by the animated images.) “Data labels and markers” is included to highlight the occasional need on the graph for precision. The next point – appropriate imprecision – is here to highlight that graph developers sometimes include far too much precision for the needs of the user. The message: know your audience.

Finally, on the “Missing data” point, move to PPT 16 and review the points. Then:

  • Open up Excel file “2.1. Dealing with Missing Data.xls. ” Review on-screen the two techniques shown here (averaging values and the “centered moving averages “ or “CMA” approach. Go through the various formulas behind the values in Red Font. Note that there is inherent bias in using any such technique and some choose simply to leave the data value blank.

Show PPT 17 and ask if participants have specific questions about using particular Excel features. If you have time left in the hour, this would be a good time to go over these particular needs, as the next exercise will require some use of Excel.

Use the rest of the hour to go over these particular needs: take 5 minutes to note their questions to flip chart and then proceed to go over them in order.

Exercise 2.1.b. The Marketastan File: Excel Charts for Senior Managers

60 minutes

This exercise is intended to (1) test participants’ understanding of Excel and (2) enable them to use their creativity with the software. Form them into small groups (pairs or triads), and have them open up Excel file “2.1.b. Charts for WFP-Marketastan Senior Managers – Excel File.xls” on their computers. Have them turn to Exercise 2.1.b in their workbooks. Several statements intended to guide the participants’ production of Excel graphs are listed there. They are:

  1. Most Marketastani households in the north tend to spend the lion’s share of their income on food.
  2. The increases in the price of wheat of the last half of 2009 that hit the north, central and southern provinces of Marketastan reversed the declines that had improved household food access during the first half of the year. Some analysts forecast that 2010 could see prices as high as were seen in the latter part of 2008. Some say this is quickly becoming the worst situation in many years, particularly for Northern Province.
  3. The average wheat price across Marketastan generally spikes upward around April of each year, and tends to fall in July.
  4. In 2009, real wages of the working poor continued their upward trend which began back in mid-2008.
  5. Of the three main livelihood groups in northern Marketastan (farmers, pastoralists and traders), pastoralists were by far the most food insecure.

You should feel free to use these statements or create your own for the session. The important point is to get participants’ thinking about the best way to communicate a message graphically.

Give them up to 60 minutes, depending upon your perception of their abilities. Move around the room and offer guidance on using Excel, its features, and the best way to present the graphs. Make not of which group(s) seem to be producing high-quality graphs which present the statement well.

Exercise debriefing

45 minutes

After an hour, show PPT 17 and ask the group with a high quality response to Statement #1 to project its response on the screen. Have the other groups then comment, ask questions, or critique as needed. Ask all in plenary how the graph might be improved: whether they feel the graph is unnecessarily technical, or if it satisfies the task of communicating the message well to senior managers. Once you have exhausted the comments for Statement #1, go to another group and ask its participants to project their response. Repeat the process for each statement.

Their responses should be somewhat similar to the following (allowing, of course, for additional creativity by the participants).

  1. Most Marketastani households in the north tend to spend the lion’s share of their income on food. (Pie or column)

  1. The increases in the price of wheat of the last half of 2009 that hit the north, central and southern provinces of Marketastan reversed the declines that had improved household food access during the first half of the year. Some analysts forecast that 2010 could see prices as high as were seen in the latter part of 2008. Some say this is quickly becoming the worst situation in many years, particularly for Northern Province.

  1. The average wheat price across Marketastan generally spikes upward around April of each year, and tends to fall in July.

  1. In 2009, real wages of the working poor continued their upward trend which began back in mid-2008.

  1. Of the three main livelihood groups in northern Marketastan (farmers, pastoralists and traders), pastoralists were by far the most food insecure.

Finally, wrap up the session with PPT 20, noting that both data quality and presentation format are critical in graphical analysis.

Annex 1

Exercise 2.1.a. Charts and Graphs – Strengths & Weaknesses

Task: Analyse your team’s assigned charts[1] and discuss:

  • What types of data are being presented (e.g. discrete events or trends)?
  • What are the main messages the chart is trying to communicate to senior management?
  • What are the chart’s strengths/weaknesses in communicating these data/messages?
  • What recommendations can you make for improving presentation of this information?

Group 1:

Group 2:


Group 3:


Group 4:


Group 5:


Exercise 2.1.b. Charts for WFP/Marketastan Senior Managers

Read the statements below and then, using the data file “2.1.b. Charts for WFP-Marketastan Senior Managers – Excel File.xls” provided by the facilitator, create a chart in Excel to depict the key messages of each statement.

  1. Most Marketastani households in the north tend to spend the lion’s share of their income on food.
  2. The increases in the price of wheat of the last half of 2009 that hit the north, central and southern provinces of Marketastan reversed the declines that had improved household food access during the first half of the year. Some analysts forecast that 2010 could see prices as high as were seen in the latter part of 2008. Some say this is quickly becoming the worst situation in many years, particularly for Northern Province.
  3. The average wheat price across Marketastan generally spikes upward around April of each year, and tends to fall in July.
  4. In 2009, real wages of the working poor continued their upward trend which began back in mid-2008.
  5. Of the three main livelihood groups in northern Marketastan (farmers, pastoralists and traders), pastoralists were by far the most food insecure.

Your goal is to produce graphical images that communicate as clearly – and as simply – as possible the intended messages for WFP senior managers in Marketastan.

Page 1 of 14

[1]All graphical images from WFP: Lao PDR: Comprehensive Food Security and Vulnerability Analysis (CFSVA), WFP, December, 2007.