I am trying to make a Excel Spreedsheet that i can use to quote my clients with
I need the spreadsheet to calculate the expense of the
Labour_Type_1 - Charged 1 per item type, regardless or quantity
Labour_Type_2 - Charged 1 per item type, regardless or quantity
Labour_Type_3 - Charged by quantity of each item type
Labour needs to be calculated on a sliding scale. The large the item the less I need to charge for labour.
To work out the size of the item its in mm,
Here is the formula
IF(ISERROR(MAX(TRUNC(Max_Length/($D8))*TRUNC(Max_Width/($E8)),
TRUNC(Max_Length/($E8))*TRUNC(Max_Width/($D8)))),1,
MAX(TRUNC(Max_Length/($D8))*TRUNC(Max_Width/($E8)),
TRUNC(Max_Length/($E8))*TRUNC(Max_Width/($D8))))
The formula is checking how many can fit onto 1 sheet
$D8 is the length of the graphic designs,
$E8 is the Width of the graphic design.
C8 is the Quantity of the order.
Labour_Per_Min = Dollar Per Hour / 60
Rounding = 2
B8 = Type of Product being purchased
Expenses_Per_Order = Total Expenses and materials to complete order.
Sale_markup = the profit formula that is still to be worked out
My full formula is looking like this
=FLOOR((ROUND((Labour_Type_1+Labour_Type_2+(C8* Labour_Type_3))*Labour_Per_Min,Rounding))
+(ROUND((VLOOKUP(B8,Product_Worksheet,4,FALSE))*IF(ISERROR(MAX(TRUNC(Max_Length/($D8))*TRUNC(Max_Width/($E8)),
TRUNC(Max_Length/($E8))*TRUNC(Max_Width/($D8)))),0,C8/
MAX(TRUNC(Max_Length/($D8))*TRUNC(Max_Width/($E8)),
TRUNC(Max_Length/($E8))*TRUNC(Max_Width/($D8)))),Rounding))
+(ROUND(SUM(Expenses_Per_Order),Rounding))
+(ROUND(SUM(K28)*Sale_Markup,Rounding)),Cents_Rounding)
I hope this is enough information
Can anyone give me some insite to this.
I need to workout how to formulate the sliding scale for labour and get my formula to work.
I really appreciate your time
Thank you
Bookmarks