Page Comparison

Setting → Inventory settings → Groups (scroll to bottom of Groups page) 

Job Sheet Parts and Purposes

1. First sheet should be the Data sheet this where the raw Quote/Order data is place starting at column AV and the Data Setting table is that controls the adding of rows to each sheet that requires it when the Quote/ Order lines exceed the template. The template should be designed to fit on one page initially.
2. The Job Sheet is the base form output to progress and Order so this is a production cutting sheet or a Purchase Order for a buy–in product.
3. There is no limit on the number of additional sheets that are used for production.
4. Labels sheet controls the format of the labels to be printed for this product type. This requires the installation of BUZ Labels Windows program to print to a Label Printer.
5. Check Measure sheet is a form that can be output to best suit the Check Measure process with space provided for updated measurements etc.
6. Components sheet is the Bill of Materials or recipe for taking components from stock for inventory control and reordering purposes.

Set up Job Sheets 

Job sheets are used to output processed Orders labels and BOM (Bill of Materials)

They are created per Inventory group 

Job sheets can have multiple tabs;

Data Sheet

The first sheet in the Job Sheet Workbook, should always be the Data sheet - this is where the raw Order data is placed from your processed order.

The Data sheet has a “DataSettings” named area (click here for More information), this sets the areas of your Sheets that have formulas and enables them to Grow there should be NO deductions or other notes within the Growing area. It sets the pages and the amount of pages that you want to print in the PDF.  

NB: If you have a sheet that will never need to grow , such as a calculation sheet the Data settings should be left blank for Right Column and a Zero in the First Row, Last Row and Inc. in PDF columns.

The “Check Measure” Sheet is set as below with False in the Include in PDF column but it will print in PDF when a Check Measure is required. 

Starting at column AV is where BUZ sits the Data from the Order that you are Printing, this information is not saved on the Job Sheet but exported to the template each time. The Customer information sits at AV5 and across and the Ordered Items sit from AV11 and below

Job Sheet

The Job Sheet is the “Production cutting sheet” or a “Purchase Order” for a product it pulls all its information from the Data Tab and you use formulas to get this information to be correct for your deductions or information required.

There is no limit on the number of additional sheets that are used for production and can be edited to suit your individual products. If you are using Generic Group Options the Generic Job Sheet that goes along with this may only need a few tweaks to be correct for you. The more you change the Generic Group Options the more you will have to change the Job Sheet.

NB: if you add in more columns and have to adjust the print area then the scale of the barcode may be affected and it may not scan.

Check Measure Sheet

The Check Measure sheet is a form that can be output to best suit the Check Measure process. You may want to provide space for updated measurements etc, If your Check Measure person has not got access to BUZ themselves.

The Check Measure sheet is available to print or View when the order has been accepted and a Check Measure is required. The Order will be set to a status or “Waiting for Check Measure”

Labels Sheet

The Labels sheet controls the format of the labels to be printed for this product type.

This requires the installation of BUZ Labels Windows program to print to a Label. To get this set up you can find instructions in the Help Area. Labels will print as below, if you need changes to this there are notes in the Help Documentation for you. 

Image RemovedImage Removed

Components Tab

The Components sheet is the Bill of Materials or the recipe for taking components from stock for inventory control and reordering purposes. In this sheet you can add in every part that is used for this product, the waste % your Description and a formula to calculate what is needed from stock when an order is processed.

Image Removed

• Cell C4 down is your formula for fabric 
• Cell D4 down is your formula to calculate your fabric M2

Image Removed

Job Sheet Considerations 

1. Job Sheets are processed left to right and top to bottom. Excel will sometimes compensate for not sticking to this rule but Aspose, the program BUZ uses to generate the PDF's from Excel does not. So this means that a sheet cannot refer to cells on another sheet to the right of the current one because it will not have it values calculated at that point in the process.
2. Cells in the body of the job sheet (the area that grows when number of line items exceed template) must NOT BE MERGED. 

To spread text across multiple cells:

1. 1. Select the cells value is to spread across on the first row.
   2. Right click, select Format Cells, Alignment and Centre Across Selection and then copy down.

Formating for Inches (Fractions)

1. Select cell(s) to be formatted.
2. Right click cell(s), select Format Cells, Number
3. Select Category: Fraction
4. Select desired Type

Note: If format doesn't work it is because Excel treats the number as text so type +0 on the end and that forces Excel to treat it as a number. 

Setup of "Tickets" Job Sheets

Door Tickets (1 per page)

Your Tab name needs to be "Tickets" plural

Set print area on the first Ticket only for item 1, have a second ticket setup with all formulas but relating to item 2.

Each formula will contain "INDIRECT", This allows the ticket to go down the page rather than across the page as BUZ is already programmed to do.

For the first ticket cell "A1" will contain the item number

Using the below formula returns a reference in the cell you require in this case the "Item number"

=INDIRECT("'Data'!BN"&(10+A1))

The data settings should be set to your first printable area,even though you have the formulas in for the second item.

The below examples show

First row 1

Last row 33

Right column U

Image Removed

Download Job Sheet template/example here → JobSheets_SECD_BUZ_(1).xlsm

Image Removed

Curtain Ticket (2 or more per page)

Set print area on the first page which is 2 or more tickets.

Each formula will use the Excel "INDEX" command, This allows the ticket to use a single Job Sheet line to go down the Ticket page.

Cell "A1" will contain the item number for the first ticket "1" and the second ticket will contain the item number 2 using the formula "=A1+1"

Using the below formula returns a reference in the cell you require

