MS Access for Cost Estimation Tool?

[sud2017]

Hi All,

I work for a company where we rely on spreadsheets for cost estimation of different types of opportunities that we quote for. This is something which takes a lot of time of President. I was thinking if MS Access can be a good tool to develop a database with a main page where all the data can be stored with functions for cost estimations for different types of projects we quote for and then end user can simply perform the estimation. Main fields of the estimation are below:

Item Description (item 1, 2, 3, .....)
Qty
Unit Price
Extended Price
Mark Up
Sale Price



Would appreciate your comment. I have beginners level proficiency in Access but can learn more.

-Sud

[ranman256]

Access IS the tool to use.
Note: Extended Price would not be a field in a table. It would be a calculated field in a query ,
Extended Price: Qty*[unit price]

you would also need fields for the tQuotes table (along with those above):
QuoteID
CustID
QuoteDate

[Preston]

That would be a perfect application for Access. Make sure you learn something about building the right data structure.

Unit price would be in a table. User would enter quantity and the form would calculate extended price. Same with Markup. When you save the estimate to an Estimates table, you wouldn't need to save Extension or Sale price, you would save Qty, Unit Price, and Markup, and you could then calculate Sale Price etc. on demand.

[sud2017]

Thanks guys. I am planning to start with a small database which I want to show to my manager and if he likes then I would work on a complete database for different types of opportunities we quote for.

I have created a simple layout of what I am looking for in the image below, with some tables and queries and would like to build with some test data. Can you please review and advise.

[Preston]

If you have specific markup levels, you might want a Markups table as well. This would allow you to include a dropdown for the Markup % to be used and avoid human error (fatfinger) The $ Markup would then be calculated as total cost times the rate selected.

If markup can be completely arbitrary, that table would be pointless. You would still want user to enter a markup percent so the form can calculate the $ amount.

[Preston]

Also, would it be good to apply markup on a line item basis, so that you can include various product margins, such as loss leaders, etc, in the estimate?

[sud2017]

Very valid comment. In some quote we assign mark-up on individual line items. In other project we only assign mark-up on total cost. I am thinking to add an option in Main-Page to allow users to select if they want 'line-item wise mark-up' or 'mark-up on total cost'. if they select 'line-item wise Mark-Up', then there will be 2 columns added after Unit cost in the estimation sheet, which will be - Mark-Up% and Sale Price. but I definitely don't want specific pre-assigned mark-up levels because our mark-up changes a lot. Now, I want to understand if I still need to create a Mark-Up table to achieve what I want here? How can I achieve it?

I can send an updated layout if above is unclear. Thanks.

[Preston]

If your mark-up % aren't a discrete and finite list, then you don't need the table.

I would suggest that with a little coding, you can have a textbox where user enters a quote-wide mark-up, and can then override that markup on one or more individual line. So they could put 80%, but change line 3 to 50% and line 5 to 25%.

[sud2017]

That sounds like a good idea. I'm still a newbie to Access but I'd like to have that functionality of customizing the margins for different line items.

For now, can you please advise how I can have a 'mark-up' label in my estimation form so that it can calculate the mark up $ from the % entered in that form. How is that done in Access? So far, I am only educated on how to create a form by importing fields from a table. Thanks!

[Preston]

Sure. On your estimation main form, add an unbound textbox. You can name it txtMarkupPerc. Format it for %.
Then in the appropriate place on the main form below the detail subform, place another textbox. You can call that txtMarkupDoll. In that textbox's source, use the formula =txtMarkup*txtSumCost. txtSumCost being the textbox that sums up all the line item cost extensions in your detail subform. Everything from Total Cost down on your mockup is going to be the main form, while the detail lines 1 through ..... will be the subform.

[sud2017]

Thanks so much. I will do it when I reach to that stage. Can you help me with the type of relationships should be between 'Unit Cost table' and 'Items Table' from the below image? Should it be 1 to 1?

[Preston]

It looks like Doors is the table containing your item detail. You wouldn't physically relate Cost table to that table. You would look up the cost at the time of estimate and write that cost into a Cost field in the Doors table as a permanent record of the cost at the time of estimate. You would also want Qty and Markup in your Item record if you want Item level markup. If you want Opportunity level markup, then you would store the markup in the Opportunity table.

So 6 months down the road, your cost for an item has changed, but the original estimate still shows the original cost.

[sud2017]

Thanks very much again. I am now in the Main Page where I have created a drop down list which will direct me to the respective 'cost estimation form' based on the type of opportunity I select. But I am struggling with how I can link the drop down item with its respective form. For example, I want to be able to be redirected to the 'DoorsCostEstimationForm' when I select 'DoorsOpportunity' from the drop down list in my main form. Would appreciate for any directions.

[Preston]

Just so we all understand terminology, what do you mean by dropdown list? Do you mean that to be like a menu from which the user can choose what she wants to do?

When I see dropdown, I visualize a combo box that allows you to select from a preset list of values, and the choice you make ends up either populating a field in a table or providing a value to a variable for later use.

If you mean it to be a menu selection, I'd suggest using command buttons for easier programming.

[sud2017]

Thanks and sorry about the confusion on my terminology. Yes, I am referring to the combo box when I said drop down. I am visualizing a combo box to select from a preset list of values, and the choice I make should end up populating a FORM.

The reason why I DO NOT want a command button is because I have so many forms and i'll end up having so many command buttons. I hope I am clear, if not then I will send a sketch.