| Description |
This invoice template is customized as an estimate template for engineering service business - for providing and fixing of curtains and curtain accessories, including curtains and blinds, awnings, canopies, garden umbrellas, to be specific.
The "Ship To" section now has a new title, "SITE ADDRESS", so in this section it is possible to fill in the location where the service to be performed.
The most significant feature of this service invoice template is that it includes 10 columns, and has complex relationship built between the cells/columns using Excel formulas to make the template meet the requirements of the intent service business. This provides a good example of the flexibility of Excel Invoice Manager, the billing software and invoicing software using Excel invoice template as its frontend, as it is able to apply formulas to create/define/build the relationships between cells and fields, while many other non Excel-based invoice programs allow you to customize just the style or format of the invoice form, such as fonts and colors.
|
| Detail |
It's well known that Microsoft Excel provides powerful features for formatting and presenting data, and that is why so many invoice templates are provided in Excel format. However you may not notice that Excel also provides handy tools to define or build the relationship between cells or fields.
For example, the unit price of your product or service might be determined by many factors that should also be listed on the invoice form, such as customer category (VIP, wholesale, or general customer), measurement unit, etc.
Relationships between cells and columns
Here is a brief description of what implemented by this engineering service invoice or estimate template by using Excel formula.
- There are 3 unit types used by the products offered by the service company, i.e. "per Sq.ft.", "per Rn.ft." and "per PC". If the unit type is "per PC", the Line Total of a service item should be calculated from Quantity and Rate. Otherwise, the Line Total should be "Quantity * Price * Area or Length".
Here is a formula example of how the Line Total is calculated:
=ROUND(IF(NOT(ISERR(SEARCH("per pc",oknUnit_1,1))),
oknQuantity_1*oknPrice_1,
oknQuantity_1*oknPrice_1*oknAreaLength_1),2)
- If the unit is "per Sq.ft." the Area or Length should be Area Temp, with a minimum value of 10.76; or in case the unit is "per Rn.ft.", the Area or Length should be Area Temp with a minimum value of 3.
Here is a formula example of how the Area or Length is calculated:
=ROUND(IF(OR(oknUnit_1="",NOT(ISERR(SEARCH("per pc",oknUnit_1,
1)))),0,IF(NOT(ISERR(SEARCH("Sq",oknUnit_1,1))),
IF(oknAreaTemp_1<10.76,10.76,oknAreaTemp_1),
IF(oknAreaTemp_1<3,3,oknAreaTemp_1))),2)
- Area Temp is a temporary value stored in a hidden column outside the Print_Area. The value is calculated from the other two columns, W and D, which could be measured in different ways, including CMS, Inch and Ft. The choice of measurement unit is implemented as a drop-down list labeled "Choose W/D type here", and the column titles are changed depending on the current measurement unit, such as W[CMS] and D[CMS], W[Inch] and D[Inch], etc.
Below is a formula example of how the Area Temp value is calculated.
=IF(NOT(ISERR(SEARCH("Sq",oknUnit_1,1))),(oknItemW_1*oknItemD_1)/
IF(oknWDType="CMS",929,IF(oknWDType="Inch",144,1)),oknItemW_1/
IF(oknWDType="CMS",30.48,IF(oknWDType="Inch",12,1)))
- On the bottom of the invoice form, in additional to those cells defined by the default/standard invoice template included in Excel Invoice Manager setup program, there should be a field for discount, a field for labour charges, a field for advance payment, and a field for balance due. All these fields should either be manually editable, or be calculated automatically, depending on the meaning of each field/cell.
It is clear that the relationships between the cells / fields is much more complex than what in the default invoice template shipped with Excel Invoice Manager setup program, yet Excel Invoice Manager, the billing software and invoicing software based on Excel invoice templates, still can handle this type of complex easily with Excel formulas.
Custom fields
To implement the features required by the estimate or service invoice template, there are a number of custom fields defined / added to both the underlying database and the front-end Excel template, as detailed in the following table.
| Table name |
Field name |
Field type |
| Customer |
Arch |
text (60) |
| Invoice Header (InvHdr) |
RefNo |
text (30) |
| Invoice Header (InvHdr) |
Date2 |
date |
| Invoice Header (InvHdr) |
Arch |
text (60) |
| Invoice Header (InvHdr) |
W |
text (30) |
| Invoice Header (InvHdr) |
D |
text (30) |
| Invoice Header (InvHdr) |
WDType |
text (30) |
| Invoice Header (InvHdr) |
Labour |
decimal (12,2) |
| Invoice Header (InvHdr) |
Advance |
decimal (12,2) |
| Invoice Header (InvHdr) |
Total1 |
decimal (12,2) |
| Invoice Header (InvHdr) |
Discount |
decimal (12,2) |
| Invoice Body (InvBdy) |
Shade |
text (60) |
| Invoice Body (InvBdy) |
ItemW |
decimal (12,2) |
| Invoice Body (InvBdy) |
ItemD |
decimal (12,2) |
| Invoice Body (InvBdy) |
ItemWDType |
text (30) |
| Invoice Body (InvBdy) |
AreaLength |
decimal (12,2) |
| Invoice Body (InvBdy) |
Unit |
text (60) |
| Product |
Unit |
text (60) |
For more information about using custom fields, please see
Custom Fields - Overview and
Custom Fields - Tutorial. |
