I have chosen this name XaCT for two reasons. XaCT is the recursive acronym of XaCT: a Costing Tool and it is the short form of the word 'exact'. I am one of the millions of people using spreadsheet application as a decision support system and an analytical tool. I am a vivid user of Excel and Calc as a costing tool for more than a decade. When I have designed a workbook for our own organization's costing needs, the workbook spread out to more than one hundred worksheets. Few years back, when I had explained about my workbook to one of my colleagues, he asked me a simple question. 'It is ok, your workbook automatically calculates the costs of all the products you manufacture when you just change the cost of a raw material which goes into many products. But, is there a mechanism to track all the changes to a particular cost element?'. I blinked for a while and convinced him with the scenario manager features in spreadsheets. The question remained in my mind all these years and I wanted to combine both a spreadsheet application and a database application and make a new application exclusively for costing and keep track of changes to a particular cost element.
XaCT is mainly designed to prepare and maintain costsheets effectively.
What is it?
XaCT as the name suggests is a costing tool which can be used for costing/pricing product(s) in an organization. It is a spreadsheet like application. Though it doesn't have all the exciting and fancy features of a full fledged spreadsheet application like Microsoft Excel or LibreOffice Calc, it does have some resemblance of a spreadsheet application.
What is it not?
XaCT is neither a replacement of a spreadsheet application nor a general purpose application. Though it can be used otherwise, it is designed mainly as a Costing Tool. I can find the following main differences between a spreadsheet suite and XaCT. Spreadsheet applications have many user friendly features like range selection, many categorized built-in functions, graphs, pivot tables, scenario manager, goal seeking, data table manipulation, effective formatting to show the data in an useful manner etc., which XaCT doesn't have in its pockets. It would be easier to list out the features similar to spreadsheet software products than to distinguish them.
Features of XaCT similar to spreadsheet applications:
- An XaCT Book is also consisting of worksheets containing cells.
- Cells can hold data (though not many types of data like a spreadsheet application).
- Cells are linked with formulas
- Calculation of all the formulas are done automatically once a cell value is changed.
- Use of a Scenario manager
- Sheet wide relative referencing.
The following features are unique to XaCT. These are the purposes for which XaCT is created.
- Sheets can be categorized into a tree structure, making it easy for searching and finding out. For example, sheets can have a structure like the following:
- This tree structure helps to find out and navigate through quickly.
- A handy sheet navigator with anywhere searching feature can also be used to search a sheet by its name.
- The idea of having sheets in tree structure is to have a separate sheet for each cost element.
- Each sheet name in a XaCT book shall be unique so as to avoid misunderstanding.
- Only open sheets are listed in a separate area to move around the frequently used sheets. In other words, some sheets need not be seen at all, once the formulas and values are set except for referencing them.
- Unlike a general purpose spreadsheet application here you can't have any cells empty!
- Unlike a spreadsheet, here there are no separate columns! So, it is a single column multi-row spreadsheet. However, each row in XaCT sheets are divided into three parts that look like columns. Rows are added to a sheet as and when necessary. Now it is to be remembered that XaCT is not only a spreadsheet based application but also database oriented. Hence, the cell data are arranged in a particular structure.
- First part in a row consists of the name of the formula.
- Second part consists of the numeric data, used in costing calculations.
- Third part consists of remarks/descriptions of the cell.
- XaCT cells are not referenced using row number either. Actually they are always referenced using their formula names.
- Each piece of cost data element is stored in a row of a sheet.
- Cells are referenced with their sheet name followed by a . character and the formula name within square brackets. For example: [materialsheet.chemicalcost]
- Cells can hold there are three kinds of values viz, constants, variables and formula.
- Constant cells can be used for those values that are never changed through out the workbook. For example, inches_to_centimeter conversion factor.
- Variable cells hold data that might be changed in the future and scenario manager is used to change such values. A default value is also saved to use when there are no scenarios available. For calculation purposes, the latest active scenario will be taken into account. The effective value is also shown in the cell editor.
- Formula cells are used to link the cells mathematically. Since XaCT is designed in Harbour language (xBase) and formula cells are simply parsed into a Harbour statement, any Harbour function can also be used inside the formula. For example if(), round() etc.,
- In the above example, there are three cells viz., cost per drum, weight per drum and cost per kg. Weight per drum is standard and constant. It won't change. Cost per drum is variable in nature. Cost per kg is a calculated value which is cost per drum divided by weight per drum.
- Ordinary cells can be referred only inside the particular sheet in which it is defined. These cells can not be referred from outside their corresponding sheets. They are private cells. In the example cost per drum and weight per drum are never used in other sheets. Hence they are defined as ordinary cells by selecting public cell as 'No'.
- Public cells can be referred from other sheets in the book. Again, this is to separate the 'fill it forget it cells' from other cells being used in other sheets. In the example above, cost per kg of the chemical is used in other cells. So, it is defined as a public cell.
- Bookmarked cells are used for reporting purposes, that will be discussed later. In the above example, none of the cells are defined as bookmarked cells, as they are not being used in the end reports. Bookmarked cells might be the total cost of a product or profit earned from a product etc.,
- The last row in a sheet is the only allowed empty row. It is to create a new row. Once the new row is saved using the green button, another empty row is added. By this way we can add as many as rows we require.
- Scenario manager is to manage the changes in the variable cells (for example changes in the prices of materials).
- Scenario manager is divided into two parts.
- Top most table/grid covers the available scenarios. Scenarios are ordered chronologically having recent scenarios first. Each scenario is saved with the following data:
- Their status whether active/inactive.
- Name of the scenario to identify.
- Date of the scenario to be effective from.
- Remarks if any.
- Bottom table covers the variables each scenario is used to change. Each row has the following data:
- Their status whether active/inactive.
- Formula/Row name
- The effective value for the scenario as selected from the top grid.
- The active status makes the scenario or the variable to be active or not. It helps to toggle the scenario how it would be otherwise if this particular variable is not changed.
- Scenario as defined here, will be shown in the particular rows in the sheets also for reference.
- It is to be noted that, only variable cells can be used in the scenarios.
- Reports screen helps to keep track of the changes in the rows.
- Reports can be filtered by the bookmarked cells, public cells or all cells.
- Hierarchy of the sheets can also be used to filter the cells.
- We can select some specific cells also by moving from the list of cells to the selected list of cells to be reported.
- XaCT is designed using HMG, an Open Source Windows application development language/tool.
- The backend database is SQLite. So, one can open a XaCT book using SQLite database browser.
- XaCT is a copyleft GNU general public licensed software product. The source code is open, personal and commercial use of this software is FREE for all at your own risk.