Commercial solar material list spreadsheet

Monday, August 9, 2021
Training
by
Veli Markovic

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


About the author

Veli Markovic

CEC Designer
Veli has nearly two decades of experience in the renewable industry. He is passionate about providing people with valuable education and is highly regarded throughout the industry as an educator and operator.
Training videos

Every week we publish new videos and walkthroughs online.

More Posts

Related posts for you

Energy storage and your home or business
Training
September 22, 2021

Energy storage and your home or business

Commercial Grid connect signage
Training
September 21, 2021

Commercial Grid connect signage

Solar export tariffs! What, more changes?
Training
September 21, 2021

Solar export tariffs! What, more changes?

Over current protection:overload and SC, circuit breakers and fuses
Training
September 13, 2021

Over current protection:overload and SC, circuit breakers and fuses

Heat Pumps and solar
Training
September 13, 2021

Heat Pumps and solar

Satellites, space and solar panels
Training
September 13, 2021

Satellites, space and solar panels

Looking for a partner to deliver your sustainable vision?