Cost Estimating Calculator with Multiple Quantities

[pmantey13]

I've built a cost estimating calculator that allows me to input several variables for a job and it will calculate my time and materials expenses to get the job done. One of the main variables I need to provide is a quantity. As you can imagine, most customers ask for quotes based upon several quantities. So for now, I have a separate pricing table to the side that lists the quantities I want to quote and their costs (generated from the calculator). I add a markup and the table calculates a price for each quantity. But I need to manually enter each of the quantities (sometimes dozens) into the calculator one-at-a-time and manually post the costs into the pricing table.

I'd like to enter all my quantities in the pricing table and have each quantity automatically run through the calculator and plug a cost into the appropriate column/row in the pricing table. Would that require a pivot table? A macro? I don't really know how to approach that challenge, but I feel like I can figure it out with a little direction.

 

[dreid1011]

Welcome to the forum. Could you post some sample data and the formula(s) you are currently using to calculate the cost?

 

[pmantey13]

PC Quote.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Job	Size	Paper	Color	Variables
3		6 x 9	6 x 9	100 Gloss Cover	4/4	Vendor to supply processed Print Ready files, postal tray information, etc
4
5		Roll Change feet	14272	ProStream $ per hour	$200.00
6		Web width	20	MBO $ per hour	$125.00
7		Web length	9.25
8		Number up	3		QTY		6x9 Postcards
9		Paper price per LB	0.89		100,000		100# Gloss Cover
10		Prostream $ per min	$3.33				Prints 4/4
11		MBO $ per min	$2.08				Vendor to supply processed Print Ready files, postal tray information, etc
12		Per peice/Paper lbs needed	0.0243	Paper Weight Calculator			Vendor to drop in mail
13		Sheet width	6.67
14
15		Quantities		Per hour			QTY	COST Per Qty	COST Per Piece	Markup %	PRICE Per Qty	PRICE Per Piece
16		1	Shipping	35.00	$35.00		2,500	$380.39	0.1522	20	$456.47	0.1826
17		0	File processing	45.00	$0.00		5,000	$536.86	0.1074	20	$644.23	0.1288
18							10,000	$849.79	0.0850	20	$1,019.75	0.1020
19				LBS needed			15,000	$1,162.72	0.0775	20	$1,395.26	0.0930
20		100,000	Paper	2430.00	$2,162.70		20,000	$1,475.65	0.0738	20	$1,770.78	0.0885
21		2	Roll change	28.46	$50.66		25,000	$1,788.58	0.0715	25	$2,235.73	0.0894
22							30,000	$2,101.51	0.0701	25	$2,626.89	0.0876
23				FPM			40,000	$2,727.37	0.0682	25	$3,409.21	0.0852
24		100,000	Digital Press	200	$428.24		50,000	$3,353.23	0.0671	25	$4,191.54	0.0838
25		0.5	Make ready		$50.00		60,000	$4,018.01	0.0670	25	$5,022.51	0.0837
26							70,000	$4,643.87	0.0663	25	$5,804.84	0.0829
27		.		FPM			80,000	$5,269.73	0.0659	25	$6,587.16	0.0823
28		100,000	MBO	200	$267.65		90,000	$5,895.59	0.0655	25	$7,369.49	0.0819
29		0.5	Make ready		$25.00		100,000	$6,521.45	0.0652	25	$8,151.81	0.0815
30
31				LBS ink per piece
32		100,000	Ink	0.0340	$3,400.00
33		800	Waste	0.0340	$27.20
34
35		0.5	Handling	150.00	$75.00
36
37
38		100,000	Total Qty	Cost	$6,521.45
Post card mail piece

