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.

material reconciliation format in excel


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.

Creating table for Material Reconciliation Format

Step 2 – Input Material Data

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

 Dataset of Estimated 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.

Dataset of Material Reconciliation

  • Insert this formula in Cell E11:
  • Press Enter to get the Total Consumption (Assumed) of Cement.

 Using Excel SUM function to create Material Reconciliation Format

Here, this formula returns the summation of the cell range E6:E10.
  • Copy the formula through the entire row via the Fill Handle:

Dragging Fill handle to Copy Formula

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

Computing Total Consumption using SUM function

  • Calculate the Remaining Quantity (Assumed). For that, copy this formula in cell E15:
  • Press Enter to get the value of Remaining Quantity (Assumed) for Cement.

Calculating Remaining Quantity

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.

Dragging Fill handle to calculate Remaining Quantity

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

Final output of Remaining Quantity (Assumed)

  • Copy this formula to cell E19 to compute the Difference between Current Quantity (at site) and Remaining Quantity (Assumed) for cement:
  • Press Enter to get the result in cell E19.

Applying subtraction formula to compute Difference

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.

Using AutoFill Feature to determine Difference of the Material Reconciliation

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

Final output of the Difference in creating Material Reconciliation

Step 4 – Format the Table

  • Select the row of RCC.
  • Click on Fill Color.

Changing Fill color of a row

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

Selecting Fill color

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

Output of changing Fill Color

  • 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.

Final look after changing Fill color

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

Changing Font and Fill Color of a cell

  • 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.

Giving Thick Outside Border to a row

  • Select Thick Outside Borders from the options.

Selecting Thick Outside Borders option

  • You can see a Thick Border outside the row.

Final Output after giving Thick Outside Border

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.

Changing font color of the Difference values

  • We have completed the Material Reconciliation Format in Excel.

 A Material Reconciliation Format in Excel

Step 5 – Prepare a Reconciliation Report

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

Material Reconciliation Report

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.

Practice sheet

Download Practice Workbook

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

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mahfuza Anika Era
Mahfuza Anika Era

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF