Copyright: Copyright ® 1991 - 1993 by Kaarle Kaila


Index Page
KKBOOKS.XLA-Book-keeping software

Main Index

Copyright © Kaarle Kaila 1991-1993.

Introduction

Installing the program

Program commands

Program files

Book-keeping with KKBOOKS

Rents book-keeping

Accounts Receivables and Payables

Instructions in Finnish


Preface
Book-keeping software KKBOOKS

Copyright © Kaarle Kaila 1991-1993.

KKBOOKS is an Excel 4.0 based software for book-keeping. KKBOOKS is SHAREWARE. You may make as many copies of the software and give it anyone. Commercial distribution is not permitted unless specially agreed with the Author.

KKBOOKS is the English language version of KKTILIT, a book-keeping program for the Finnish market. The program files, i.e. KKBOOKS.XLA and KKTILIT.XLA are identical. This file is LANGUAGE INDEPENDENT. All other KKBOOKS components are however language dependent. The best testing has been made with the Finnish version. This English version KKBOOKS has been prepared to enable users of other nationalities understand what KKBOOKS is all about.

If you are interesting in making a local version of KKBOOKS in your language with some templates that are valid in your country, please contact me. I would be interested in cooperation.

My SHAREWARE conditions for KKBOOKS are: You may freely test the program. If you decide to use it for your book-keeping you must register yourselves as a user by paying the eqivalent of 135 FIM to me. 135 FIM is equivalent to 15 GBP or 20 USD. See at the end of this chapter for my address information. This fee does not include the right to use the Finnish langauge modules. You can pay the fee to me in any currency. You can send the money to me as an Intrernational Money order, Eurocheck or Cash.

Here are some notes on how KKBOOKS functions.

The program needs an AccountFile, that includes all book-keeping transactions, the AccountMap and the Result/Balance sheet.

If you wish to use this program for rent book-keeping also you must also have a Rents file.

And there is a simple Accounts Receivables part in this software. For this purpose you need the Customer file.

Espoo/Finland 29th October 1993

best regards

Kaarle Kaila

Kalenteritie 23 B 4

FIN-02200 ESPOO

Finland

Contact info: Telephone (29.10.1993):

int+358-0-5696204/office and

int+358-0-885289/home

Electronic mail X400 address: C=FI A=MAILNET P=ICL O=ICL OU1=FIN1701 S=KAILA G=KAARLE


Installing the program

The program is delivered as a self-extracting file KKT0300.EXE. Run this file in the directory where you want to keep the program and it's files. It will then unload it's contents to that directory.

First you must create the English version of the Program File KKBOOKS.XLA. Create it by loading the Excel macro MAKEENG.XLM. This macro will execute itself when loaded and make an English language copy of the Program file: KKBOOKS.XLA. After this the macro will remove itself from memory.

Following files are relevant to the English version:

MAKEENG.XLM	Macro that creates the KKBOOKS.XLA Program File.

KKBOOKS.XLA	Program File. This is created by the MAKEENG.XLM macro from the KKTILIT.XLA file.

KKBOOKS.DEF	Definition File.

KKBOOKS.HLP	Help file.

KKTILIT.ICO	Icon file.

KKEXAMPL.XLS	Account example.

KKCUSTOM.XLS	Customer file.

KKRENT.XLS	Rent example.

The rest of the files belong to the Finnish version of the program

The program needs an AccountFile. This file contains all the transactions but also the Account Chart and the Income Statement and Balance Sheet and some formulas. The KKEXAMPL.XLS is such a simple AccountFile. You should use that as an example to make a suitable AccountFile for your needs.

If you need Rents book-keeping too study the Rents book-keeping file KKRENTS.XLS.

The program files KKBOOKS.XLA, KKBOOKS.DEF and KKBOOKS.HLP must be located in the same directory.

The Program is an Excel 4.0 Int't macro. Most formats are defined in the module KKBOOKS.DEF. If you encounter problems with e.g. national date or currency setting you may check the fields and their formats in this file.

