What formula can I use for this?

[lgarcia2509]

I’m working with a pricebook that has a row of pay rates ($20-$45) and includes columns of taxes under each pay rate all sectioned out by a specific profit margin percentage. I am trying to create a "rate card” on a separate sheet where you can type in any “pay rate” and it will calculate the value including taxes based off the percentage selected in a dropdown list (I’ve created on the rate card).

What formula can I use for this?

 

[awoohaw]

if at all possible can you post a mini worksheet using the xl2bb add in (I'mnot sure if you can get it for mac or not).
There is a link to it below.

Assuming you have a pay rate of $24, what values are needed for "Regular Bill Rate", "O/T Bill Rate", "O/T Holiday Bill Rate"? You don't have exact row names to match your column name.

 

[lgarcia2509]

if at all possible can you post a mini worksheet using the xl2bb add in (I'mnot sure if you can get it for mac or not).
There is a link to it below.

Assuming you have a pay rate of $24, what values are needed for "Regular Bill Rate", "O/T Bill Rate", "O/T Holiday Bill Rate"? You don't have exact row names to match your column name.

CA Pricing - Profit Margin Project.xlsx
	A	B
1	Base Pay (Hourly)	$20.00
2
3	Payroll Taxes (FICA, FUTA, SUTA)	$2.14
4	Worker's Compensation	$0.53
5	Health and Dental Insurance Benefits	$1.17
6
7	Total Base Pay, Taxes, and Benefits	$23.84
8
9	Training (State mandated, Client required)	$0.17
10	Uniform Expense	$0.13
11	Equipment	$0.00
12	Background Checks, Drug Screening, etc.	$0.04
13	General Liability Insurance	$0.15
14	Paid Time Off	$0.74
15	Government Mandated COVID/Emergency Health PTO	$0.00
16
17	Hiring and Related Costs	$1.22
18
19	Employee Incentives	$0.11
20
21	Corporate Support ($)	$6.74
22
23	Total Supplier Managed Variable Costs	$6.85
24
25
26	Total Cost	$31.92
67
68	Overtime Rate	$44.78
69	Holiday Rate	$44.78
70	Holiday Overtime Rate	$56.31
71
72	Profit Margin	5.0%
73
74	Profit Margin ($)	$1.68
75	Markup Factor	1.053
76	Markup	68.0%
77	Bill Rate	$33.60
78
79	Overtime Rate	$45.25
80	Holiday Rate	$45.25
81	Holiday Overtime Rate	$56.90
Pricing Worksheet

Cell Formulas
Range	Formula
B3	B3	=B1*'Rate Components'!$C$4
B4	B4	='Rate Components'!$C$32*B1
B5	B5	=('Rate Components'!$C$10)
B7	B7	=SUM(B1:B6)
B9	B9	='Rate Components'!$C$9*(B1+B3)
B10	B10	='Rate Components'!$C$6
B11	B11	='Rate Components'!$C$7
B12	B12	='Rate Components'!$C$8
B13	B13	='Rate Components'!$C$5
B14	B14	=(((B1+B3)*(1/30)))
B15	B15	=+(((B1+B3)*('Rate Components'!$P$32/2080)))
B17	B17	=SUM(B9:B16)
B19	B19	='Rate Components'!$C$11
B21	B21	='Rate Components'!$C$12
B23	B23	=SUM(B19:B21)
B26	B26	=SUM(B7,B17,B23)
B68,B79	B68	=ROUND((SUM(B$1:B$3)*1.5*B64)+(SUM(B$4:B$5,B$17,B$23)*B64),2)
B69,B80	B69	=ROUND((SUM(B$1:B$3)*1.5*B64)+(SUM(B$4:B$5,B$17,B$23)*B64),2)
B70,B81	B70	=ROUND((SUM(B$1:B$3)*2*B64)+(SUM(B$4:B$5,B$17,B$23)*B64),2)
B72	B72	=ROUND((B77-B$26)/B77,4)
B74	B74	=B77-B$26
B76	B76	=(B77-B$1)/B$1
B77	B77	=ROUND((B$26*B75),2)

 

[lgarcia2509]

I was able to upload the mini sheet - it’s not the whole sheet, but it has the same info.

Assuming we’re working on the pay rate of $20 based off a 5% profit margin, I would like to populate B77(reg pay rate), B79 (OT rate), B80(holiday), and B81 (holiday OT)

 

[lgarcia2509]

CA Pricing - Profit Margin Project.xlsx
	A	B	C	D	E	F
1	5%	PAY RATE	REGULAR BILL RATE	HOLIDAY / OT BILL RATE	DOUBLE TIME / HOLIDAY OT BILL RATE
2		$20.00
3		$21.00
4		$22.00
5		$23.00
6		$24.00
7		$25.00
8		$26.00
9		$27.00
10		$28.00
11		$29.00
12		$30.00
13		$31.00
14		$32.00
15		$33.00
16		$34.00
17		$35.00
18		$40.00
19		$45.00
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$A$36:$A$45

 

[awoohaw]

Okay, you have links to other workbooks or worksheets so I'm getting ref errors.
But also you have the same rows headers:

2023-02-27_SimplyChecking...4607.csv
	A
68	Overtime Rate
69	Holiday Rate
70	Holiday Overtime Rate
71
72	Profit Margin
73
74	Profit Margin ($)
75	Markup Factor
76	Markup
77	Bill Rate
78
79	Overtime Rate
80	Holiday Rate
81	Holiday Overtime Rate
Sheet2

If you want to hit the first of those rows, it isn't an issue, but if you want the 2nd, then you may need to rename/alter their text.

 

[awoohaw]

your margin rates are all 5%, do you have more columns (same layout on same rows) with the different margin rates?

 

[lgarcia2509]

Yes, the margin rates go from 1-15%. I am pasting another mini sheet, hope this works. I mistakenly copied too much data the first time.

CA Pricing - Profit Margin Project.xlsx
	A	B
1	Base Pay (Hourly)	$20.00
2
3	Payroll Taxes (FICA, FUTA, SUTA)	$2.14
4	Worker's Compensation	$0.53
5	Health and Dental Insurance Benefits	$1.17
6
7	Total Base Pay, Taxes, and Benefits	$23.84
8
9	Training (State mandated, Client required)	$0.17
10	Uniform Expense	$0.13
11	Equipment	$0.00
12	Background Checks, Drug Screening, etc.	$0.04
13	General Liability Insurance	$0.15
14	Paid Time Off	$0.74
15	Government Mandated COVID/Emergency Health PTO	$0.00
16
17	Hiring and Related Costs	$1.22
18
19	Employee Incentives	$0.11
20
21	Corporate Support ($)	$6.74
22
23	Total Supplier Managed Variable Costs	$6.85
24
25
26	Total Cost	$31.92
27
72	Profit Margin	5.0%
73
74	Profit Margin ($)	$1.68
75	Markup Factor	1.053
76	Markup	68.0%
77	Bill Rate	$33.60
78
79	Overtime Rate	$45.25
80	Holiday Rate	$45.25
81	Holiday Overtime Rate	$56.90
82
Pricing Worksheet

Cell Formulas
Range	Formula
B3	B3	=B1*'Rate Components'!$C$4
B4	B4	='Rate Components'!$C$32*B1
B5	B5	=('Rate Components'!$C$10)
B7	B7	=SUM(B1:B6)
B9	B9	='Rate Components'!$C$9*(B1+B3)
B10	B10	='Rate Components'!$C$6
B11	B11	='Rate Components'!$C$7
B12	B12	='Rate Components'!$C$8
B13	B13	='Rate Components'!$C$5
B14	B14	=(((B1+B3)*(1/30)))
B15	B15	=+(((B1+B3)*('Rate Components'!$P$32/2080)))
B17	B17	=SUM(B9:B16)
B19	B19	='Rate Components'!$C$11
B21	B21	='Rate Components'!$C$12
B23	B23	=SUM(B19:B21)
B26	B26	=SUM(B7,B17,B23)
B72	B72	=ROUND((B77-B$26)/B77,4)
B74	B74	=B77-B$26
B76	B76	=(B77-B$1)/B$1
B77	B77	=ROUND((B$26*B75),2)
B79	B79	=ROUND((SUM(B$1:B$3)*1.5*B75)+(SUM(B$4:B$5,B$17,B$23)*B75),2)
B80	B80	=ROUND((SUM(B$1:B$3)*1.5*B75)+(SUM(B$4:B$5,B$17,B$23)*B75),2)
B81	B81	=ROUND((SUM(B$1:B$3)*2*B75)+(SUM(B$4:B$5,B$17,B$23)*B75),2)

 

[awoohaw]

Hiding the rows will not make the lookup ignore the text in cells B68, B69, B70.

I'm also sill looking for my questions for what values do you want mapped into the columns on sheet1. Also, what does the 5% mean in cell A1 of Sheet1? Is that part of hte lookup?

 

[awoohaw]

Are these the definitions for mapping?
Do you want to use the values in rows (68,69,70) or (79,80,81)
is the Profit Margin % that you seek in Column B (1st row in the mini workbook) of Sheet1? And is this the drop down percentage you mention? Is it to be compared to the Profit Margin Row?

REGULAR BILL RATE	HOLIDAY / OT BILL RATE	DOUBLE TIME / HOLIDAY OT BILL RATE
Overtime Rate	Holiday Rate	Holiday Overtime Rate