The Estimate maintenance allows the following:

Retention of the spreadsheet details and version control
Access of the estimate spreadsheet by users who are allowed access to maintain the spreadsheet (TMSBP/OPT-APR – User Options – Estimating).
Uploading of Costs, Profit & Loss and specification by version to IP1.
Viewing of Cost, Profit & Loss values, and specification details saved in IP1 from the spreadsheet, controlled via TMSBP/MNT-APR & TMBSP/OPT-APR). Estimate mapping structure is set up in TMSBP/EST-MAP.

The Estimate screen has the following functionality:

Release spreadsheet for email approval process
Change existing spreadsheet
Copy an existing spreadsheet
Delete a spreadsheet and associated related data in IP1
Refresh details from IBM Power Systems to spreadsheet
Upload spreadsheet figures (Costs/budget, specifications and Profit & Loss, customer order number and series code) to IP1
Lock approved cost sheets. Once approved no changes are updated to the host system.

If the user chooses the option to maintain a spreadsheet (S/Sheet) then the system automatically starts up Excel with the applicable spreadsheet in a new window. To update these changes from the spreadsheet from version to Book spec level, the user takes the “Upload” option (upload all specs, budgets, P&L info, RRP and print qty).

The copy option takes the current spreadsheet and copies it with a new version number and starts up Excel. The naming convention used for the spreadsheets is Project ID(6) + Book ID(6) + Version(3).XLS.

The Reference of the storage location (network location) of the Excel spreadsheet is stored in TMSBP/EST-PATH. This nominates the path for all spreadsheets to be held if maintained by the system. TMSBP/EST-SVR nominates the directories for the client and server. This Control File defines the server code and attributes in case you have users in various locations and allocated to various servers.

Estimating is carried out within an Excel spreadsheet and uploaded to IP1 once approved. Normally estimating has four stages:

Current version
Proposal
Price fix
Final

These stages are user configurable. Only one person can maintain a spreadsheet at a time. If the in use flag in IP1 is flagged as Y=Yes for a spreadsheet then it cannot be accessed as someone is working on it. If flagged as N=No you can access the spreadsheet and work on it

Specifications will be entered within the Specification option in IP1 before it is loaded to the spreadsheet.

Costing is worked out in the spreadsheet and is then uploaded in to IP1.

For freelance, print costs must be setup in templates.

Budgets will download into the spreadsheet as blank the first time, as costing will be done in the spreadsheet then uploaded to the IP1 budget option. Any time after that the budget will be downloaded and relevant cells populated within the spreadsheet.

For ad hoc costing, entries can be made directly into the spreadsheet.

The Gross Profit option shows a summary of the history.

If the spreadsheet is being used by another user the in use flag is flagged as yes. Once the user has finished with the spreadsheet and closed it the Excel Processing program automatically changes the in use flag to No.

The [reset] option allows you to ‘unlock’ the estimate spreadsheet if it is locked because of an error on the spreadsheet. [Err Code] allows the user to view the error code and to correct.

Estimates need to be approved via the approval process, if approvers are set. The Release button must be selected for the proposal (price fix, final) to get approved. When this Release Option is taken, the minimum number of approvers is checked and also a check for duplicate approvers is performed. The message “Minimum number of approvers should be set” may display. The minimum number of approvers required for approving an estimate is established in TMSBP/BS-APPRM. All pending approvers who must approve the proposal (price fix, final), display on the screen. Each approver is notified by email that there are pending estimates. Estimating is automatically updated with ‘Approve’ once the approver has approved the estimate or it goes to pending for the next approver. Once it is approved it is copied to the current version but cannot be maintained.

Note: The current version is the most up to date version.

If there is an Estimation Server in use, TMSBP/EST-CFG is flagged accordingly so that this server is used when interfacing with the Estimating Excel spreadsheet. Users should be linked to an applicable server in TMSBP/EST-SVRU.

Whether the Control Budget should be updated from the Estimate file is determined by TMSBP/EST-BUD. The Control Budget and the Total Budget will be built from the estimating process and maintenance will not be allowed from the Header update.

TMSBP/EST-STG2 contains the specific stages and actions which will be performed when those stages are approved. Hence the update will occur when the specific stage is approved and it has to be in record status ‘A’ (actuals).

