Mahfuza Anika Era
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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
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.
Step-by-Step Procedures to Create Material Reconciliation Format in Excel
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.
Read More: How to Reconcile Data in Excel (4 Easy Ways)
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.
Read More: How to Do Reconciliation in Excel (12 Effective Techniques)
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.
- Insert the formula in Cell E11.
=SUM(E6:E10)- Next, press Enter to get the Total Consumption (Assumed) of Cement.
- Secondly, copy the formula through the entire row shown in the snapshot.
- Consequently, we got the Total Consumption (Assumed) for each material with the help of the AutoFill feature of Excel.
- 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.
- Moving forward, copy the formula to the entire row.
- Therefore, we will get the Remaining Quantity (Assumed) for each Material.
- Correspondingly, we got the desired information for each material by using the AutoFill.
- 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.
- In conclusion, 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.
Read More: How to Reconcile Data in 2 Excel Sheets (4 Ways)
Similar Readings
- How to Do Intercompany Reconciliation in Excel (2 Easy Methods)
- Automation of Bank Reconciliation with Excel Macros
- How to Reconcile Vendor Statements in Excel (2 Easy Methods)
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.
- At this point, select a Fill Color according to your choice.
- Here, we have chosen a shade of Green.
- After selecting the Fill Color, the selected row will become colorful like the image below.
- 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.
- 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.
- 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.
- Then, select Thick Outside Borders from the options.
- Eventually, you can see a Thick Border outside the row.
- 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.
- Finally, we have completed the Material Reconciliation Format in Excel.
Read More: How to Reconcile Two Sets of Data in Excel (9 Simple Ways)
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.
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.
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.
