Materials needed for a commercial solar system

‍

When organising a commercial solar system project a considerable amount of materials are needed. As well as all the major components like solar panels, inverters and framing there is a whole host of smaller but no less important items that are required.

‍

This list as known under many names including:

• Bill of materials
• Material list
• Material checklist

‍

No matter the name, the main function is a checklist, making sure all materials required have been accounted for.

‍

What form does this list take?

‍

This could be achieved with an A4 piece of paper, with various columns drawn up, showing names and prices of components, quantity and total price all way to purpose built software with all the bells and whistles.

Somewhere in between is a spreadsheet approach that allows easy entry of the various components and their pricing and with a bit of tweaking, a powerful tool that can help with the analysis of a project.

‍

The Spreadsheet approach

‍

In this presentation we will be looking at:

‍

• What column headings can be used?
• How to turn the data into a table using Excel ( can use other programs)
• Internal and external filtering
• Component price: total price ratios

‍

‍

Column headings

‍

So obviously we need to determine our column headings:

• Main component
• Sub category
• Component name
• Being used?
• Current price per unit
• New price per unit
• Quantity
• Supplier, part number and total price

‍

‍

This heading list can be shortened or expanded depending on the level of data you need. Once the headings are created then it is simply a case of populating the rows.

The main component categories are some you create and can be accessed via a drop list ( see arrow) and placed on the same or another sheet.

 This list should be detailed enough but not too much. I have restricted the number to approximately 17 ( could be a bit too much!)

The more headings the more powerful BUT don’t complicate it too much!

Convert to table

‍

So we have created some headings and started to populate the rows but what now. One option is to turn this data into a table.

‍

With excel this process is as follows:

• Click on insert tab 
• Click within the data
• Click on insert table
• Highlight the data to be converted
• Click OK

‍

You now have a table!

‍

Filters

‍

Now you have a table that allows for filtering. For example let’s say I go to:

• Main component
• I click on the arrow
• I unselect the Select All checkbox
• Click on DC
• What do I get?

‍

‍

Filters continued

‍

And I can do this for every heading so I find how many line items of each major component and how many sub components belong to this category, their price and the list goes on!

‍

‍

What else can I do?

‍

Can also ask the following:

• Is the part being used, N means its cost is not added to the total
• Current price, New price with more than one supplier
• Quantity ( self explanatory)
• The actual supplier of the goods for this project
• Part no. for ordering purposes
• Total price

‍

Total price

‍

Can use a formula that says:

‍

• If part not being used, n, do not add to total
• If part not being used dull the font and/or fill
• Select lower price between 2 or more prices

‍

‍

Can I filter outside the table?

‍

Sometimes filtering within the table becomes cumbersome but there is another way and that is by using some spreadsheet functions that extract the relevant information from the main table and then create its own lists that are separate but connected to the original table.

So how do we do that?

We use *Excel’s filter function

*check the spreadsheet you are using functionality

How does it work?

‍

The FILTER function allows you to  filter a range of data based on the criteria that you specify. In our case I can filter the Main component name or the sub category or whatever. 

By using this function external to the Table combined with a drop down list, can extract some interesting data.

‍

‍

Connections

‍

So first thing is to select an empty cell next to the table with plenty of room below

Type the equals sign then FILTER, then select across the full range of the Table

Then a comma, then select the column you want the result to be extracted from

In this case component name, put in an equal sign and click on cell reference O2

‍

‍

Connections continued

‍

The O2 cell reference is a drop down list of all the major components.

The total cost figure references the total sum of all the components used in the project and the cost of components selected uses the AGGREGATE function that adds up all the individual costs.

‍

What do we have?

‍

So with the use of a few spreadsheet functions we have:

• A full filterable table of components and all their details
• The ability to filter external from the table
• Cost of components selected
• The percentage of the total component cost of the project

Is there anything else we can do?

‍

We can look at the sub component category

‍

What if you were looking at the connections  category and you wanted to know:

• How many bootlaces fall under that category?
• What % of the connections category do they make up, $ wise?
• What % of the total project cost do they make up, $ wise?

‍

‍

The results

‍

How does this all help? Well, we now know that:

• Total cost of project is $382,942.62
• The connections component accounts for $3,465.51 and is 0.90% of the total cost
• The Heat shrink component accounts for $523.55, 15.11% of connections but 0.14% of the total

‍

Conclusion

‍

A bill of materials is an effective checklist and with the use of spreadsheets, can really get a lot of detail down. By filtering the data some interesting information can be extracted and there is no limit to the analysis that can be made using a spreadsheet approach.

If you’d like to see more of what Greenwood Solutions get up to in the real world of renewable energy, solar, battery storage and grid protection check out the following pages:

‍

https://www.greenwoodsolutions.com.au/industry 

https://www.greenwoodsolutions.com.au/commercial

https://www.greenwoodsolutions.com.au/commercial/customer-stories

https://www.greenwoodsolutions.com.au/news