All comand names for KKBOOKS are located in the file KKBOOKS.DEF. The commands in KKBOOKS.DEF are all written in English. If you need the software in another language than English you should convert the texts in KKBOOKS.DEF to this other language.

Formatting the output

Some of the rapports are printed directly from the AccountsFile. You should adjust the width of the respective columns in this file.

The column widths for some rapports are defined in the KKBOOKS.DEF file in columns D-M. By changing these values you can permanently change the format of the rapport. Remember to save the changes. KKBOOKS.XLA never saves the KKBOOKS.DEF file. Following rapports can be formatted this way:

Col_AccMap	Account Chart

Col_MainBook	Ledger

Col_SaldoList	Account Balance

Col_Result	Simple Income statement + Balance.

Below these you can see some model settings for some printers I have tested these with. I have made these tests on Windows 3.0 in 1991 so they may not be fully accurate anymore.

Font type and size for most rapports is defined in the file KKBOOKS.DEF in column D beginning in the fields header and text. By changing the font and size in these fields you also change it in the rapports. Some texts that are printed directly from the AccountFile is not affected by these definitions, so you may have to change the fonts and texts in this file too.

You can install the KKBOOKS.XLA file in your Windows 3.1:n Program Manager. Do it with the Program Managering File New command. Find with the Browse command the KKBOOKS.XLA file. You may change the icon you use to KKTILIT.ICO if you like.

Start of program

Start the program by double clicking the icon you created in the previous chapter or Open the KKBOOKS.XLA with Excel 4.0 file open command. The program request for the AccountFile during this open process. A new menu Book-Keeping is created.


Book-keeping with KKBOOKS
Book-keeping with the KKBOOKS program

The AccountFile contains all transactions.

First you must create and Account Chart in the AccountFile. This is described in the chapter Files

Write the company name in the AccountFile and begin date of the Fiscal year.

Test the various rapports to find out whats available in KKBOOKS.

You have 2 choices for the Income Statement and Balance Sheet

1. The official format

You must define the official format to the AccountFile according to the rules in your country and with help from the KKEXAM.XLS example file.

2. Simple format directly from the Account Balances


Rent Book-keeping

See the descriptionKKRENTS.XLS for details of the rents book-keeping file format.

Rents book-keeping is perfomed during the feeding of the transactions. A spwcial dialog for feeding rents is available.

KKRENTS.XLS file shows how musch each Residence has paid also compared to how much they are supposed to have paid.

Recalclulate Rents

You can at any time recalculate the rent balance. This is needed if you have made some manual changes in some of the files or if you have forgotten to save the rents file.


Account Ledger

Feeding information for the Account Ledger

is performed by joining the customer information to the transaction

Recalculate Customer Balances

This routine recalculates the Customer balances. This is needed if you have made some manual changes in some of the files or if you have forgotten to save the rents file.

Background and description of the KKBOOKS current account ledger

The result of my studies of the current account ledger secrets was that a simplified current account ledger requires the following information for a transaction verificate:  transaction type, customer identification, invoice number and due date. Transaction entry is identic to other transaction entries, but the information mentioned above must be entered before accepting the transaction. Therefore, the transaction dialogue includes a customer button, which enables the entry of respective information.

Accounting requires a separate customer file similar to the residence file in rent accounting. Customers are listed by customer number with customer-specific balance. Space is available for other customer data.

The Customer button included in the transaction entry dialog box fetches the customer file, from which the appropriate customer is selected. The selection is performed by selecting the respective row from the customer file. Then press the Resume button shown on the screen. The application then displays a dialog box, where to enter invoice number and due date. Invoice number is mandatory but due date is optional. Then customer and invoice number are entered into the the account file until the next customer transaction is selected from the customer button. The character "R" is inserted into the account file type field to indicate that this column is included into the  current accounts ledger.

The customers are listed below the header line in the customer file. If the requested customer does not (yet) exist, the customer information can be written on a new line before pressing the Resume button.

