XaCT: a Costing Tool

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
Rathinagiri
Posts: 5212
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 156 times
Been thanked: 145 times
Contact:

XaCT: a Costing Tool

Post by Rathinagiri » Tue Oct 28, 2014 7:51 am

xact1.png
xact1.png (134.87 KiB) Viewed 1677 times
XaCT : a Costing Tool

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.
Then why XaCT?

The following features are unique to XaCT. These are the purposes for which XaCT is created.

Tree Structure:
  • 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:
    Clipboard01.jpg
    Clipboard01.jpg (30.32 KiB) Viewed 1677 times
  • 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.
    Clipboard02.jpg
    Clipboard02.jpg (8.46 KiB) Viewed 1677 times
Now we are coming to the main part, which is the cell.
  • 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.
Clipboard03.jpg
Clipboard03.jpg (28.92 KiB) Viewed 1677 times
  • 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.
    Clipboard04.jpg
    Clipboard04.jpg (42.67 KiB) Viewed 1677 times
  • 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.,
    Clipboard05.jpg
    Clipboard05.jpg (36.82 KiB) Viewed 1677 times
  • 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.
Cells can be of three types in XaCT viz., ordinary cells, public cells and bookmarked cells.
  • 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.,
This is how a whole sheet look like.
Clipboard06.jpg
Clipboard06.jpg (58.04 KiB) Viewed 1677 times
  • 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.
Now, it is time for scenario manager.
Clipboard07.jpg
Clipboard07.jpg (64.92 KiB) Viewed 1677 times
  • 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:
Clipboard08.jpg
Clipboard08.jpg (74.73 KiB) Viewed 1677 times
  • 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.
About XaCT:
  • 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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Rathinagiri
Posts: 5212
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 156 times
Been thanked: 145 times
Contact:

Post by Rathinagiri » Tue Oct 28, 2014 8:00 am

The total project folder along with the application, source code, resource files, sample files and the above write up is available for download from here.:

Friends, this is an alpha version for evaluation purposes only. I invite member's comments and suggestions for improvements.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Agil Abdullah
Posts: 204
Joined: Mon Aug 25, 2014 11:57 am
Location: Jakarta, Indonesia
Contact:

Post by Agil Abdullah » Tue Oct 28, 2014 8:40 am

Friends, this is an alpha version for evaluation purposes only. I invite member's comments and suggestions for improvements.
Hi, it looks great. That's I am waiting for: a ready-to-use program developed with HMG.

Unfortunately, I failed to build the source-prog with HMG331:
Error reads "....unknown function: sql(), c2sql(), miscsql(), connect2db()"

But, at least I can learn from your project. Many thanks.

Regards.
Agil Abdullah Albatati (just call me Agil)
Programmer Never Surrender

User avatar
Agil Abdullah
Posts: 204
Joined: Mon Aug 25, 2014 11:57 am
Location: Jakarta, Indonesia
Contact:

Post by Agil Abdullah » Tue Oct 28, 2014 9:00 am

The above error found when I build it from DOS-prompt.

When using HMGeasybuild, error read like this:
Error: Referenced, missing, but unknown function(s): SQL(), C2SQL(),
MISCSQL(), CONNECT2DB()
Agil Abdullah Albatati (just call me Agil)
Programmer Never Surrender

User avatar
Rathinagiri
Posts: 5212
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 156 times
Been thanked: 145 times
Contact:

Post by Rathinagiri » Tue Oct 28, 2014 10:18 am

Hi, you require libhmgsqlite.a in hmg lib folder.

Also you require libsqlcipher.a which is bundled here.
libs.zip
(538.16 KiB) Downloaded 126 times
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
esgici
Posts: 4441
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 330 times
Been thanked: 99 times
Contact:

Post by esgici » Tue Oct 28, 2014 10:28 am

Great !

Thanks Mr. Rathinagiri :)

Happy HMG'ing :D
Viva INTERNATIONAL HMG :D

User avatar
bpd2000
Posts: 1017
Joined: Sat Sep 10, 2011 4:07 am
Location: India
Has thanked: 164 times
Been thanked: 72 times

Post by bpd2000 » Tue Oct 28, 2014 11:48 am

Thank you Rathi for sharing
BPD
Convert Dream into Reality through HMG

User avatar
serge_girard
Posts: 2064
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 417 times
Been thanked: 91 times
Contact:

Post by serge_girard » Tue Oct 28, 2014 12:04 pm

Thanks Rathi !

Serge

User avatar
serge_girard
Posts: 2064
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 417 times
Been thanked: 91 times
Contact:

Post by serge_girard » Tue Oct 28, 2014 12:14 pm

Rathi,

Comments and suggestions for improvements:

- user login ... etc (now everybody can mess up content). Perhaps with user authorization at sheetlevel?
- extend english.txt with text from form etc (msgyesno( 'Are you sure to save this config?' ))

Overall it is looking great !

Can you supply more example data in order to get more idea about the project?

Serge

User avatar
esgici
Posts: 4441
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 330 times
Been thanked: 99 times
Contact:

Post by esgici » Tue Oct 28, 2014 12:17 pm

serge_girard wrote: Can you supply more example data in order to get more idea about the project?
+1
Viva INTERNATIONAL HMG :D

Post Reply