=INDEX(JobLines,A1,2) JobLines refers to a named area which is the line items on the Job Sheet tab, A1 the item line and 2 is the column where 1 is the first column in the JobLines named area. This named area needs to match the DataSettings for the Job Sheet tab and is best when starting in Column A, and must be first line item row, last line item row and rightmost column.

The DataSettings should be set to your first ticket area, even though the printed area is set to 2 or more tickets.

Image Removed

Download Job Sheet template/example here → 20160527.0916_Curtains_Job_Sheet_BUZ_BUZDemo.xlsm

Image Removed

 Pricing Scripts

1. Curtain Pricing by Calculating Fabric Length for Roll Fabrics

 RRP formula

• [Number of drops] = ROUNDUP([Window width] / [Fabric Width] X (1 + [Fullness% from HEADING STYLE] / 100))
• [Lineal Metres of Fabric] = [Number of Drops] X ROUNDUP(([Window Height] + [Finishing Addition] ) / [Pattern Repeat]) X [Pattern Repeat]
• [RRP] = Lineal Metres of Fabric] X Sell Lineal Metre Height]

 The fabric needs to have: 

• Pattern Repeats in millimetres. Stored on Fabric Inventory Item in Custom Var 1 (PackSize)
• Fabric Width in millimetres. Stored on Fabric Inventory Item in Custom Var 2(PackOpt)
• Can be railroaded? Y / N in Custom Var 3 (PackType)
  
• Fabric Lineal Metre price is stored in Lineal Metre Height for the Fabric Inventory Item.

 The Heading Style (Group Option = "HEADING STYLE") Inventory Item for option selected needs to have: 

• Fullness as percentage addition e.g. 100% = add same window width again. Stored on the Inventory Item of the particular Heading Style in the field Custom Var 1(PackSize).
• Additional fabric for hemming / finishing in millimetres. Stored on the Inventory Item of the particular Heading Style in the field Custom Var 2(PackOpt).
  

 The Return Size (Group Option = "RETURN SIZE") is required as a question for the calculation.

 The Tiebacks (Group Option = "TIEBACKS") Inventory Item for option selected needs to have: 

• Additional Fabric in millimetres e.g. 1000 = adds an extra 1 metre of fabric. Stored on the Inventory Item of the particular tiebacks in the field Custom Var 1(PackSize).

This calculation is based on the above Number of Drops which is the quantity for the calculation of price.

See our Curtain Template with Calculator for more details and easy setup. Curtain Script Calc & Quick Setup Template.xlsx

Scripts needed (Add these to the groups for pricing calculations, can be added to both Cost and RRP):

.

2. Curtain Design - Soft Roman Pricing 

RRP Formula

The fabric needs to have:

• Fabric Width - If left blank 1000mm is used.
• Pattern Repeats in millimeters. Stored on Fabric Inventory Item in PackSize
• Fabric Lineal Metre price is stored in Lineal Metre Height

3. Blinds - Panel Glide Pricing 

RRP Formula

• Track Width (ITEMWIDTH) / QTY = Panel Width and then use that width X ITEMHEIGHT to search Price Grid.

3. Doors - Panel Pricing 

RRP Script: 20171026.1629_Common Options - Extrusions & Profiles- Option_Script_RRP.cs

Using Excel Name Manager

Named Areas are added using Name Manager, in Excel 2013 this is found on the FORMULAS tab near the middle.

1. 
2. Select New
3. 
4. The box opens and Type the Name, in Scope select the sheet being referenced and then click where the red arrow is pointing and select the cell containing the value. Click OK.

Job Sheet Formula Explanations

Formula used to produce a “due date”

for a Supplier / Factory  that doesn’t equal a weekend date and is 2 or 3 days prior to your Follow Up date (Install Date)

Step 1: Follow up date -3 for expected complete production date =LEFT(Data!BA5,10)-3

followup-date -3, picking up 10 to the left to only select the date

Step 2: Date Formula;    =IF(WEEKDAY(AB2,2)=6,AB2+2,IF(WEEKDAY(AB2,2)=7,AB2+1,AB2))  

If follow-up date equals Saturday then follow-up date plus 2 days

If follow-up date equals Sunday then follow-up date plus 1 day

Formula used to produce Bar code on Job Sheets 

=BarcodePosition("Z",1)

Z= Cell location you would like to start the barcode in

1 = Row location you would like to barcode in

The Barcode size is dependent on the row height and is calculated at the→ Row height /6.8 + 10

The Barcode is made up of info from the Data tab (Ref Number BC5, Revision BD5 and Inventory group code BA1) 

Image Removed

Changes to Inventory Workbooks for BUZ 3 to BUZ Cloud "BOD"

Job Sheets need to be edited for BOD and can be saved as XLSM. BOD allows downloading of Job Sheets as PDF or XLSM(Excel). XLSM is mainly for making changes to the template that is used to generate the PDF.  

Changes that need to be made are:

.Image Removed

Add to the Data sheet the DataSettings named area (see Using Excel Name Manager below). The table below can be copied and pasted into the Data sheet as a template, we suggest at A5. This is required to resize the form when there are more items than the current number of lines in the template and to indicate which worksheets (tabs) to include in the PDF.

Image Removed

In the VBA (Alt-F11) clear all text in ThisWorkbook it should look like this before it's cleared
Image Removed

For help relating to othe pages that can be added to a job sheet click on one of the links below

Job Sheet - Components

Job sheet - Labels

Job Sheet - Tickets

Job sheet - Barcode

Job sheet - BUZ 3 to BUZ Cloud

Job sheet - Pricing Scripts