Customization and Carrying over steps

Download of a model 3D Boki system

The first step in customization procedure is downloading a model 3D Boki system best fit for your business. Make a choice from the three types of 3D Boki systems as follows;

• 3D Boki system with the file name of “ETFC0_GL20.ods” is what you should download if your corporation does not need departmental accounting.
• Whenever your corporation has multiple departments to be acconted for, 3D Boki titled “ETFC3_GL20.ods” is the one you should download. “ETFC3_GL20.ods” is a 3 department model. According to your number of departments, addition or delettion of department columns, department sheets and modification of department names become necessary as described in detail below in this page.
• 3D Boki titled ”ETFDV_GL20.ods” is the one what an individual should download if you run a sole proprietorship.

»

Overwite company name, beginning and ending date of fiscal year (sheet)

・Overwriting in CL cell

• First, overwrite the cell B2 with your official company name on the CL sheet (see the right illustration).
• Second, the dates of beginning and ending of your fiscal year in the cells outlined in red in the above illustration. Overwite B4 with the beginning date, and B5 with the ending date. Then, in the cells outlined in blue, temporarily overwrite B6 with the month end date of the first month of your fiscal year and B7 with the same date as B4. Be sure not to erase the single quotation mark as the first character in the cell and keep the “YYYY/MM/DD” format.
• This is all you have to do on the CL sheet. Those four cells have attribute of cell unprotection. You will follow the above procedures after year-end for carrying forward to the following year. Until then, hide the CL sheet by Alt→O→S→H. For day-to-day accounting practice, the hidden CL sheet will protect your system better from unintentional modifications.

・Overwiting column names on BS sheet:

1. Overwrite the string representing the months in the fiscal year in the first row of BS sheet (see the above illustration). Be sure to follow the format of “Col_”+”year”(4 digits)+”month”(2 digits) as the above illustration shows. For example, the first month of the year is represented as “Col_201301”, and the ending month of the year is given as “Col_201312”. The columns M through AN are filled with these strings.
2. In all 3D Boki systems, column M corresponds to the ending month of the fiscal year before last, column N corresponds to the first month of the prior year, column O to the 2nd month, …, column Y to the ending month of the prior year, column Z to the first month of the current year,..., and column AK to the ending month of the current year. Further, columns AL, AM, AN are reserved for temporary use without carrying over to the following year as the first, second and the third months of the following year. The strings must be overwritten keeping those correspondencies of columns with the months of the years.
3. When making BS, PL and CS, any of these strings serve as a guide from what column to take the figure of the prior month, and to what column to post the current month end figures. So a little bit painstaking, overwrite those strings correctly.
4. Maintaining the same correspondence between the column and the month, overwrite the year in the fourth row and month name in the fifth row. These information is not used by the 3D macro, but overwrite them correctly because these are important visual aids when reading trends on BS. PL and CS.
5. Finally, copy and paste the BS range of M3:AN5 after step 4 above to the corresponding range on PL, each department PL and CF sheets. 3D Boki recognizes the month-column correspondence based on the input data in the first row of BS sheet only. This necessitates the maintenance of the month-column correspondence on PL, each departmental PL and CF sheets exactly the same as on BS sheet.
   (added：September 3, 2012, 18:02)
6. That's all for fiscal year related overwriting. Because this relates to fiscal period, those steps are to be followed once a year when carrying forward to the following year.

TOP

Tailoring for departments
(applicable only to departmental accounting)

