ABP: Analysis-by-Parts with Manually Margining Bill of Goods
These instructions are an optional component of the Bill of Goods ABP approach.
INTRODUCTION:
When you have a Bill of Goods (BOG) or line-item budget available in addition to your labor costs, you may want to create a spending pattern unique for your Industry using Analysis-by-Parts (ABP).
The Intermediate Expenditures that are purchased by the business can be modeled in several different ways. Intermediate Expenditures are determined and modeled by IMPLAN for Industry Events, but they can also be modeled separately as a part of an ABP analysis within an Industry Spending Pattern or on an individual basis as a Commodity Output Events.
When Intermediate Expenditures are purchased via retail or wholesale venues, Margins become relevant and therefore using the Commodity approach may be more suitable. Since the prices paid to a wholesaler or retailer are purchaser prices and not producer prices, adjustments need to be made to the sales value.
DETAILED INFORMATION:
ABP can be an incredibly useful technique for digging deeper into a firm’s budgetary expenditures, but when we look at retail or wholesale purchases, it is important that these purchases are handled carefully. The reason for this is that IMPLAN understands values only expressed in producer prices, but a retail or wholesale gross sales value (known as the purchase price) includes not only the production component of the retail or wholesale venue but also the production of all the elements of the Value Chain that proceed it. Thus, the gross sales value of a retail sale is a combination of the Retail Value, Wholesale Value, Transportation Value, and Producer Value. The wholesale gross sales value is actually a combination of the Wholesale Value, Transportation Value, and Producer Value. Thus, if we create a coefficient of spending for either a retail or wholesale component without Margining, or if we apply the entire value to the production Sector’s coefficient, we will be mis-estimating the impacts on those respective Sectors.
There are two possible situations that might arise:
- Purchases are known to be retail or wholesale, but the specific items purchased are unknown.
- Purchases are known to be specific items, but the value associated to them is a wholesale or retail gross sales value.
These two situations will be handled in the Usage section in two different examples using simplified spending as examples.
THE PROCESS:
Example 1: Retail or Wholesale Purchase Value Is Known, but the Item Purchased Is Unknown
A Kansas flour milling business, Best of Grains, buys supplies as part of its production function. We know that of their $15,000,000 Output value they spend $1,000 a year at the local Office Retail Superstore and $21,600 through a Wholesaler. Our first inclination might be to simply create coefficients based on the respective portions of the total cost that these purchases represent. If we were to do so, we would represent these coefficients as:
However, this would be attributing too much value to the Retail and Wholesale Sectors involved.
In order to determine what the Margin should be for each of these Sectors, we need to adjust the sales value further by determining what portion is kept by the retailer and wholesaler respectively. Note that in this circumstance, because we do not know the items purchased, the value that cannot be attributed to the retailer or wholesaler is leaked from the spending pattern.
We will get the Margin value from the “2017 COMMON (IMPLAN5) MARGINS” spreadsheet.
Column A: The Producer (in this case the producing Sector would be grains)
Column B: The Industries and Commodities available to receive component parts of the Value Chain (Retail Value, Wholesale Value, Transportation Value, and Producer Value). Note that while not all the Retail Sectors (369-407) have values in column C, they are all present.
Column C: The portion of the Total Sales value that goes to the Sector listed in Column B. Also, the Retail and Wholesale Sectors (395-407) have the same Sector designation in Columns A and B.
For our example, we do not know exactly what is being purchased, so we are going to highlight Column A and use the Find function in Excel to search for 3395 – Wholesale Trade, as well as 3406 – Retail- Miscellaneous Retail Stores. You should see the portion of the table captured below:
With this new information, we will now reformulate our coefficient calculation to include the Margined values captured from the spreadsheet above.
Thus when we added these components to our spending they would be represented as their Margined coefficients.
Now we can enter the information with the rest of the Bill of Goods as shown here.
Example 2: Item Purchased Is Known, but the Value Is Expressed in Retail or Wholesale Costs
Best of Grains also buys grain as one of the primary components of its production. Of their $15,000,000 annual Output, they spend $8,400,000 on grain, but they purchase the grain through a wholesale food provider.
Just like above, we cannot directly attribute the $8.4 million in production to Sector 2 – Grains. But unlike in Example 1, we know exactly what our flour mill is purchasing in this circumstance. But now there is also an added caveat. When we look at the splits for Sector 2, we see that all elements of the Value Chain are included in the spreadsheet: Margin splits for Retail Value, Wholesale Value, Transportation Value, and Producer Value. However, we know there is no retailer because the purchase was made through a wholesaler. We will need to recalculate the Margin splits by adjusting the provided values so that the portion of the Margin assigned to the retailer in the table is redistributed into the wholesale, transport, and production elements of the Margin.
To do this we will need to zero out the retail Margin and normalize the remaining Margin values. We can normalize the remaining Margins as follows:
Now that we have adjusted the Margins to account for the entire sales value without the retail component, we can apply the Margin values to the grain purchase to determine how much of the grain purchase goes to each of the other respective Sectors.
So now we split the Grain entry into the new margined pieces as shown here.
While this represents a simplified example, the same principles apply to Margining larger more complicated projects as well. Note that more than one component in a spending pattern will likely have Wholesale and Retail purchases. It is usually best, unless you want to create a single Event for each part of the Value Chain (retail, wholesale, transportation, production), to sum these coefficients on like Sectors (ie combining all the Wholesale) into their total component so you have only one Event for each.
Remember, in these cases Capital Expenditures should be modeled separately from any operations or construction of the business.
ADDITIONAL CONSIDERATIONS:
What about the Local Purchase Percentage?
This should be set to SAM Model Value. This will allow the Model to make estimates of local purchasing ability of all Commodities.
Thinking about Transportation
Typically there are two legs of transport: between the producer and the wholesaler and between the wholesaler and the retailer. The transportation expenses should be accrued into a single Sector. If you are using the IMPLAN Margin splits as outlined in this article, both legs of transportation are included in the respective Margin components. Keep in mind, however, that the first leg could be via ship from the producer to the wholesaler, while the second leg is via truck from the wholesaler to the retailer.
Zeroing out the Retail Margin
In this case, we re-normalized across all Margin Sectors to remove the retail component in Example 2. However, this is not the only option, though it is typically the most conservative one. In contrast, you could argue that the wholesaler and transporters do not get a larger cut when there is no retailer. Perhaps it is more likely that the producer gets to keep the difference or splits the difference with the wholesaler. You can make these adjustments based on what you know about the Industry, and then state how you handled the normalization process in your results.
RELATED ARTICLES:
ABP: Introduction to Analysis-By-Parts
ABP: Analysis-by-Parts & Bill of Goods Using Commodity or Industry Events with Labor Income Event(s)
ABP: Analysis-by-Parts Using an Industry Spending Pattern Event with Labor Income Event(s)
Hospitals: Modeling Private Hospital Impacts with Analysis-by-Parts
Hospitals: Modeling Public & Nonprofit Hospital Impacts with Analysis-by-Parts
will display the default selection of Intermediate Expenditures, indicating the Value entered in the Event should be the value of Intermediate Expenditures. This can be changed so that Value entered should be Total Output, in which case the regional proportion of Intermediate Expenditures out of Total Industry Output for the selected Industry is multiplied by the Value before it is modeled as spending on the commodities included in the Industry Spending Pattern. The proportion can be found as the Total Gross Absorption percentage found in Region Details > Social Accounts > Balance Sheets > Industry Balance Sheet > Commodity Demand. Whether Intermediate Expenditures or Total Output is selected, the Sum of Percentages will stay at 100% unless you modify the Percentage on an individual commodity. 
