Costing Spreadsheet

[ColorCrest]

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]

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]

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]

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]

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]

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.

 

[Evan Gillette]

Colorcrest, do you use any database software and if so how do you like it and what would you change?

 

[ColorCrest]

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]

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]

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:

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

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

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

 

[ColorCrest]

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]

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:

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

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

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

Yes, I do consider spreadsheets fun! Thanks for the updates guys, always looking to update my spreadsheets!

 

[JamesLam]

The members of this community never cease to amaze me. Thanks for all of the great ideas and insights that you have shared, this is so cool and extremely helpful.

 

[Stacey K]

I used the above examples and added to my current spreadsheet. I copy/paste the tab, rename it, fill it out when the job comes in with as much as I can, print it, make notes, track time on the paper copy and when the job is done I enter the rest into the sheet and hide the tab once the job is done, and toss the paper. It works quite well for estimating also. For the sample hourly rate is set at $60 and mark-up at 100% for easy calculating.


It looks like a blank sheet if you don't scroll up and left...not sure why it saved weird.

Sample Spreadsheet.xlsx

Microsoft Excel Workbook

1drv.ms

 

[GB2]

I'm just seeing a totally blank sheet when I click your link....nevermind.....I guess I should follow directions and scroll up and left....thanks!

 

[ColorCrest]

Stacey K,

In your linked sample sheet, on the far right side, you have a box “Add Extra Below” with a formula to add a variable currently at 30% to the total of the worksheet. The example shows $193.59 + 30% = $251.66.

Be aware and be very careful about using your formula of a multiplier here and not the usual divisor (of a very different value, by the way) to get the results you expect. Maybe research the internet or ask a good bookkeeper to learn “why 30% is not 30%.”

I feel it is important to point out markups vs margins and especially since I believe this thread has had far more views than when it was originally active.

 

[GB2]

Stacey I really like what you did with this sheet. I've been trying to do the same thing from scratch since this thread started but I'm no Excel expert and I've been running into some road blocks but you are leaps and bounds ahead of me. I really ran into problems when trying to do Dependent Drop Down Lists, they are just too limiting. Just curious, is this something you just came up with now in response to this thread or have you actually been using something like this already? I have some more specific detail questions, but if you just put this together for the discussion then that would explain a lot of small irregularities such as why your dropdown list in A64-69 do not all reference the same data. Also, is there a reason you put all the data in one column rather than a column for each and why the data groups aren't formatted as tables?

Also, as some of us here know, this type of thing works to a certain level in Excel but the ideal way to construct this is in FileMaker Pro. I wish we had the same type of thread going on how to formulate FileMaker Pro to our best advantage.


Note:
Once again, I re-read your post to find the answer was there and that you actually do use such a sheet....Sheesh....I have to learn how to be less hasty!

 

[netsol]

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.

So, you're not an access developer kit guy?

FileMaker probably makes a lot of sense, I am much less comfortable in O365 than I was in the older versions

 

[Stacey K]

Stacey I really like what you did with this sheet. I've been trying to do the same thing from scratch since this thread started but I'm no Excel expert and I've been running into some road blocks but you are leaps and bounds ahead of me. I really ran into problems when trying to do Dependent Drop Down Lists, they are just too limiting. Just curious, is this something you just came up with now in response to this thread or have you actually been using something like this already? I have some more specific detail questions, but if you just put this together for the discussion then that would explain a lot of small irregularities such as why your dropdown list in A64-69 do not all reference the same data. Also, is there a reason you put all the data in one column rather than a column for each and why the data groups aren't formatted as tables?

Also, as some of us here know, this type of thing works to a certain level in Excel but the ideal way to construct this is in FileMaker Pro. I wish we had the same type of thread going on how to formulate FileMaker Pro to our best advantage.


Note:
Once again, I re-read your post to find the answer was there and that you actually do use such a sheet....Sheesh....I have to learn how to be less hasty!

I had actually created a much simpler version of this several months back after I think RJSigns had posted that his wife created a nice spreadsheet for him. It included just a couple drop downs, but no VLookups. Once I saw this post, I began adding drop downs and the VLookups as an added convenience. The data sheet has everything in one column so I can add and delete items without messing up any columns to the right of it - and once everything is in there I shouldn't have to reference the data sheet very often. You can definitely add tables to the data sheet, I simply sorted the vinyl and left the rest of it. As with most of the spreadsheets I've created in the past for various projects, I'm always making changes, adding and removing certain things as the need arises.

Yes, many people use FilePro but I've never used it. I did try it once but felt that I could whip up a spreadsheet quicker. I also downloaded free versions of all sorts of sign making software and keep resorting back to my spreadsheet. As a one person shop, I try to keep my overhead extremely low and if it's something I can do myself, I usually do. I also use Wave Accounting which is free, and of course so is the spreadsheet. If I were become larger then I would switch over to something more robust. For what I'm doing, this system works quite well for me. I have a separate spreadsheet for apparel orders also.

For the drop downs, if they are limiting, you could maybe look at adding macros? I used to use them a lot in my old job but I'm pretty rusty so I didn't bother getting too fancy. Pivot tables could probably be used also for certain things.

 

[Stacey K]

Stacey K,

In your linked sample sheet, on the far right side, you have a box “Add Extra Below” with a formula to add a variable currently at 30% to the total of the worksheet. The example shows $193.59 + 30% = $251.66.

Be aware and be very careful about using your formula of a multiplier here and not the usual divisor (of a very different value, by the way) to get the results you expect. Maybe research the internet or ask a good bookkeeper to learn “why 30% is not 30%.”

I feel it is important to point out markups vs margins and especially since I believe this thread has had far more views than when it was originally active.

That is a great point! The original intent was to add something extra for rush jobs. It's a little unfinished and I should look up some of the posts on markup vs. margin and maybe create a couple boxes that are more specific. Thanks for pointing that out...I should make note of that in the sample for others who don't see this.