• In case of more than 3 departments

  1. Addition and renaming departmental PL sheets

     • Activate ST sheet (or any other department PL), make sheet copy by Alt→E→S→M command until the number of department PL sheets be the same as the number of your departmental units.
     • Rename each of the department PL sheet by Alt→O→S→R command (or double clicking the sheet tab located at the bottom of the screen).
       Department PL sheet name is preferably represented by 2 alphabet characters, for example, “GA” for general & administrative department.

  2. Insert columns for departmental apportionment
     on BK, JV, AP, and AR sheets.

     • Unprotect BK, JV, AP and AR sheets, and insert columns for apportionment to the right of the “JE amount” column so that their number of columns equals the number of departments. The model 3D Boki system “JTFC3_GL20.ods” has three apporionment columns for “Standard”, “Custom” and “G&A” departments by default. For 4 department accounting, insert a column between the “Custom” and “G&A” columns. Before inserting a column, select any cell on the “G&A” column and stroke Alt→I→L. For 5 department accounting, insert 2 columns in the same way to make them 5 in total.
     • Rewrite or input in the title row (2nd row) of the above columns on each sheet the department names commonly used in your company. The order of the departments must exactly be the same for BK, JV, AP and AR. It would be easier if you input department names on BK sheet first, make copy of the cell range and then paste them to other sheets.
     • Finally, on BK sheet only, input or rewrite the abbreviated department names exactly the same as those given to each department PL sheet in the hidden row (the first row) of apportionment columns. The model 3D Bok “ETFC3_GL20.ods” already has “ST”, “CS”, and “GA” input in those cells. They will serve you as a guide where to rewrite or input those short names. In order to select a hidden row, key in in the name box such a cell name as “P1” having the row portion of 1. Name box is located toward the left top corner of the screen immediately above the column name row of “A” or “B” or “C”. In the illustration above, you can see the cursor shaped like bold “I” and beneath it, the popup of “Name Box”, and the name box shows “A182”. This method of sneeking in to the hidden row or hidden column is worth remembering for it is convenient and frequently used.

  3. Inserting columns
     on the DPT(department summary) sheet

     • Select the cell G5(“G&A”) and insert columns by the key stroke of Alt→I→L. Repeat it until you get enough columns. Next, select the cell indicating “G&A” under “Year-To-Date” and insert columns in the same way. Do not select the merged cells in row 3 where “Current Month” or “Year-To-Date” is input. If you do, you will get a result quite different from what you intended.
     • After insertion, rewite or input department names in the 5th row under both “Current Month” and “Year-To-Date” captions.
     • Next, modification and copy&paste of formulas.
       Select a cell in row 8 and input a formula of “=$XX.$F8” in the department column under ”Current Month” where “XX” stands for the department name, so that the formula refers to the cell in the column F of the corresponding department PL sheet. Likewise on the same row, input a formula of “=$XX.$E8” in the department column under “Year-To-Date” where “XX” stands for the department name, so that the formula refers to the cell in column E (month-end figure) of the corresponding department PL sheet.
       
       For this step, first, copy the formula of the cell with 8th row of old “G&A” column and paste it to the cells of the other departments on the same row. Then, replace the “XX” portion with the department PL sheet name. This must be done for both of the cells under “Current Month” and “Year-To-Date” captions. Repeat this replacement all the departments.
       
       After making the formula referring correctly to the applicable cell on the corresponding department PL sheet on the same row, copy the formulae of all the departments both under “Current Month” and “Year-To-Date” captions, and paste to all the rows beneath the 8th row if column B cell of the same row has account code input. Finally, copy the formula of the cell on the “Total” row in the old “G&A” column and paste it to all the other department columns in the same row. Repeat this for all the “Total” rows.

  4. Inserting columns on the “Apportion” sheet

     • Select the cell D2(“G&A”) on the “Apportion” sheet and insert columns by key stroke of Alt→I→L. Repeat it until you have the columns to accommodate all the departments.
     • Rewrite or input department names commonly used in your company.
Congratulations ! You have done all the works necessary for addition of departments by completing steps 1 to 4 above.



• In case of 3 departments

  The procedure is the same as in the case above, except for addition of sheets and columns is not necessary. So, please follow the instructions regarding renaming in the preceding procedures for the items listed below;
  1. Department PL sheet names
  2. Column names on BK, JV, AP and AR sheet
  3. Abbreviated department names in the first row of BK sheet
  4. Column names on DPT sheet
  5. Column names on Apportion sheet
  (Added：2012/09/05, 07:16)

• In case of 2 departments

  1. Deletion and renaming departmental PL sheets

     • Two department accounting is the accounting with minimum number of departments. If one department accounting is what you want, throw away “ETFC3_GL20.ods”, and use “ETFC0_GL20.ods” which would not require you to go through the following steps.
     • First, activate department PL sheet of “CS” and delete CS sheet by keystroke of Alt→E→S→D.
     • Then, rename the remaining two department PL sheets. See section 1 under “Addition and renaming departmental PL sheets” above. for how to rename a sheet.
       

  2. Delete the column “Custom” on the BK, JV, AP and AR sheets

     • Unprotect BK, JV, AP and AR sheet, and delete the column of Custom to the right of the “JE amount” column by the keystroke of Alt→E→E→C.
     • Rewrite or input in the title row (2nd row) of the above columns on each sheet the department names commonly used in your company. The order of the departments must exactly be the same for BK, JV, AP and AR. It would be easier if you input department names on BK sheet first, make copy of the cell range and then paste them to other sheets.
     • Finally, on BK sheet only, input or rewrite the abbreviated department names exactly the same as those given to each department PL sheet. in the hidden row (the first row) of apportionment columns. The model 3D Bok “ETFC3_GL20.ods” has “ST” and “GA” left in the hidden row, so overwrite “ST” and “GA” with your abbreviated department names. For how to show a hidden row, see the third paragraph under section 2 of “Insert columns for departmental apportionment on BK, JV, AP, and AR sheets” above.

  3. Deleting “Custom” column from the DPT(department summary) sheet.

     • Set your cursor to the cell F5 of “Custom” and delete the column by the keystroke of Alt→E→E→C. Then, set your cursor to the cell input as “Custom” under “Year-To-Date” and delete the column in the same way. Do not select the merged cells in row 3 where “Current Month” or “Year-To-Date” is input. If you do, you will get a result quite different from what you intended.
     • After deleting columns, key in “Currnet Month” in the cell F3, and “Year-To-Date” in the cell I3. Also input your department names in the cells of E5, F5, H5 and I5.
     • You do not need to modify the formulae on the DPT sheet, because change in department PL names will automatically change the formulae.

  4. Delete the column for “Custom” on the Apportion sheet.

     • Set your cursor to the cell C2 on the Apportion sheet and delete the column by the keystroke of Alt→E→E→C.
     • Then, rewrite the cells B” and C” with your department names.