TMSBP/ACTDEF determines each action code and its description. These actions are performed for a task within the schedule.

TMSBP/MNT-APR contains the valid maintenance options that can be used in the Estimating approval process.

TMSBP/OPT-APR contains the user/user group that can have maintenance access in the Estimating process.

TMSBP/EST-CFG can be flagged to upload paper or not. If set to upload paper the Materials of the Project will be uploaded to IP1 from the spreadsheet. If this Control File is flagged not to upload paper any paper calculations made within the spreadsheet will remain within the spreadsheet.

Approval process

The Estimating screen displays the Approvers (for example 5 approvers) for the approval of the proposed estimate linked to the specification, budgets and Profit & Loss. These users should exist in the Employee Masterfile.

The Employee Masterfile (BPM600) includes an e-mail address per employee, which is used to notify the respective users of pending activities (projects or specifications awaiting approval). These Approver fields are not mandatory. TMSBP/BS-APPR lists the approvers. If this Control File is not setup, the system validates against persons in the Employee Masterfile. TMSBP/BS-APPRM holds the maximum number of approvers in a list. ie. 2 approvers.

The Approval process is as follows:

Once the estimate is ready for pending or approval and the person responsible for initiating the approval releases the estimate for approval the following occurs:

The system notifies the first approver on the approval list and the status of the transaction changes from E (in progress) to P (Pending).
The Pending Approval program allows the respective approvers to check all the projects or specifications waiting for approval or rejection.
The approvers have access to the specification, costs and Profit & Loss that has been loaded to Bookmaster. This can be automatically done if the Control File is filled.
If the estimate is approved, the system notifies the next person on the list, via e-mail, and the estimate is available on the next approvers pending review list.
When the estimate is approved by the final person on the approval list the status of the estimate changes from P (Pending) to A (Approved). An e-mail is sent to the initiator of the pending process (the first person on the approvers list). The initiator has the option to finally transfer summary information to Bookmaster.
If the estimate is rejected, a reason for the rejection would be prompted by the system. This is mandatory. The system then e-mails the first person on the approval list and changes the estimate status to R (Rejected).
The initiator of the process is then able to copy the initial estimate and proceed with the entire process again for multiple stages of the Project.

Note: Approvers can only approve or reject the estimate and not maintain it.

As part of the Approval system, approval/rejection text can be mandatory. The person rejecting the estimate would be prompted for text which would then be available for viewing by persons given access to reading the text. Access is controlled via TMSBP/OPT-APR. TMSBP/TEXT-TYP holds Book Production Text Types.

Note: If an estimate is rejected it cannot be released.

Spreadsheet – calculation of print costs

The Excel spreadsheet has the required information of the specification, costs and profit & loss of the title. To be able to calculate the print costs, the spreadsheet uses the scales (print cost tables) stored in the database, or manual input for non grid books.

The spreadsheet uses the format, text, extent and quantity fields to pick up the appropriate spreadsheet and lookup the cost. This is based on grid codes for a selected printer and the print quantity for that book.

The following fields from the estimate spreadsheet are used to determine print costs:

Format (A, B, C+ etc) are used to determine the workbook.
The paper type determines the sheet.
The quantity determines the print cost from the table that has been pre-determined by the format and the text, paper type and print quantity.

Excel processing

The Excel processing program uses the location server to resolve the current Excel server. The following Control Files must be set up:

TMSBP/EST-SVR which describes various settings of the Java server

TMSBP/EST-SVRU which assigns a user to a particular server (EST-SVR)

This allows multiple servers for processing at different locations.

IP address registering and polling to work with the Internet is available.

A read only feature is available to all fields.

The paper requirements file is cleared after the delete option is taken.

Book spec item levels 1 – 5 and description are included.

A Refresh option is available to update Excel from the IBM Power Master.

The user can flag fields they don’t want uploaded based on TMSBP/EST-MAP, hence this flag controls both upload and download where if the value is flagged as ‘1’ – no upload back to the IBM Power Systems or ‘2’ – no download, to keep it backward compatible. When actuals are to be shown in the spreadsheet, record type should be flagged as ‘3’ and when budgets are to be seen flag the record type as ‘1’.

The same field can be in two locations – one for upload, another for Download.

An integrity check for paper upload is performed. All main fields are mandatory and if any are missing the system will not upload the information.