This instruction will show you how to set up a basic job sheet.
Job sheets are located within the product "Group" level.
To navigate there, you will need to go to settings → Group Settings → Groups tab.
Before you start
First, You don't need to be an Excel guru, but there are a few things that help you to know so that you can understand the Job sheet.
Take a look at a few Example job sheets, download them, and we will eventually modify them to suit your business.
Job Sheet Templates - Download a template here.
We have made two templates.
Job sheet - basic layout is a simple form with a table for the job sheet and check measure.
Job Sheet - with Bill of Material has many example pages, including
Tube size chart example,
Parts List
Deductions tab with a few example formulas
Components sheet and an example for a full Bill of material.
You are welcome to modify the template and remove almost every tab that is not required. You will need the Data and Job sheet tab as a minimum.
Now that you have opened the job sheet, I will explain a few things
The data that is populated in the Job sheet comes from several places
Information is gathered from
There are areas in the Excel spreadsheet that are called "Named fields", which can make life easier if you know how to use them.
Job sheets contain a Macro, but you can not add your own macros
BUZ uses Excel as a tool to get the coded information in and out of BUZ
Before attempting to create and finalise a job sheet, you must have the inventory and group options completed; without this information, it will be challenging to complete the Job sheet.
Step 1.
Upload a template Job sheet.
Go to Setting → Inventory Settings → Groups (Will be the first tab)
Select the Group you want to add the Job sheet to
Scroll to the bottom of the screen
Click on the "select file" button and upload the template Job sheet
It doesn't matter which job sheet you add, nor do the headings matter too much just yet; we can connect the customer data to the correct columns later.
Step 2.
Setting up a test customer
If you haven't already, Create a test customer/lead, ensuring you fill in all available options.
You can input your details or make them up; this customer card can be useful for testing new products and or processes rather than using a current customer.
Having this data in the customer card will help you allocate the information in the job sheet.
For help creating a Lead or customer, click on the link below.
Also, anywhere you see a "Notes" field for testing purposes, input something here, something that will remind you where you entered the information.
Step 3a
Create an Order
We need to generate data for the Job sheet so that we can see how BUZ allocates this information.
Create a quote, again make sure every question is answered (even if it is not a mandatory question)
Step 3b
Finalising the order
Complete all the Finalize Questions.
Step 3c
Accept the Quote
You may need to process and approve if required, don't worry about any payments.
Once the order has been approved, print the job sheet
In the top right is a Print button - Print the Job sheet - excel
Great work! Now we have created an order with our required information; we are ready to look at the Job sheet.
Job sheet - Data Tab
Open the Excel file; this may be located in your "Downloads".
Once the document is open, you may be prompted to "Enable Editing" Click the button, and you will be able to make changes to the Job sheet.
If this is the first time you have opened a job sheet, it will look quite alien but don't worry it is not as complicated as it first appears.
Below is an example of a "populated" job sheet; by populated, I mean customer and product data.
For a detailed overview specific to the Data Tab, click here
Let's break down the different areas in the Data tab
Data Settings
The first thing you will notice is our Data Settings table, we won't worry about this for now, but we will return to it later.
Customer and sales rep Information
Data in Row 5 (between Columns AV - DU) contain Customer and Sale rep information.
Getting this information to other tabs is as simple as =Data! + Cell number (example - Customer. Description would be =Data!BF5).
Commonly used (but not restricted to) cells are
Quote No. - Concatenate of BC5 & BD5
Customer Name - BF5
Address - Concatenate of BH5, BI5 & BK5
Email - BR5
Customer Order Reference - AX5
Sales Rep - CE5
Note: Please ensure you test any formulas in the setup stage; these cells may have moved or changed since this help was written.
Key Word Headings
Key Word headings have a special significance and may serve an additional purpose like a calculation.
For more information relating to Key Word Headings, click here.
Row 11 - Between Columns AV and BW if all 3 Descriptions have been utilized in the Group inventory Questions. If one of the Descriptions is not used, there will be fewer columns.
Not all of the product information is here, so we tend not to use this information wholly.
A significant constant is that the first Product is always on row 11, and the unique Identification key is in Cell AV, so commonly use AV11 to perform look-up searches.
Group options - Cust OrdOpt
The Group Options area contains all the data that pertain to an order; this information will populate in a "Named Field" called CustOrdOpt - which can be populated in Rows CC - CK. This is dependent on the use of the description fields and if they have been utilised. This may change from product to product but will generally stay in the same area once the product has been set up.
Data explained
So where is data coming from?
All the headings you entered into the group options populate into the Job sheet with the answer provided in the order.
Above is a small snippet of the Job sheet and the Group options,
The Headings in Green,
Questions asked in the Quote process in Orange and
Answers in red
What do we do with all this information?
Now we need to organise the data into a table that helps you.
BUZ has provided a table that is commonly used, but you don't have to use this layout if you have something different
Let's look at the formulas on the Job sheet page
A few common formulas that we use in BUZ are
Formula | Explanation | Example |
|---|---|---|
= Cell | by pressing = then selecting a cell, this will show the value of a cell (that may be in another tab) in the Cell you require Where you see Data! in the formula like =Data!BF5 Mean that the cell value is being copied from the Data tab Generally, all of your information will come from the data tab unless you have deductions or reference tables, but we will get to that later. | =Data!BF5 |
=Cell1&"."&Cell2 | this will add two cells together with a . between them | =Data!BC5&"."&Data!BD5 |
=CONCATENATE(Cell1,".",Cell2) | essentially the same as above but works on older versions of Excel | =CONCATENATE(Data!BC5,".",Data!BD5) |
=TRIM(Cell1) | TRIM function returns a text value with the leading and trailing spaces removed. | =TRIM(Data!AX5) |
Example of the customer details area of the job sheet
Product Table
This space is where all the product information will populate. A commonly used formula would be the bpLOOKUP; this will use the reference in the heading row (Row 12 in the image below) to find the answer selected in the order.
Example of the product table
If you look at the table above, I have put the Heading in Green (the same heading you would have in the group options)
The formula's in this table can be customised to suit your needs,
The support team has provided a few examples of some useful formulas. Click here to see them
Once you have set up the table, you will need to define the table size in the Data Tab
Data Tab - Data Settings table
This is a quick guide for the Data Settings table.
You will need to enter the values of the table here so that the software knows where to populate the data.
Data Settings
Sheet name - relates to the corresponding Tab,
First Row, Last Row and Right Column
These values dictate the size of the table on each page (If a table exists).
First Row
Last Row
Right Column
The table is designed to expand if there are more products than lines. BUZ will use the formulas in the "Last Row" and copy them down.
Factors that may affect the population of information in the table → Job sheet not printing correctly
If there are formulas in the rows below the table, they will stay and even populate, but the information will be duplicated from an existing Row causing more products to be made than required.
If there are formulas on the right side that are not within the data table, they will populate but will not expand if there are extra products.
The example above has data in Row 20 and in Column H, which will cause the table to be inaccurate.
Include in PDF
Some of the Tabs are only required for calculations, like a deductions Tab or for printing Labels; these are not required to be printed out with the job sheet.
To select which documents are printable, use the value "TRUE" in the "Include in PDF" Column.
No. of Copy in PDF
This lets you set the value for how many copies of the Tab you need. There may be times when you require more than 1 Job sheet; if so, you can change the value in this column.
Save, Upload and Test
Go to Setting → Inventory Settings → Groups
Select the Group (Product) that you have been working on
Scroll to the heading Job Sheet (at the bottom of the page)
Select the file and press the "Save" Button
Go back to the test order and print another Job sheet.
Dont lose heart
Chances are that it's not exactly what you expect; this is common, and you may need to edit the Job sheet several times to get the correct setup; remember to keep saving copies so you can go back if needed.
Help!
Click here for some possible errors that may be occurring.