TOP

Customizing accounting codes and names(sheet)

• Addition and/or deletion of accounts on the BS and PL sheet of the model 3D Boki system is what you do in the customization of accounts.
• For addition of accounting code and name, see Insert a row (I) section in the “Command menu” page.
• For deletion of accounting code and name, see Delete a row (D) section in the “Command menu” page.

TOP

Last but important step – modification of macro sheet

Macro sheet is a OOo sheet where you write in OOo Basic programs. What you do here is to overwrite some definitions of user specific parameters. Do not be afraid of the word “program”. You just select and rewrite what is written already in the macro sheet in one or several (no more than 10) words. Let's start with opening the macro sheet by the keystroke of Alt→T→M→O→B (or Alt+F11) which opens “OpenOffice.org Basic Macros” dialog as shown below.
Click the “Organizer(O)...” button toward the right bottom corner of the dialog, and you will see “OpenOffice.org Basic Macro Organizer” dialog as illustrated below.
In the left bottom half of this “Organizer” dialog, there is a branch “Standard” under “ETFC3_GL20.ods”. If the mark in front of “Standard” shapes like “+”, click it and let show the sub-branch “VZ” under “Standard”. Then, select “VZ” and “Edit” button on the right side of the dialog becomes active. Click the Edit button, and “ETFC3_GL20.ods-Standard OpenOffice.org Basic” macro sheet will open.

The macro sheet will look like one as illustrated below according to your choice from ETFC0_GL20, ETFC3_GL20 and ETFDV_GL20.

In the above illustration, the items 21-23 and 52-53 with yellow background color must not be changed, the items with background color of light blue may be modified, the items with font color of magenta differ according to the model of your choice. Do not change items #44 or larger if you do not use MW sheet made available as an advance payment register for imports which usually entails time lag between cash payment and actual receipt of goods. Further, if you use the set of accounting codes of the model 3D Boki system without change, items 11 through 18 can be kept unchanged. So there are not so many items to be changed as it looks.

When you modify, change the parameters after the mark of “=”. Where the parameter is between quotation marks, change the parameter only leaving the quotation marks. Now, let's get started. First of all, overwrite "Ten Feet Co. Ltd." of Line #1 with your official company name. It must be exactly the same as the name you have written in cell B2 of the CL sheet.

Now, some explanations are due for (1) Line #31 and 39, (2) Line #34, 35 and 42, 43, (3) Line #25 and (4) Line #26. Other items are self-explanatory.

(1) Line #31 and 39 (payment terms):

Parameter AP_Due of Line #31 is used when AP sheet is active, and parameter AR_Due of Line #39 is used when AR sheet is active. “Insert a row” command (Alt→A→I) makes a copy of the data of the row where the cursor was set and paste them to the row immediately below the active row. However, the fields of month like “Month booked”, “Month accrued” and “Month settled” are pasted differently. For the fields of “Month booked” and “Month accrued”, the current month is posted, and for “Month settled”, the sum of current month and AP_Due or AR_Due is posted. When the parameter AP_Due is set as 1, the month settled becomes the next month, and when AP_Due is 2, the month after the next is posted.

For example, when AP_Due is 1 and the current month is November, 2013, the “Month Settled” cell will be “11312” which represents the next month of the current month. In the same example, if AP_Due is 2, the “Month Settled” cell will be “11401” which represents the month after the next. The identical calculation is made for AR_Due for posting to the month settled cell on the AR sheet.

Because payment terms differ from vendor to vendor or customer to customer, register AP_Due or AR_Due what you estimate the most prevalent so that you can save time for modification.

