Administration guide PRISMAdirect Formula manager
Copyright © 2015, Océ All rights reserved. No part of this work may be reproduced, copied, adapted, or transmitted in any form or by any means without written permission from Océ. Océ makes no representation or warranties with respect to the contents hereof and specifically disclaims any implied warranties of merchantability or fitness for any particular purpose.
Trademarks "Océ" and "PRISMA" are trademarks or registered trademarks of Océ. Adobe®, Acrobat®, Reader® and Distiller® are registered trademarks of Adobe® Systems Incorporated. Portions copyright © 2011 Enfocus BVBA; all rights reserved. PostScript® 3™ is a registered trademark of Adobe® Systems Incorporated.
Contents Contents Chapter 1 Introduction.........................................................................................................................7 Software version.............................................................................................................................................. 8 The worksheets.................................................................................................................................................9 Worksheet "CostSheet".....
Contents 6 PRISMAdirect v1.
Chapter 1 Introduction
Software version Introduction This help file describes how you can use the formula manager. What does it do The formula manager allows you to change the cost calculation rules of the system. The formula manager uses the default functionality of Microsoft Excel. You can configure the default formulas. You can also create new formulas. Where is it The formula manager is located in [PRISMAaccess Administration].
The worksheets The worksheets Introduction By default, a number of formulas are available. You can define custom formulas in the columns next to the default formulas up to and including column Z. Each column can contain one formula. Each column contains all information for one formula: • The name and the calculated result of the formula. The result of the formula is shown in row 3. • The calculation for the formula. The formula can use one of the default calculations, or a custom calculation.
Worksheet "CostSheet" 10 Frame Description 3 The formulas By default, a number of formulas are available. You can define custom formulas in the columns next to the default formulas up to and including column Z. Each column can contain one formula. The formula can use one of the default calculations, or a custom calculation. The result of the formula is shown in row 3. 4 The calculations Default calculations are available. You can also create a custom calculation.
Worksheet "Utilities" Worksheet "Utilities" The layout of the formula manager in worksheet "Utilities" 1 2 3 4 5 Frame Description 1 The [Captions] table The translated names for the formulas in the "CostSheet" worksheet are taken from the table [Captions] in "Utilities" worksheet. The [Captions] can be modified for each language and the changes will show up in [Web Submission]. NOTE The [Captions] table is defined as a Named Range. The name of the Named Range is [CostCaptions].
Worksheet "Utilities" 12 Frame Description 4 The delivery time table Same day delivery of the job is more expensive than a delivery after one or more days. Create an absolute cell reference to use the values from this table. For example: Utilities!$D$37. 5 The default worksheets. Chapter 1 - Introduction PRISMAdirect v1.
Chapter 2 The toolbar buttons
[Add items] [Add items] Introduction If you want to add [Job ticket items] to the CostSheet and automatically use the proper calculation settings. Use this procedure. NOTE If the required [Job ticket items] are not available close the Formula manager. Open the [Job Ticket Editor] to create the required [Job ticket items]. Procedure 1. Click [Add items]. The [Add items] dialog will open. The dialog contains all [Job ticket items] that are not available in the job ticket table. 2.
[Refresh] [Refresh] Introduction The job ticket definition can be changed in the [Job Ticket Editor]. Click the [Refresh] button to update the [Item values] of the [Job ticket items] in the job ticket table. Click the [Add items] button to add new [Job ticket items] to the job ticket table. Procedure 1. Click the [Refresh] button. The [Item values] of the [Job ticket items] in the job ticket table are updated. A row is added to the job ticket table for each new [Item value].
[Used items on top] [Used items on top] Introduction If you want to sort [Job ticket items] from the CostSheet without disturbing the proper calculation settings. Use this procedure. Procedure 1. Click the [Used items on top]. The usage of an item is calculated with the following formula: number of values in the item * number of appearances in the formulas. The list of [Job ticket items] will be sorted from top to bottom in a descending order relative to the usage of each item.
[Remove all unused items] [Remove all unused items] Introduction If you want to remove unused [Job ticket items] from the CostSheet without disturbing the proper calculation settings. Use this procedure. Unused [Job ticket items] are the [Job ticket items] that are not used in any formulas. Procedure 1. Click [Remove all unused items]. The unused items are removed from the job ticket table. 2. For each removed [Job ticket item]: 1. Remove the [Job ticket item] from the result table. PRISMAdirect v1.
[Remove items] [Remove items] Introduction If you want to remove [Job ticket items] from the CostSheet without disturbing the proper calculation settings. Use this procedure. Procedure 1. Click [Remove items]. The [Remove items] dialog will open. The dialog contains all [Job ticket items] that are available in the job ticket table. 2. Select the [Job ticket items] you want to remove from the CostSheet. You can select the [Job ticket items] one by one via the check boxes or select all / deselect all. 3.
[Remove unused regions] [Remove unused regions] Introduction The job ticket definition can be changed in the [Job Ticket Editor]. If you want to remove [Job ticket items] that are no longer in the definition you can use this procedure. Procedure 1. Click [Remove unused regions]. All [Job ticket items] that are not in the definition will be removed from theCostSheet . 2. For each removed [Job ticket item]: 1. Remove the cells of the result table from the calculations that used the [Job ticket item]. 2.
[Use system language] [Use system language] Introduction You can automatically translate the following parts of the formula manager into the installation language of Microsoft Excel: • The [Job ticket items] and default headers in the job ticket table. • The buttons of the formula manager. The installation language of Microsoft Excel must be one of the 18 languages that are supported by PRISMAaccess.
[Use default style] [Use default style] Introduction The factory default can be applied to the job ticket table in worksheet CostSheet. All custom changes to the job ticket table are lost. Procedure 1. Click [Use default style]. The factory default is applied to the job ticket table in worksheet CostSheet. All custom changes to the job ticket table are lost. • The names of the [Job ticket items] and the default headers are restored.
[Use default style] 22 Chapter 2 - The toolbar buttons PRISMAdirect v1.
Chapter 3 Working with the formulas
Check the [Job ticket items] Check the [Job ticket items] Introduction The required [Job ticket items] must be available before you can create or configure a formula. Procedure 1. Check that all required [Job ticket items] are available in the job ticket table. If the required [Job ticket items] are not available see : Add items on page 14 2. Check that all required values of the [Job ticket items] are available in column [Item values]. Column [Item values] is column B of the worksheet "CostSheet".
Create a formula Create a formula Introduction By default, a number of formulas are available. You can define custom formulas in the columns next to the default formulas up to and including column Z. Each column can contain one formula. Each column contains all information for one formula: • The name and the calculated result of the formula. The result of the formula is shown in row 3. • The calculation for the formula. The formula can use one of the default calculations, or a custom calculation.
Create a formula 2. Define the name of the new formula in cell Z1. In this example: "Job cost". The formula names in row 1 are displayed in the [PRISMAaccess Administration]. 3. Define the name of the new formula in cell Z2. In this example: "Job cost". The formula names in row 2 are displayed in the [Web Submission]. Define the price for the [Item values] of the [Job ticket items] in column Z. 4. Optionally, you can define a custom name for the new pricing column. In this example: "Job cost". 5.
Create a formula NOTE You can use the functions of most categories in Microsoft Office Excel to create a calculation. You cannot use the functions of category "Database". 1. Add up the prices of [Copies] and [Cover] in cell Z7: =IF((Z13+Z26)>0,SUM(Z13, Z26),9999.99). 2. If required, you can reduce the total price when the customer orders a certain amount of [Copies]: =IF((Z13+Z26)>0,SUM(Z13, Z26)*VLOOKUP(Z13,CopiesTable,2,TRUE),9999.99).
Test a formula Test a formula Introduction You can create and configure new formulas in the Formula manager. You must test each new formula for correctness. When to do A new formula is created in column Z. The new formula is based on two [Job ticket items]: [Copies] and [Cover]. In this procedure, the new formula is tested for correctness. Procedure 1. Check that the new pricing column uses the values from column [Amount:]. 1. Click cell Z78. 2. Click "Trace Precedents" from the Excel menu.
Test a formula 4. Check that the formula uses the correct calculation. 1. Click cell Z3. 2. Click "Trace Precedents" from the Excel menu. 3. Check that the formula uses the correct calculation. PRISMAdirect v1.
Test a formula 30 Chapter 3 - Working with the formulas PRISMAdirect v1.
Index Index A Add items................................................................ 14 B Button: Add items...................................................14 Button: Refresh....................................................... 15 Button: Remove all unused items......................... 17 Button: Remove items............................................18 Button: Remove unused regions...........................19 Button: Use default style........................................
Index 32 PRISMAdirect v1.
Canon Inc. www.canon.com Canon U.S.A., Inc. www.usa.canon.com Canon Canada Inc. www.canon.ca Canon Europa Inc. www.canon-europe.com Canon Latin America Inc. www.cla.canon.com Canon Australia PTY. Ltd www.canon.com.au Canon China Co., Ltd www.canon.com.cn Canon Singapore PTE. Ltd www.canon.com.sg Canon Hongkong Co., Ltd www.canon.com.hk © CANON INC. & Océ Holding B.V.