The line preceding the header line in the customer file is reserved for account numbers to be tracked.  The commands listed below bring to the header line field located below the account number information as follows:

Usually Receivables and Payables are the accounts tracked in the current account ledger.

The following transaction types may be needed:

Receivables:

Invoice entry A sales account is usually the An account and Receivables, and perhaps allowed discounts, a Per account.

Payment entry As An account usually Receivables and as Per account some Assets account, e.g. bank account. discounts etc.

Entry of annual discounts, bonus etc.

Credit invoice entry

Interest invoice entry

Payables:

Invoice entry

Credit invoice entry

Interest invoice entry

Current account reports

There might be some useful reports on the current accounts; e.g unpaid/paid invoices. Customer report including balance information. A report on invoices with passed due date may also be useful.  Because the KKBOOKS files are clearly visible as EXCEL tables, these reports can be generated straight from the account file.


KKBOOKS.XLA Commands

The program adds the Menu Command Book_keeping to the starndard Excel Menu commands. By selecting this command you will see the commands included:

Transaction Entry

Feeding of new transactions. This command accepts all kinds of transactions, also rent transactions.

Entering Rents

Feeding of rent transactions only.

Update Transaction Balances

This command calculated the Transactions Balances. This is needed e.g. if the transaction database has been modified manually. The program checks always that the transaction sum equale the sums of the Debet and Credit Balances and suggests execution of this command if these sums do not equal. This check does not expose all errors in the balances.

Update Rent Balances

This command recalculates the Rent Balances in the RENTS file. The rent balances are calculated from the rents in the transaction database.

Update Customer Balances

This command recalculates the Customer balances in the customer file.

Initialize Transaction Data

This command clears the transactions in the transaction file. It also moves the Account Balances to the previous years balances. This can be used to compare the balances to the previous years correspondig ones. These previous years balances are showed in the Account Balances List command.

Account Chart

This command lists the Accounts used. It also displays the lines in the Income Statement or Balance Sheet where this account is summed to.

Ledger

This command lists the ledger including either all or selected Accounts.

Account Balance

This command lists the Accounts with their balances. It also displays the budgeted balances and the balance sums and % and the balance sums of the previous year.

Account Transactions

This command lists all the transactions.

Income Stat. + Balance from Account Balances

A simple Income Statement and Balance Sheet is printed. This report is printed directly from the AccountBalances using the Account types to decide whether the accouns will be printed in the Income Stament or in the Balance Sheet. It Calculates the profit/loss from the Income statement and moves it to the Balance sheet.

Income Statement + Balance Sheet

The Income Statement and Balance sheet report in the columns AB-AH of the Account File is printed.

Link Account Chart

Connect the Accounts to the Income Statement and Balance Sheet input data by building formulas in columns X and Y. The Column Z contains Y-X. This value is referred to in the Income Statement or Balance Sheet in columns AB-AH.

Help

This helpfile is displayed from thie command

Exit

Exit book-keeping. The book-keeping program KKBOOKS.XLA is an Add'In macro. Exiting is easiest done from within the macro itself. This command also closes (without saving) the KKBOOKS.DEF worksheet.

About...

Displays the program version and Copyrigth notice.


Files needed by the program

KKBOOKS uses the following files:

-	KKBOOKS.XLA, the program file and KKBOOKS.DEF the definition file.

-	Accountfile

 	kkrent.xls

 	customer.xls

KKBOOKS.HLP is this helpfile.

-	KKTILIT.ICO is an icon, that can be used to include KKBOOKS.XLA in Program Manager.


Account File

Create your accountfile using the KKEXAMPL.XLS example accountfile. Testing of all functions in this example are not necessarily waterproof. You may name the file as you like.

File format is:

Rows 1-2 are used for headers. These headers are used in some reports.

TRANSACTIONS:

are located in columns A-L from row 3.

Meaning of columns A-H:

A:	Verification number. use number 0 when opening the books for the fiscal yesr. The verification numbers is automatically increased by one. You may command the program to use the verifiaction number of the previous transaction if one verification need more that one transaction

