Costing Spreadsheet

ColorCrest

New Member
A goal is to create a layout as orderly as attributes for column headers and records of items as rows. Think of an airport flight schedule board as a visual and how basic the layout is. Sometimes you can find an image of the board with a section in the lower right corner. For the public, that area might display an advertisement or such. Behind the scenes that area may show a summary of amounts to controllers. This is an area on a spreadsheet which is ideal to show summary amounts of costs and prices.
 

Evan Gillette

New Member
While I agree with almost all of your suggestions, I would say that your intended objective is counter to my original intent with the example. Sheet 1 was intended to act less like a spreadsheet and more like an "app" or UI with calculations and a very basic material catalog in the background. I will gladly admit that it never developed to that point, mostly because it serves its limited purpose and I don't tend to overdevelop things that will not be long lived. Likely by the time I would have this all cleaned up and a comprehensive list of materials it should be replaced by a database and interface (erp mrp system). In my opinion, an erp/mrp or accounting system is where all common/standard product pricing should live anyways. Thanks for the comments as I would image they will be helpful to others as well.
 

ColorCrest

New Member
While I agree with almost all of your suggestions, I would say that your intended objective is counter to my original intent with the example. Sheet 1 was intended to act less like a spreadsheet and more like an "app" or UI with calculations and a very basic material catalog in the background. I will gladly admit that it never developed to that point, mostly because it serves its limited purpose and I don't tend to overdevelop things that will not be long lived. Likely by the time I would have this all cleaned up and a comprehensive list of materials it should be replaced by a database and interface (erp mrp system). In my opinion, an erp/mrp or accounting system is where all common/standard product pricing should live anyways. Thanks for the comments as I would image they will be helpful to others as well.
I've mentioned you've got a good thing going and your list of materials is practically ready for a database and it only takes minor rearranging of the first sheet to open in a database. I've done that. If, and when, you want to grow the spreadsheet itself or into a database, the structure I've suggested is what will be necessary. In the meantime, you have a tool which works for you.
 

GB2

Old Member
Consider categorizing the long list of materials, services, and any sell-through products and treat them individually. Although, this example of three components may be sold as a single sign product with a name such as "Rigid Sign" for example, one can always track required components to cost-account accordingly.
View attachment 156597
More to come.
In this version of the Excel sheet, does anyone know how you would bring up a specific dropdown item/description list based upon the category choice, which I assume would also be a dropdown list. For example, if Media is chosen in the first column then the Description column would offer a choice of only vinyl materials and not the entire list of everything. If Laminate is chosen as the Category then only laminates will come up on the list for the second column.
 

ColorCrest

New Member
In this version of the Excel sheet, does anyone know how you would bring up a specific dropdown item/description list based upon the category choice
Search for terms something like "Excel dependent drop down list" on YouTube, etc. "Conditional drop list" is another term.

As a quick alternative, the component names can just be prefaced with a category word such as media, laminate, substrate, hdwr, glass, lumber, etc. which will work so long as the list is relatively short. (~100)

Many shops have ~1000 components however many of those are the same component but of a different size, color, finish etc., which require their listing due to purchasing, cost accounting, calculations, etc.
 

ColorCrest

New Member
so I made a simple spreadsheet.
Another good example of good thing going.

I first rearranged the fields from the Google Sheets into a vertical list and added a new field for a job / line item identifier. I then transposed the rows to columns and colored the sections, modified some terms and changed labor to use hours (decimal form) instead of minutes. The tool is now scaleable as much as spreadsheets afford. The format is also ready to export into any other spreadsheet or database. I also merged the component items into a single spreadsheet / table and added a preface category column for organization.

grabAsLineItem.jpg
 

ColorCrest

New Member
Colorcrest, do you use any database software and if so how do you like it and what would you change?
I use FileMaker as a so-called "rapid database management system" where I'v created my own solution over the years and I try to keep up with technology such as integrations and mobile capability and data security.
 

Evan Gillette

New Member
In this version of the Excel sheet, does anyone know how you would bring up a specific dropdown item/description list based upon the category choice, which I assume would also be a dropdown list. For example, if Media is chosen in the first column then the Description column would offer a choice of only vinyl materials and not the entire list of everything. If Laminate is chosen as the Category then only laminates will come up on the list for the second column.
You could split them out into sub tables, but it gets pretty messy pretty quick, and way more tedious to update. Sub headings or color coding seems to be the best for finding things quickly
 

Evan Gillette

New Member
Actually, I did a quick proof of concept based on your request. It needs a lot more polishing and there are some weird excel syntax things with spaces so I just added an underscore. Anyone with a decent coding background (not me) could probably fix it up in a hurry. I just followed the concept from this example:

Here you go, have fun (if you consider it that)
 

ColorCrest

New Member
Actually, I did a quick proof of concept based on your request.
To be clear, I'm only offering suggestions to those wanting to account for costing of their sign products using spreadsheets, per the thread title. (The thread quickly involved pricing too, as much as anything else.)
 

Stacey K

I like making signs
Actually, I did a quick proof of concept based on your request. It needs a lot more polishing and there are some weird excel syntax things with spaces so I just added an underscore. Anyone with a decent coding background (not me) could probably fix it up in a hurry. I just followed the concept from this example:

Here you go, have fun (if you consider it that)
Yes, I do consider spreadsheets fun! Thanks for the updates guys, always looking to update my spreadsheets!
 
Top