How to Create Material Reconciliation Format in Excel

This guide will explain how to set up a material reconciliation table in Excel for manufacturing or construction projects.

Material reconciliation is the process of analyzing the amount of material used after a project has been completed. The actual amount of material used will be compared to the estimated amount of material expected for the project.

The final result of the material reconciliation table is the difference between the current quantity and the projected remaining quantity. This difference must be accounted for with a specific reason in a reconciliation report to be used to improve processes in the future.

In this guide, we will provide a step-by-step tutorial on how to create a material reconciliation table in Excel.

A Real Example of Creating Material Reconciliation Format in Excel

Let’s explore a basic manufacturing use case where we can use a material reconciliation table to identify discrepancies in a production process.

In the table above, we have three output materials our project needs to produce: plain cement concrete, reinforced cement concrete, and damp proof course. Each of these materials requires a certain quantity of raw materials to produce it. For simplicity, our example will only require three raw materials: cement, sand, and coarse aggregate. 

The team projects how much of a certain amount of each raw material is needed to produce the output. However, there will always be some discrepancies between the projected and the actual consumption.

We can create a material reconciliation table to have a better view of the discrepancies. In the image above, we’ve added additional rows to keep track of the projected and actual quantities for each raw material.

The Total Consumption refers to the total estimated amount of raw material used in the project. The Quantity Purchased value is the actual amount of raw materials that were purchased for the project. The Remaining Quantity is the estimated amount that should remain from the quantity purchased. The Current Quantity is the actual amount of raw material that remains after production.

We can find the total consumption by using the SUM function.

For example, if we want to find the estimated amount of cement that will be used in the project, we can use the following formula:

=SUM(C2:C4)

After finding the Total Consumption, we can find the Remaining Quantity by subtracting the total from the Quantity Purchased.

We can use the formula:

=C6-C5

Once we find the remaining quantity, we can subtract that value from the actual current quantity to get the difference in amount.

A large difference between the projected remaining quantity and the actual current quantity indicates that there may be inefficiencies with the production process or that the amount of output has changed from the original plan.

Click on the link below to create your own copy of our example.

Head to the next section to read our step-by-step tutorial on how to create a material reconciliation table in Excel.

How to Create Material Reconciliation Format in Excel

1. Start the material reconciliation template by adding the necessary headers. A basic table will need the serial number, the description of the output material, and individual columns for each consumed raw material during the production process.
   In the example above, our project involves the consumption of three main raw materials: cement, sand, and coarse aggregate.
   
2. Fill out the appropriate quantity of each raw material used for each output. Ensure that the units used in the input are consistent for each raw material.
   
3. Next, we’ll need to add four more rows to our material reconciliation table. Add row headers for Total Consumption, Quantity Purchased, Remaining Quantity, and Current Quantity.
   The values for the Quantity Purchased and Current Quantity should be added to the table by the user.
   
4. Use the SUM function to fill out the total consumption of a given raw material.
   In the image above, we used the formula =SUM(C2:C4) to find the total consumption of cement after producing plain cement concrete and other materials.
   
5. Use the AutoFill feature to find the total consumption of the remaining raw materials.
   
6. We can determine the remaining quantity by subtracting the total consumption from the quantity purchased.
   In our example, we’ll use the formula =C6-C5 to find the remaining quantity of cement.
   
7. Use the AutoFill feature again to find the remaining quantity of the other raw materials in the material reconciliation template.
   
8. We can now find the difference between the remaining quantity and the current quantity.
   In the example above, we used the formula =C8-C7 to find the discrepancy between the current quantity and the remaining quantity.
   

These are all the steps you need to follow to create a material reconciliation table in Excel. If you want to learn more about how Excel can help with budgeting expenses, you can read our guide on how to perform sensitivity analysis for capital budgeting.

That’s all for this guide!  Try browsing our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

Subscribe