Calculate total landed cost

- [Instructor] Every time that Kinetico decides to purchase something from a supplier, they need to perform a Total Landed Cost analysis. Total Landed Cost, or TLC, represents the complete cost of purchasing and delivering a product to its final destination, including all of the expenses along the supply chain. Understanding TLC is crucial for making informed purchasing decisions. Let's walk through the steps to calculate TLC using Excel. To calculate the Total Landed Cost, you'll need the following key cost components, the unit price, shipping costs, customs and duties, insurance costs, handling fees, and other costs. Let's start by setting up our Excel sheet. If you've downloaded the exercise files, look for 0101 Total Landed Cost, and go to the tab named Single TLC Data. If you're building your own spreadsheet, you can enter the data that I'm showing here. To calculate the Total Landed Cost, we'll use the following formula. TLC equals the unit price times the quantity, plus shipping costs, plus customs and duties, plus insurance costs, plus handling fees, plus any other costs. In Excel, we'll do this as follows. Click on cell J2, where we want the Total Landed Cost result to appear. Then enter the formula equals C2 times D2, plus E2, plus F2, plus G2, plus H2, plus I2, and press return. This formula multiplies the unit price by the quantity and then adds all of the other costs to calculate the Total Landed Cost. After entering the formula, Excel will calculate the TLC for you. In this case, it's $5,450. This represents the total expense of getting a shipment of the product from the supplier to its final destination. Knowing the total cost is helpful, but what we often really need to know is the cost for each unit in the shipment. So we need to divide the total cost for the shipment by the number of units. In cell K2, enter the formula equals J2 divided by D2, and press return. This shows us that the Total Landed Cost for each unit we purchase from this supplier is $54.50. Good job. You can check your work by looking at the tab labeled Single TLC Solution. Next, we can compare the Total Landed Costs of different suppliers by entering their respective costs into additional rows in Excel. This allows us to see not just the price difference but the overall cost impact, helping us choose the most cost effective supplier. To make the comparison easier, we can create a summary table that lists each supplier's Total Landed Cost. We can use conditional formatting to highlight the lowest cost, giving us a clear visual indication of the most economical option. Go to the tab named TLC Summary Table Data, and you can practice adding the formulas to column J and K for the three different suppliers. You can check your work by referring to the TLC Summary Data Solution tab. You should find that supplier three provides the lowest TLC for each shipment, but supplier one has the lowest TLC per unit. So which supplier really offers Kinetico the best deal for this item? Probably supplier one. By calculating the Total Landed Cost using Excel, we can make more informed purchasing decisions that consider all of the costs, not just the unit price. And that way we can ensure that we're getting the best overall value.