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:
No matter the name, the main function is a checklist, making sure all materials required have been accounted for.
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.
In this presentation we will be looking at:
So obviously we need to determine our column headings:
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!
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:
You now have a table!
Now you have a table that allows for filtering. For example let’s say I go to:
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!
Can also ask the following:
Can use a formula that says:
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
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.
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
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.
So with the use of a few spreadsheet functions we have:
Is there anything else we can do?
What if you were looking at the connections category and you wanted to know:
How does this all help? Well, we now know that:
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
