C
craigwojo
Hi everyone,
Instead of posting a little bit of assist here and a little there, I’
posting 90% of my spreadsheet that I work with to formulate quotes for
my business.
Just a note for all who assist us that are learning Excel, I (and I
think many others) want to THANK YOU Very Much. I appreciate the time
you take to figure things out for me and many others. I personally
appreciate it greatly.
I’ll stop the brown-nosing and get to my spreadsheet. What I need is
any of you out there that could figure out some cells that I need to
have a formula for, and if you think I need anything to add, all of
your suggestions are welcome.
A little background, I own a small business just starting out doing
labels for machinery in the automotive industry and trying to compete
with the big guys I need to have a good, accurate spreadsheet for my
quotes. I did some costs and timings on my production of some labels I
produce and this is what I have so far.
Column A: Quantity (input)
Column B: Height (input)
Column C: Length (input)
Column D: Square Inches per Tag (formula) =B2*C2
Column E: Total tags 16”x24” Sheet of Material (formula)
=IF(OR(B2="",C2=""),"",FLOOR(16/B2,1)*FLOOR(24/C2,1))
Column F: # of 16x24 Sheets for Order (formula) =ROUNDUP(A2/E2,0)
Column G: # of Full Sheets 24x48 for Order
Note: this is a formula I need to order full sheets from the
manufacturer. When I order a sheet of material I need to buy the full
sheet, which is 24x48. My machine only holds 16x24 sheets, I have them
cut it in 3rds. So what I have for this cell is if Column F is saying
that if I need 2.45 shts of material for the job, then I need to this
cell to say “1” (only 1 full sheet is needed to be ordered). If Column
F says 3.5 (16x24) shts needed for the job then this cell needs to say
that I need to order “2” full sheets (24x48), and so on.
(formula) =ROUNDUP(F2/3,0)
Column H: Unused Sheets from Order on Job 16x24 (formula)
=(G2*3)-F2
Column I: Total Tags Left/Right on Sheet (formula) =INT(24/C2)
Column J: Total Tags Top/Bottom on Sheet (formula) =INT(16/B2)
Column K: Customers Cost per Square Inch (formula)
=IF(D2<4.5,1.25,IF(D2<100,1.225,IF(D2<200,0.2,1.15)))
Column L: Profit % on Tag (Material) (formula) =ROUNDUP(Q2/P2,2)
Column M: Customer Tag Cost per Sheet 16x24 (formula) =E2*Q2
Column N: My Profit per Sheet (formula) =M2-102.0
Column O: My Material Order Cost Company 1 (formula) =G2*306
Column P: My Cost per Tag (Material) (formula)
=ROUNDUP(102/E2,2)
Column Q: Customer Price per Tag (formula) =ROUNDDOWN(K2*D2,2)
Column R: Total Order Cost for Customer (formula) =A2*Q2
Column S: My Profit in $ (formula) =R2-O2
-Then I wrap the spreadsheet to ROW 11 for timing factors. So please
note that there will be duplicate column letters from the top part of
the spreadsheet on this part of it.-
Column A: Quantity (formula) =A2*1
Column B: Height (formula) =B2*1
Column C: Length (formula) =C2*1
Note: The above 3 Columns I just wanted to copy from the top area of
the spreadsheet. I didn’t know if there was a way to carry over the
information from one cell to another.
Column D: Lines of Text per Tag (input)
Column E: Text Size (input)
Column F: Number of Tie Wraps (input)
Column G: Number of Side Holes (input)
Column H: TIME-Tie Wrap Slots per Sheet (formula-time)
Note: Tie Wrap Slots are a series of 4 slots cut through the tags for a
tie wrap to be inserted. They are all the same size, on every tag. On
a timing I did, the figure was for 60 tags it took 00:08:31 hh:mm:ss.
What I need here is a formula to take this information and use it with
this cell. If there is only 9 tie wrap slot then it should tell the
time for this action.
Column I: TIME-Tie Wrap Slots per Job (formula-time) I guess, use
the answer to Column H: (second row) and…
Column J: TIME-Side Holes per Sheet (formula-time) Same as Column
H: but for the holes I timed 00:05:10 hh:mm:ss for 60 tags.
Column K: TIME-Side Holes per Job (formula-time) Same as Column
I: but for holes.
Here is where I pull my hair out.
Column L: TIME-Perimeter Cut for the Tags per Sheet.
(formula-time) Where do I even start? OK, Each tag gets cut out and it
took 00:02:41 hh:mm:ss to cut out 60 tags. Each tag is 1.5” x 3.5”
(HxW). I need to know, according to how many tags and what ever size
they are, is the time for the perimeter cuts.
Column M: TIME-Perimeter Cut for the Tags per Job (formula-time)
Column N: TIME-Text per Sheet (formula-time) OK, figured on it
takes 00:05:54 hh:mm:ss: to make 1” high text by 24” in length. Text
size is based on 96 points equals 1” (inch) in height. This figure
should be based on Column D: and Column E:
Column O: TIME-Text per Job (formula-time)
Well, that’s about it. Anyone, I mean anyone that tries to assist me
in this will be appreciated. I’ve worked on this for a month and with
some help of people in this forum I’ve done it this far with the
spreadsheet, but I’m stuck with mostly the time factor in the formulas.
I keep getting this AM/PM stuff when all I want is basic time, not time
of the day. Thank you again.
Thank you,
Craig
Instead of posting a little bit of assist here and a little there, I’
posting 90% of my spreadsheet that I work with to formulate quotes for
my business.
Just a note for all who assist us that are learning Excel, I (and I
think many others) want to THANK YOU Very Much. I appreciate the time
you take to figure things out for me and many others. I personally
appreciate it greatly.
I’ll stop the brown-nosing and get to my spreadsheet. What I need is
any of you out there that could figure out some cells that I need to
have a formula for, and if you think I need anything to add, all of
your suggestions are welcome.
A little background, I own a small business just starting out doing
labels for machinery in the automotive industry and trying to compete
with the big guys I need to have a good, accurate spreadsheet for my
quotes. I did some costs and timings on my production of some labels I
produce and this is what I have so far.
Column A: Quantity (input)
Column B: Height (input)
Column C: Length (input)
Column D: Square Inches per Tag (formula) =B2*C2
Column E: Total tags 16”x24” Sheet of Material (formula)
=IF(OR(B2="",C2=""),"",FLOOR(16/B2,1)*FLOOR(24/C2,1))
Column F: # of 16x24 Sheets for Order (formula) =ROUNDUP(A2/E2,0)
Column G: # of Full Sheets 24x48 for Order
Note: this is a formula I need to order full sheets from the
manufacturer. When I order a sheet of material I need to buy the full
sheet, which is 24x48. My machine only holds 16x24 sheets, I have them
cut it in 3rds. So what I have for this cell is if Column F is saying
that if I need 2.45 shts of material for the job, then I need to this
cell to say “1” (only 1 full sheet is needed to be ordered). If Column
F says 3.5 (16x24) shts needed for the job then this cell needs to say
that I need to order “2” full sheets (24x48), and so on.
(formula) =ROUNDUP(F2/3,0)
Column H: Unused Sheets from Order on Job 16x24 (formula)
=(G2*3)-F2
Column I: Total Tags Left/Right on Sheet (formula) =INT(24/C2)
Column J: Total Tags Top/Bottom on Sheet (formula) =INT(16/B2)
Column K: Customers Cost per Square Inch (formula)
=IF(D2<4.5,1.25,IF(D2<100,1.225,IF(D2<200,0.2,1.15)))
Column L: Profit % on Tag (Material) (formula) =ROUNDUP(Q2/P2,2)
Column M: Customer Tag Cost per Sheet 16x24 (formula) =E2*Q2
Column N: My Profit per Sheet (formula) =M2-102.0
Column O: My Material Order Cost Company 1 (formula) =G2*306
Column P: My Cost per Tag (Material) (formula)
=ROUNDUP(102/E2,2)
Column Q: Customer Price per Tag (formula) =ROUNDDOWN(K2*D2,2)
Column R: Total Order Cost for Customer (formula) =A2*Q2
Column S: My Profit in $ (formula) =R2-O2
-Then I wrap the spreadsheet to ROW 11 for timing factors. So please
note that there will be duplicate column letters from the top part of
the spreadsheet on this part of it.-
Column A: Quantity (formula) =A2*1
Column B: Height (formula) =B2*1
Column C: Length (formula) =C2*1
Note: The above 3 Columns I just wanted to copy from the top area of
the spreadsheet. I didn’t know if there was a way to carry over the
information from one cell to another.
Column D: Lines of Text per Tag (input)
Column E: Text Size (input)
Column F: Number of Tie Wraps (input)
Column G: Number of Side Holes (input)
Column H: TIME-Tie Wrap Slots per Sheet (formula-time)
Note: Tie Wrap Slots are a series of 4 slots cut through the tags for a
tie wrap to be inserted. They are all the same size, on every tag. On
a timing I did, the figure was for 60 tags it took 00:08:31 hh:mm:ss.
What I need here is a formula to take this information and use it with
this cell. If there is only 9 tie wrap slot then it should tell the
time for this action.
Column I: TIME-Tie Wrap Slots per Job (formula-time) I guess, use
the answer to Column H: (second row) and…
Column J: TIME-Side Holes per Sheet (formula-time) Same as Column
H: but for the holes I timed 00:05:10 hh:mm:ss for 60 tags.
Column K: TIME-Side Holes per Job (formula-time) Same as Column
I: but for holes.
Here is where I pull my hair out.
Column L: TIME-Perimeter Cut for the Tags per Sheet.
(formula-time) Where do I even start? OK, Each tag gets cut out and it
took 00:02:41 hh:mm:ss to cut out 60 tags. Each tag is 1.5” x 3.5”
(HxW). I need to know, according to how many tags and what ever size
they are, is the time for the perimeter cuts.
Column M: TIME-Perimeter Cut for the Tags per Job (formula-time)
Column N: TIME-Text per Sheet (formula-time) OK, figured on it
takes 00:05:54 hh:mm:ss: to make 1” high text by 24” in length. Text
size is based on 96 points equals 1” (inch) in height. This figure
should be based on Column D: and Column E:
Column O: TIME-Text per Job (formula-time)
Well, that’s about it. Anyone, I mean anyone that tries to assist me
in this will be appreciated. I’ve worked on this for a month and with
some help of people in this forum I’ve done it this far with the
spreadsheet, but I’m stuck with mostly the time factor in the formulas.
I keep getting this AM/PM stuff when all I want is basic time, not time
of the day. Thank you again.
Thank you,
Craig