(2) Line #34･35 and #42･43 (the 2nd and 3rd sorting key):

KeyAP2 and KeyAP3 are the column numbers for second key and third key when sorting of AP sheet is done by Alt→A→S. And KeyAR2, KeyAR3 are the column numbers for second key and third key applied to sorting of AR sheet by executing Alt→A→S.

If you give KeyAR2＝12 (the column number for month incurred) and KeyAR3＝11 (column number for month settled), when KeyAR1＝4 (column number for customer code), sorting by Alt→A→S will sort firstly in the ascending order of customer code, and for the items with the same customer code, sort secondly in the ascending order of the month incurred, and further if the customer code and month incurred are still the same, sort thirdly in the ascending order of the month settled.

Now, if you give KeyAR2＝12 (the column number for month incurred) and KeyAR3＝1 (column number for invoice number), the sorting order will be, 1st - customer code, 2nd - month accrued and 3rd - invoice number. You might prefer this order. Try and find a sorting order best suited to your needs.

(3) Line #25 and 26 (date formats used by matching tool):

(3-1) Line #25 (date format for posting to BK sheet):

This is to define the format of receipt date posted to the cell in column A on the BK sheet from the matching tool dialog opened by Alt→A→K.

• Parameter “Date” will post the receipt date in the format of “YYYY/MM/DD”.
• Parameter “0Num”(the first digit is zero) will post the receipt date in the format of “MM/DD” where month or day figure is less than 9, zero is added in front to make them two digits.
• Parameter “Num” will post the receipt date in the format of “MM/DD”. However, in this case, the month or day figure less than 9 is kept unchanged without being added zero in front.

Make a choice from appearance viewpoint in respect of the format of the other rows on the Bk sheet. The author prefers “0Num”, therefore “0Num” is the default parameter on the VZ macro sheet.

(3-2) Line #26 (date format for posting to AP and AR):

This relates also to matching tool. This parameter of PRDateOrStr decides the format of payment date (or receipt date) when matching tool dialog posts to the cell in column C on the AP(or AR) sheet.

• Parameter “Date” posts payment (or receipt) date in the format of “YYYY/MM/DD”. If you have a single bank to deal with, this will give you sufficient information.
• Parameter “BN_Num” posts payment (or receipt) date in the format of “one character of the bank name” + ”MM/DD” (reformating all month and day to two digit figures). Because multiple banks are used in usual cases, this parameter gives you better information to identify the bank and tie in with its bank statement.
  
  As for the functions of the posting button on the matching tool dialog and the rule in getting one character from a bank name, see Sub-section 5, posting Items selected for settlement in box (B) to BK and AP sheets in the “Matching tool (K)” section.

TOP

Customizing FS (Financial Statements) sheet (Sheet)

• FS sheet is one of the Calc sheets which has no macro programs to work thereon. It is the author's policy to leave it to the users who has the equivalent ability of high school graduates of accounting courses.
• The prototype FS sheet as a part of 3D Boki has proved to be acceptable in the author's 25 years of practical use. In Tax Examinations, the FS as an attachement to a Corporate Income Tax Returns, has been rather well valued for the informativeness of its comparative style.
• However, the prototype FS has a Japanese style format. Therefore, according to the accounting rules and regulations in your country, there may well be such cases which require a major modification as where the account presentation must be in the reverse order of liquidity. But in any case, the basic building block of the FS with the use of “SUMIF” function should not lose its usefulness.

• SUMIF function

  The accounting figures in the FS refer the figures on BS and PL sheet using SUMIF function.
  
  The SUMIF functions are input in the cells of column E, “Current Year”.
  For example, the cell E57 has the formula of
  

  = SUMIF($BS.$A$1:$A$152;C57;$BS.$E$1:$E$152)

  This is a command that (1) in the cell range of A1 through A152 of the BS sheet, (2) find cells with the same value as that of the cell C57 of FS sheet, (3) and for those rows found to coincide, make summation of the figures in the range of E1 through E152 of the BS sheet and present the result in the cell E7 where this formula is embedded. In general terms, SUMIF function is expressed as SUMIF(Range; Criteria; SumRange), where the parameter “Range” means the range to which the criteria is to be applied and “SumRange” defines the range from which values are summed.
  
  You may guess that there could be cells in the hidden column A of BS sheet which has the same content as that of cells in colomn C of the FS sheet. The column E of BS sheet retains the figures on the month end date which falls on the year end date at the fiscal year end. So, this is where we can make summation to get the figures to show on the FS.
  
  Get into the hidden column A of BS sheet using the “Name Box” mentioned earlier in this page and stroke keys of Alt→O→M→S in order to make sure what are in there. For example, in the column A of the BS sheet, you see ”Cash and cash equivalents” in two rows. The figure of the FS cell in the column “Current Year” with the account name of “Cash and cash equivalents” is the total of the cell values on the intersections of column E and those two rows of BS sheet.
  
  Accodingly, your customization steps of FS will be;
  
  1. Input appropriate account name in the cells of column C of FS.
  2. Make copy of the account name and paste it to the applicable cells in column A of BS sheet.
  3. For the added accounts in FS sheet, make copy of the SUMIF function from some other cell in the column E and paste it to the added cell. Also, make a copy of the formula of some other cell in column I and paste it to the added cell in column I of FS.

