Introduction
Normally, I use Excel for analyzing financial data. Today, I encountered a financial problem for which Mathcad seemed appropriate-- there was a bit of algebra involved. Let's see what you think ...
Background
Before I can state my problem, we need to establish a common vocabulary. A few definitions are in order.
Some Definitions
- Factory Cost
- The cost of a fully assembled, tested, and shipped product.
- Freight
- The cost of shipping the product to our distribution center. For example, if you are using an express freight service across europe the cost will be varied.
- Bill of Materials (BOM)
- The list of all the components that make up the product, such as whether they need shipping warmers to ensure they stay at a constant temperature during transportation.
- BOM Cost
- The cost of all the components that make up the product. BOM cost does not include any freight or assembly charges.
- Conversion Cost
- The cost of assembling the components into the product. Conversion cost (by agreement with our contract manufacturer [CM] ) does not include shipping cost.
- Markup
- The difference between factory cost and BOM cost. Markup includes freight.
- Conversion Cost Percentage
- Conversion cost as a percentage of factory cost minus freight. Freight varies for each product and final customer. The efficiency of a factory is often measured in terms of its conversion cost percentage. I am VERY interested in conversion cost.
- Markup Percentage
- Markup as a percentage of factory cost. Markup includes freight.
My Problem
I had a large number of products for which I wanted to evaluate their conversion cost percentage. Normally, our CM lists the conversion cost percentage for each product on a spreadsheet they send to us, which I then use as a way of comparing my manufacturer's efficiency against other manufacturers. Unfortunately, my CM did not list the conversion cost percentage of each of the products I was interested in. Instead, they gave me the following information:
- Factory Cost
- Markup Percentage
- Freight (listed on another spreadsheet)
There is a tiny bit of algebra involved in computing the conversion cost percentage, so I decided to use Mathcad's symbolic solver to perform that function. I could do it manually, but it is late Friday afternoon, I am tired, and I do not want to make any errors.
Analysis
Figure 1 shows my solution of this problem in Mathcad.
Figure 1: Determination of Conversion Cost for a List of Products.
I have attached a zip file containing the Mathcad15 file here.
Conclusion
My quick analysis showed that I was being charged a higher conversion cost than I had expected. A bit of investigation soon explained the issue, which I can now deal with.