B:	Date of transaction

C:	Debet account for this transaction (to-account)

D:	Credit account for this transaction (from account)

E:	Sum

F:	Description1. Program inserts the Account name in most cases if this is left empty.

G :	Description 2.

H:	Project id can be set here if used

I	Type. Special transactions like rent and ledger info is marked here

J	Customer Id. Either the appertment of customer Id

K	Incoice number

L	Due date

Program parameters are in columns O-P

Account Chart and Account Balances

The Account Chart and the account balances are located in columns Q-W. Rows 1-2 are ´header rows.

The account is identified by a 4 digit number. The accounts are grouped in 3 different ways.

1.	Account type defines what you can do with the account.

2.	The Account number  organizes the the accounts. The Account Balances report sums all accounts where the account number belongs to the same thousand.

3.	The simplified Income Statement and Balance Sheet prints the accounts in the order they are in the Account Chart.

Columns Q-W are:

Q:	Account number. 4-digit

R:	Account type

S:	Account name

T:	The Debet balance of the account

U:	The Credit balance of the account.

V:	Budget values for the accounts.

W	Values for the accounts from the previous fiscal year.

Account types are:

R =	Money account

M =	Expence account

T =	Income account

D =	The Asset accounts for the Balance Sheet

K =	The Liabilities accounts for the Balance Sheet

3 special account types exist

A =	Balance. This account is used when opening the new books

U =	The Result. This account is used by the simplified Income Statement and Balance Sheet

V	Sums previous accounts on the simplified Income Statement and Balance Sheet. The account number is 0000. There can be several of these.

Other account numbers 0000 are header rows for the simplified Income Statement and Balance Sheet.

The Income Statement and Balance Sheet

The report is located usually starting at column AA. The report itself is in an area called ResultBalance (Formula Define Name).

The first column is referred here by [0].

4 columns before this area is reserved by the program as follows:

Column [-4] contains a formula that calculates the Debet sum of the Accounts.

Column [-3] contains a formula that calculates the Credit sum of the Accounts.

Column [-2] contains a formula with Credit-Debet

Column [-1] contains a printing guide character for the report itself.

The balances in the Income Statement and Balance refer to the Balance in column [-2] of the same row. If the value in this column is not #N/A it is copied to the balance in the report.

The Printing guide characters are:

P	Obligatory row

B	Blanc row

X	Conditional row

H	Header

T	Account row

S	Sum row

(H)TS	are part of a group. H and S are printed if at least one row exists with acceptable data

In a HTS group the Sum row contents is coped to the last row. Other unused T or S rows are hidden.


Rent book keeping
KKRENT.XLS

This sheet contains the monthly balances for the rents per appertment.

Following definitions are needed (Formula Define Name). See the example.

Field name	Contents

Appartments	one row per appartment. The header row is included. The sum row is not included.

MonthNo	For Internal calculation

PayAcc	Maximum 6 columns for accounts that can accept the rent

Period	First day of Fiscal Year.

RentAcc	Max 6 accounts that can be part of the rent ex: rent, Heating, Warm Water

In column 3 you can see the balance of thi smonth rents. If it is negative it id displayed in red

The buttons on the sheet help you to view the different parts of the sheet.


Customer file

Use the Customer file CUSTOMER.XLS if you want to use the simple Accounts receivables and/or payables feature

The column A of the first header row is named CustomerHeader by Define Name command. This is mandatory.

Following columns are mandatory

A	CustomerId

B	Name

The row above the header is reserved for the accounts to be followed per on customer basis. This is limited to columns C-Q.


Select file

This command reuqest the user to select the file (type is in dialog caption). First the files already in memory are presented.


Select Period

The Account balances can be printed for a part of the Fiscal Year only.

The program calculate the period balances befor the Account balances report is printed. If you only selected part of the fiscal year the program will find out by comparing the sums in later commands that the sumd do not match. It will then ask permission to recalculate the balances. If you no longer need these part year balances answer OK to get the balances right again.