MEE Case Studies

Ground Beef Industry

How much is that ground beef in the window? How much should it be?

In this case, we will examine the Value (as sustainable price) of ground beef. It comes in one to 100-pound packages, which vary in leanness from 50% to 100%. How can 1) buyers, 2) existing sellers and 3) new sellers benefit from Value Analysis using MEE4D?

First things first: What is our Hypothesis?

We use MEE4D to find relationships in markets. With this problem, we are trying to predict the sustainable price of ground beef, the value of it. What would you guess determines the price of ground beef? And yes, often we start with just a guess – we don’t know how this market works yet, we must make some suppositions to get started. So, let’s guess.

We know that we can go to our local supermarket and see the colors of the color of the ground beef packages. We could hypothesize that what we are willing to pay for ground beef depends on its freshness, which we could get by its color (red is good, gray is bad), or by its “sell-by” date, and prices go up with improving color and sell-by dates. But, while we can get sell-by dates by package type at the retail grocers’ quite easily, we don’t want to go to the trouble of making a color scale for ourselves. Furthermore, we won’t be able to find any information about color and freshness at the wholesale level. But we can get package sizes, prices, and leanness of these products just by observing what the grocer has in stock. With a little research, we find out that get the same information from a beef retailers’ organization.

So, then, we might be begin by hypothesizing that the prices that we are willing to pay for ground beef are a function of the package size’s and leanness’s. To test the hypothesis, we need some organized information, thus, we set about building a database.

Building a Database

All your analyses in MEE4D require data. The example at hand is no exception. Since we just want to do a quick analysis of local retail prices compared to those at the national wholesale level, we will survey one Southern California retailer, Wal-Mart store 3523, and a large organization representing the wholesalers, www.beefretail.org. When we combine the data from both sources, we can form a table as shown below.

This table has 20 observations, five from the wholesalers, 15 from retailers. Note that we cite our sources for both – this is something that you should always do in Excel, to help provide traceability for your analyses (in practice, putting these sources in the comment field of each cell will dramatically reduce clutter).

We have five columns in this table, three of which address numerical values, two that use characters. We must keep this in mind as we move to the Excel template for MEE4D. Below, we “copy and paste” the table and place the data into the template.

Observe that both “type of seller” and “data site” columns are clearly “characters,” and we choose that designation under “data types.” The last three columns of our table deal with non-integer values, and we use “float” as their data type. When we hit the “Check Data” box in the upper left-hand portion of the template, the template indicates that there were “No Errors found. Proceed to save and import file into MEE4D software.” When import the file into MEE4D, we must ask for “All Excel Files,” as we are yet to transform this file into the *.m4d extension.

When we load the file, we are presented with this view. The program has opened to the “Database” tab.

If we wanted to, here we could do some filtering (removing pieces of the dataset from consideration) or highlighting.

In “Model” tab, we get this view. Since we want to predict the dollars per pound, we have selected it to be our dependent variable.

Note that we have two independent variable choices (indep var choices), size and leanness. We will want to use both.

When we select both independent variables, we get this linear equation result.

Note that both independent variables pass the “P-Test” (less than 1 chance in 20 that the prediction came about due to chance). However, we might wonder if we can do better, so we select the “Power” equation form.

When we ask the program to determine the power form model of this market, we get this result.

Note that all equation statistics have improved using this technique. This is the same result that you would get in Excel. However, there is problem with such results. Their constants are biased to the low side. We can correct this by applying the Ping Factor.

Rerunning the equation with the Ping Factor yields this result:

Note that the constant moved from 5.27904 to 5.29284. This small shift upward (of 0.26%) removed the downward bias of our original equation. The lower the Pearson’s2, the greater this adjustment will be. Now we want to “View Full Regression Results.”

Here we get more statistics about our model. We find that we have a Standard Error of 0.27, which, since we are predicting dollars, is $0.27.

Our “F-Stat” (the ratio of the explained variation to the unexplained variation) is exceptional at 234, as is the “P-Value” for the equation, which at 4.29E-13 indicates the chance of this equation predicting its outcomes due to chance is very miniscule.

We also find the “P-Values” for our independent variables, both of which are far below the 0.05 threshold.

In the lower part of this page, we find the analysis of variation, which shows how well the model did for each observation. In this case, we may want to copy out part of the table and place it into Excel for a more detailed examination.

When get the data into Excel and form a table around it, we get the view that we have at left. This table provides insight to buyers, existing sellers and new sellers.

For Buyers:

At the wholesale level: Data Point 3 is under priced by nearly $0.35. Buyers that like this type of product should stock up – they are getting a deal here.

At the retail level: Data Point 15 is under priced by over $0.48. This is a situation in which retail buyers can buy a product that is worth more than they pay for it.

For Existing Sellers:

At the wholesale level: Data point 3 is very problematic. It turns out that beef retailers sold about 290 million pounds of this product form to retailers. Thus $0.347 * 290,000,000 = $100.6 Million. This “$0.35 error” is really a $100 million error. Customers, based on their behavior, would have supported $0.35 more per pound for this grade. Not using the right price cost wholesalers over $100 million. We figured this out in an afternoon – don’t let anybody tell you that it is not worth the time investment to perform the analysis.

At the retail level: Data point 15 leaves nearly half a dollar per pound on the table of the consumer. This retailer needs to raise the price to its proper level instead of giving this consumer windfall away.

For New Sellers:

We have lots of retailers, and we know what the wholesale prices are. Perhaps we would want to set up a business in which we sell 25 pound packages to restaurants and small retailers who cannot sell 100 pounds of product in a timely fashion. In this model, we could buy 100 pound packages from the wholesalers and repackage them into 25 pound units. We know that we can buy the 100 pound packages that are 90% lean for $2.06/pound, or $200.60 per hundred pounds. In the analyze portion of the model, below, we model a 25 pound product that is 90% lean and see that we could command $2.907 (call it $2.91/pound), or $72.75 per 25 pounds, or $291 for 100 pounds (see the “Dlr per lb = 2.907 dollars at the screen’s left center). This would give us a profit of $291 - $200.60 or $90.40 before overhead. Smaller packages (say, 10 lbs.) would offer higher margins. Even if the wholesalers mark all of their prices correctly, there may be a market for intermediate sellers.