How to Create Material Reconciliation Format in Excel

Consider the following dataset of a manufacturing company. We’ll use it to demonstrate how to create a material reconciliation template.

How to Create Material Reconciliation Format in Excel: Step-by-Step Procedures

Suppose we have some information regarding the material estimated for a project. The company bought and kept the materials at the site and estimated how much materials they need daily. Let’s create a table to more closely follow material usage.

Step 1 – Create a Table

In this project, there are 5 types of work. These are RCC, PCC, Plaster, DPC, and Brick Work. For thes, we need Cement, Sand, Coarse Aggregate, Bricks, and Acco Proof in different amounts.

Create a Table with the necessary headings.
Insert the name of each Work and the Quantity of the work as table header.
Change the Fill Color of the Table Header to make it look attractive.

Step 2 – Input Material Data

Input the Quantity of each work in the table.
Input the amount of each material.

Step 3 – Apply Necessary Formulas for Reconciliation

Insert the following rows in the table: Total Consumption (Assumed), Quantity Purchased, Remaining Quantity (Assumed), Current Quantity (at site), and Difference.
Insert the Quantity Purchased and Current Quantity (at site) information carefully in the chart.
See the example table, which is ready to apply the formulas.

Insert this formula in Cell E11:

=SUM(E6:E10)

Press Enter to get the Total Consumption (Assumed) of Cement.

Here, this formula returns the summation of the cell range E6:E10.

Copy the formula through the entire row via the Fill Handle:

We received the Total Consumption (Assumed) for each material with the help of the AutoFill feature of Excel.

Calculate the Remaining Quantity (Assumed). For that, copy this formula in cell E15:

=E14-E11

Press Enter to get the value of Remaining Quantity (Assumed) for Cement.

Now, this formula subtracts the value of cell E11 from cell E14. This means subtracting the Total consumption from Quantity Purchased.

Copy the formula to the entire row. We will get the Remaining Quantity (Assumed) for each Material.

We got the desired information for each material by using the AutoFill.

Copy this formula to cell E19 to compute the Difference between Current Quantity (at site) and Remaining Quantity (Assumed) for cement:

=E17-E15

Press Enter to get the result in cell E19.

Here, the formula subtracts the value of cell E15 from cell E17.

Copy the formula to the rest of the cells of the row to calculate the Difference for all the materials.

We got the Difference for Sand, Coarse Aggregate, Bricks, and Acco Proof by implementing the AutoFill feature.

Step 4 – Format the Table

Select the row of RCC.
Click on Fill Color.

Select a Fill Color according to your choice. We have chosen a shade of Green.

After selecting the Fill Color, the selected row will become colorful like the image below.

Repeat the same action to change the Fill Color of the rest of the rows. The Material Reconciliation Format will look like the picture below.

You can also change the Font Color and Fill Color of the other headings.
Here’s how we did that:

You can make a Thick Border to make the Difference part more prominent as this is the most essential part of the Format. The visitor normally checks this. Select the 19th Row which is Difference.
Click on the Border icon.

Select Thick Outside Borders from the options.

You can see a Thick Border outside the row.

Let’s change the Font Color of the Difference row.

Select the entire row.
Click on the Font Color.
Select a color of your own choice.

We have completed the Material Reconciliation Format in Excel.

Step 5 – Prepare a Reconciliation Report

Here is an example of the report of the mentioned Project.

Things to Remember

Be careful about entering the values in the table.
Don’t apply too much formatting that it would be tedious to read.
Try to summarize everything in a sheet so that the reader can read the values at a glance.

Practice Section

Here, we have provided a practice sheet that can be used as a template.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

Creating Material Reconciliation Format.xlsx

<< Go Back to Excel Reconciliation Templates | Accounting Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!