How to Create Material Reconciliation Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

Practically, the Material Reconciliation Format is one of the most used formats in Excel in manufacturing or construction processes. It is very handy to create a Material Reconciliation Format in Excel of your own and use it whenever you need. This article will demonstrate how to create a basic Material Reconciliation Format in Excel with very easy steps.

material reconciliation format in excel


What is Material Reconciliation?

Material Reconciliation is mainly checking and analyzing the materials of a project at the end of the process. In any manufacturing or construction industry, the consumption of a specific amount of material is very significant. Material Reconciliation tells us whether the project is going on as per it was planned or not. Mainly, an expert compares the Difference between the Assumed Consumption and Actual Consumption. Moreover, other information about the materials is also included in a Material Reconciliation Format. Therefore, a visitor can easily analyze the process and take necessary steps to increase the efficiency of the project.


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

Suppose we have some information regarding the material estimated for the Ecozen Bridge Project. In this project, the company bought and kept the materials at the site. Daily usage of the materials for each work is estimated. In any construction, it is very important to use the materials in exact amounts which are calculated theoretically. If we want to check if the materials are used according to the assumed amount or not, we need to create a Material Reconciliation Format in Excel.

To create a Material Reconciliation Format, explore the following steps carefully.


Step-01: Create a Table

In this project, there are 5 types of work. These are RCC, PCC, Plaster, DPC, and Brick Work. For these works, we need Cement, Sand, Coarse Aggregate, Bricks, and Acco Proof. Firstly, we will create a table according to the information we received.

  • To begin, create a Table with the necessary headings.
  • Afterward, insert the name of each Work and the Quantity of the work as table header.
  • Besides, change the Fill Color of the Table Header to make it look attractive.

Creating table for Material Reconciliation Format


Step-02: Input Material Data

Succeeding, we will input the data in the table created previously.

  • Firstly, input the Quantity of each work in the table. Be careful about this because practically, there would be so many columns and if you input a single wrong data, the entire Material Reconciliation Format will be ruined.
  • Later, input the amount of each material.

 Dataset of Estimated Material


Step-03: Apply Necessary Formulas for Reconciliation

This is the most important step where we will use the SUM function and formulas to calculate necessary information from the table.

  • First and foremost, insert some more rows in the table.
  • These are Total Consumption (Assumed), Quantity Purchased, Remaining Quantity (Assumed), Current Quantity (at site), and Difference.
  • Next, insert the Quantity Purchased and Current Quantity (at site) information carefully in the chart.
  • Now, the table is ready to apply the formulas.

Dataset of Material Reconciliation

  • Insert the formula in Cell E11.
=SUM(E6:E10)
  • Next, 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.
  • Secondly, copy the formula through the entire row shown in the snapshot.

Dragging Fill handle to Copy Formula

  • Consequently, we got the Total Consumption (Assumed) for each material with the help of the AutoFill feature of Excel.

Computing Total Consumption using SUM function

  • After this, calculate the Remaining Quantity ( Assumed).
  • In the beginning, write down the formula in cell E15.
=E14-E11
  • Then, 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.
  • Moving forward, copy the formula to the entire row.
  • Therefore, we will get the Remaining Quantity (Assumed) for each Material.

Dragging Fill handle to calculate Remaining Quantity

  • Correspondingly, we got the desired information for each material by using the AutoFill.

Final output of Remaining Quantity (Assumed)

  • Finally, copy the formula to cell E19 to compute the Difference between Current Quantity (at site) and Remaining Quantity (Assumed) for cement.
=E17-E15
  • Accordingly, 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.
  • In conclusion, 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-04: Format the Table

To make the Table easy to read, we will apply some Formatting to it.

  • To start, select the row of RCC.
  • Next, click on Fill Color.

Changing Fill color of a row

  • At this point, select a Fill Color according to your choice.
  • Here, 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

  • Following this, repeat the same action to change the Fill Color of the rest of the rows.
  • Thereby, 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.
  • In that case, you can follow how we did this in the given snapshot.

Changing Font and Fill Color of a cell

  • Following this, you can give 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.
  • Earlier, select the 19th Row which is Difference.
  • Then, click on the Border icon.

Giving Thick Outside Border to a row

  • Then, select Thick Outside Borders from the options.

Selecting Thick Outside Borders option

  • Eventually, you can see a Thick Border outside the row.

Final Output after giving Thick Outside Border

  • Presently, change the Font Color of the Difference row.
  • First, select the entire row.
  • Then, click on the Font Color.
  • Finally, select a color of your own choice.

Changing font color of the Difference values

  • Finally, we have completed the Material Reconciliation Format in Excel.

 A Material Reconciliation Format in Excel


Step-05: Prepare a Reconciliation Report

After analyzing the Material Reconciliation, you must prepare a Reconciliation Report for the higher authority. 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 for you to practice.

Practice sheet


Download Practice Workbook

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


Conclusion

In this article, we have tried to give you an idea about creating a Material Reconciliation Format in Excel. What we have given is an example. You may have to create the format with different data and materials. So, try to follow the steps and customize your Material Reconciliation Format according to your demand. If you face any problems regarding this article, please comment so that we can help.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

 

 

ExcelDemy
Logo