Cell Formulas
Range	Formula
E3	E3	="4/4"
C10:C11	C10	=E5/60
C13	C13	=C6/C8
K16:K29	K16	=(1+(J16/100))*H16
L16:L29	L16	=(1+(J16/100))*I16
E16:E17,E35,E33	E16	=D16*B16
D20	D20	=B20*C12
B20	B20	=E9
B21	B21	=ROUNDUP(B20/C8*C7/12/C5,0)
E20	E20	=D20*C9
E21	E21	=B21*D21*C9
B24	B24	=E9
E24	E24	=(B24/C8)*(C7/12)/D24*C10
E25	E25	=B25*100
B28	B28	=E9
E28	E28	=(B28/C8)*C7/12/D28*C11
E29	E29	=B29*50
I16:I29	I16	=H16/G16
E32	E32	=B32*D32
B32	B32	=E9
B33	B33	=400*B21
D33	D33	=D32
B38	B38	=E9
E38	E38	=E16+E17+E20+E24+E28+E21+E25+E29+E33+E32+E35

 

[pmantey13]

One of my staff started this sheet and I've taken it to this point, but I recognize it doesn't really flow intuitively yet. Basically the user fills in the yellow boxes and the calculator does the work. The table on the right is the pricing portion. E9 should be yellow. That's where I manually enter the quantities from the pricing table. E38 is where the total cost comes out. I plug that number into the H column and get a price to quote the customer. Would love to have the various quantities auto-calculate and auto-populate the cost fields.

 

[dreid1011]

See the formula in col H, you can drag that down your pricing table and it should calculate the costs per qty.

Book1
	G	H	I
15	QTY		COST Per Qty
16	2500	380.39	380.39
17	5000	536.86	536.86
18	10000	849.79	849.79
19	15000	1162.72	1162.72
20	20000	1475.65	1475.65
21	25000	1788.58	1788.58
22	30000	2101.51	2101.51
23	40000	2727.37	2727.37
24	50000	3353.23	3353.23
25	60000	4018.01	4018.01
26	70000	4643.87	4643.87
27	80000	5269.73	5269.73
28	90000	5895.59	5895.59
29	100000	6521.45	6521.45
Sheet1

Cell Formulas
Range	Formula
H16:H29	H16	=SUM($D$16*$B$16,$D$17*$B$17,($G16*$C$12)*$C$9,ROUNDUP($G16/$C$8*$C$7/12/$C$5,0)*$D$21*$C$9,($G16/$C$8)*($C$7/12)/$D$24*$C$10,$B$25*100,($G16/$C$8)*$C$7/12/$D$28*$C$11,$B$29*50,$G16*$D$32,$D$33*400*ROUNDUP($G16/$C$8*$C$7/12/$C$5,0),$D$35*$B$35)

 

[dreid1011]

And a slightly more condensed version:

=LET(g,$G16,f,$C$5,s,$C$7,e,$C$8,n,$C$9,r,ROUNDUP(g/e*s/12/f,0),SUM($D$16*$B$16,$D$17*$B$17,(g*$C$12)*n,r*$D$21*n,(g/e)*(s/12)/$D$24*$C$10,$B$25*100,(g/e)*s/12/$D$28*$C$11,$B$29*50,g*$D$32,$D$33*400*r,$D$35*$B$35))

And a note for either formula, you don't need to fill in E9 for the formula to work.

 

[pmantey13]

Wow! Now THAT'S a formula! I see what you're doing, though. The cost column (H) just runs all the calculations, rather than trying to plug different values into E9 and extract the results from E38. The result is a longer formula, but a far simpler approach. I haven't studied the LET command enough to follow the abbreviated formula, but I get that it works the same way and I appreciate the result. Thank you so much for your help on this!

 

[dreid1011]

Wow! Now THAT'S a formula! I see what you're doing, though. The cost column (H) just runs all the calculations, rather than trying to plug different values into E9 and extract the results from E38. The result is a longer formula, but a far simpler approach. I haven't studied the LET command enough to follow the abbreviated formula, but I get that it works the same way and I appreciate the result. Thank you so much for your help on this!

You're welcome, and happy to help.