• Other formating of Financial Statements

  (1) Adjust the the “Total” row formulae in column E, G and I.
  (2) Modify the information on the first page of your FS.
  (3) Modify the fiscal year title of BS, PL and others.
  (4) Modify your company and representative name where appropriate.
  
• It is your responsibility to make the FS complete by reviewing for youself, by your superviser or your external professional. It is the work made by your full-year best effort as a professional accountant. The author as an ex-professional recall that he reviewed over and over again, and after making whatever checks he could think of, used to allow a night to let the unnoticed speak for itself before making a FS final.

TOP

Carrying over to the following fiscal year

The work of carrying over to the following fiscal year is grouped into three. First, works on CL sheet, second, on such financial reports as CF, BS, PL and department PL sheets, and third, on such books as BK, JV, AP and AR sheets.

1. Making a new 3D Boki system file for the following year

   Open the latest 3D Boki system and execute Alt→File(F)→Save as(A) to create a new file. The new file name should include figures to indicate the fiscal year or the number of fiscal years after establishment.
   If ExcelVBA version is used, the “Customization Procedure” of A-4 and B-10 described in the “to Customize” sheet of “EFTVBA.xls” must be followed.

2. Update dates on CL sheet

   Carry one year forward ”Beginning at”, “Ending at”, and “Month ending at” (temporarily use the first month end date of the new fiscal year) on the CL sheet. For where to input, refer to the section of “Overwite company name, beginning and ending date of fiscal year”

3. Carrying BS sheet forward

   Copy the cell range of Z1 through the cell on the intersection of column AK and the row of “Audit of Dr./Cr. Balance:”, then select cell M1, and paste special by the keystroke of Alt→E→S only text and numbers.
   
   Then, carryover the texts in the first row indicating the months of the fiscal year. (see “Overwiting column names on BS sheet” above)
   
   Finally, replace all the figures of “Retained Earnings – At Beginning” in columns Z through AK with the figure of “Retained Earnings – At Ending” in column Y.

4. Carrying CF sheet forward

   Copy the cell range of Z3 through the cell on the intersection of column AK and the row of “Bank Balance per GL:”, then select cell M3, and paste special by the keystroke of Alt→E→S only text and numbers.

5. Carrying FS sheet forward

   Replace the “Prior Year” figures with the “Current Year” figures using the paste special command (Alt→E→S) for texts and numbers only. For security purposes, open the FS sheet of the latest and final 3D Boki system to copy the “Current Year” figures, then paste special to the “Prior Year” figures of the FS of the new 3D Boki system. This eliminates the possibility of inadvertent alteration of the final FS figures.

6. Carrying PL sheet and department PL sheet forward

   Copy the cell range of Z3 through the cell on the intersection of column AK and the row of “Audit of Sub-Totals:”, then select cell M3, and paste special by the keystroke of Alt→E→S only text and numbers.

7. Carrying BK sheet forward

   Leaving the transaction recods of January 1 and after of the old fiscal year (Example: Assuming the new year is from April 1st, 2014, retain records on and after January 1st, 2013), delete all the rows before that date. The records on and after January 1 of the prior year are used for making information return to be submitted to the tax office in Japan. Different requirements may be set forth in your country, so, take those into your consideration to decide which records to delete.

8. Carrying JV sheet forward

   Leaving the transaction recods of January 1 and after of the old fiscal year (Example: Assuming the new year is from April 1st, 2014, retain records on and after January 1st, 2013), delete all the rows before that date. The reason is the same as that mentioned in the prior paragraph. Adjustments to the cut-off date may be necessary in your country.

9. Carrying AP/AR sheet forward

   Never delete the rows which have carried forward balances from the old fiscal year. If the transaction volume is not very large, it should be a good idea to retain transaction records for the past two fiscal years and delete the others without beginning balances. The only consideration here is that the less the unused data, the faster you get the results of various commands. It is left to your discretion.

TOP