Forum Archive Index - August 2001

["G Stolwyk" <stolwyk@wave.co.nz>]

Readers,

Please read the Disclaimer at end of page first. Should you wish to apply the following information, then you will need to print this page. 

 

1. Purpose of this exercise:

To calculate the value of individual and combined holdings of stocks, debentures, cash and other financial instruments.

 

This calculation will be done off-line and a floppy disc will be used instead. The system is designed to have the output printed on a standard ( A 4 ) sheet.

 

2. Limitations and procedures

MS Works spreadsheet has been used. Excel or any other spreadsheet which uses the same formulae and procedures, can be an alternative. Note, that to obtain maximum benefit of space, font ' Arial ', size 10 is used throughout ( Except for row 2 ).

 

Immediately before printing, the four margins in the ' Page Setup ' will need to be set at 2.0 cm. 

 

Should you want to to use a different font, then you may need more space and the width of column T will be less.

Increasing row height could result in the loss of results from row 39

 

The required Formulae in columns E, G and in some cells, will be pre-inserted regardless of the entry of any stock details.   

 

2.1 Repeating numbers or letters  in a column, example: Repeat the data in say cell A 7 down to cell A 14 incl. Answ.: Enter the data in cell A 7,  position the mouse in the centre of that cell, hold down while dragging the mouse downward till past the end of cell A 14, release the mouse, hold down key:  Ctrl   while tapping key:  D. 

 

2.2 Entering of consecutive Formulae in columns, example: Cell E 5: Enter:      =C5*D5     For cell E 6, the entry is:      =C6*D6    and we continue till we reach the end of cell  E 14. An easier way will be to enter the formula    =C5*D5   in cell E 5, aim the mouse at the bottom right corner of this cell till a  +   appears. Holding down the mouse, drag this + down to just past cell E 14, then click on a vacant cell outside this column.

 

3. Preparation.

Enter the following numbers in row 4, ( from left to right ), starting with cell A 4:   14, 14, 18, 18, 27, 18,  28, 13, 13, 19,  14, 14, 19, 13,  11, 11, 13, 8, 7 and 18. ( Check: cell G 4 will have number 28 and the last cell T 4 has number 18. Please check carefully!

 

Starting with cell A 3 or A 5, enter iiiiiiiiiiiiii this is equal to number 14 in cell A 4 ( Use font Arial, size 10 ). This is the width of column A. Shift any vertical line and make a tight fit.

 

Working across from left to right, enter iiiiiiiiiiiiiiiiiiiiiiiiiiii ( 28 times letter i ) to represent the width of cell G 4. And so, the width of cell T 4 will be iiiiiiiiiiiiiiiiii or 18 times letter i.

 

Please double check the corresponding quantities of the letter i as the success of the product depends on this preparation.

 

Once this has been checked, delete the data you have entered. This empty spreadsheet is now ready for the entry of data and formatting.

 

4. Entry of Data.

4.1 Headings.

Using font Arial, size 10, starting with cell A 4, from left to right, in capitals, enter the words COM, YEAR, SHARES, SHARE, STOCK, ( Leave column F blank ), enter PORTFOLIO ( in column G), then ANNOUNCE ( columns H and I ), MEET, DIVIDEND ( columns K and L ), REPORT ( columns M and N ), DIVIDEND ( columns O and P ), TOT, RE, W and  ?   in column T. ( NB: W=Watch, deletion of this letter will result in spare space.  The  ?  in column T : That is up to you to decide - See end of item 4.3.2 ).

 

Starting with cell B 2 and across the page, using font Arial, size 12, enter the word:  ends  below ' YEAR' and enter price below ' SHARE ' and enter:  value  below ' STOCK ' and enter:  $NZ ( size 10, capitals )  below ' PORTFOLIO '.

 

Enter:  inter  in column H under ' ANNOUNCE ' as well as the word:  final in col I. Enter: inter in col. K and enter: final in column L ( Both entries are under DIVIDEND).

 

Enter: inter in col. M and enter: final  in column N ( Both entries are under REPORT ). Enter:  the one word  cents to take the total space of cols. O and P and enter: cts  in col. Q. Enter: em under RE ( = REgistered with companies to receive their news emails ).

 

Using the font Arial, size 10, row 3, col. D, under the words SHARE price, enter ( Capitals ): $NZ and ditto in col. E ( under STOCK value ). Across to col. O, using Arial, size 12, enter :  int  and in column P, enter:  fin   ( Both entries are under the word:  cents

 

NB: It is best to enter following data and formulae before proceeding with entering your own data: 

 

4.2 NEW ZEALAND Company data, using font Arial, size 10.      Rows 5 to 14 incl. 

Row 5 and across, enter: FFSPA ,  30/6,  1000000 ( no comma ), 0.32 ( col. D ). We assume that we don't have the data for columns H - S incl. and leave these cells blank till we can enter some or all of the missing data later on ).

 

Row 6 and across, SKC,  30/6,  1000, 10.1( in col. D ), and we now enter the following data in columns H to Q incl.:  19/2, 16/8, 26/10, 6/4, 6/10, 19/2, 27/9, 123, 124 and 247 ( col Q ). Enter:   Y    (= yes ) in col.R and enter:   W   in col. S.

 

Refer to item 2.1 and enter the letter:     A     in cells A 7 to A 14 incl.   Repeat:  30/06   in cells B 7 to B 14 incl. Enter number:   0   in cells C 7 to C 14 incl. Also in cells D 7 to D 14 inc. This method ensures that all the rows are live to receive new data and there is no need to enter new formulae at that time.

 

Cell E 5: Enter the formula:     =C5*D5         Refer to item 2.2 and enter the formulae in cells E 6 to E 14 incl.

 

( Should SKC in row  6 be completely sold, then enter the letter:     A      in cell  A 6, leave:   30/06       in cell B 6.  Enter number:     0     in cols. C 6 to D 6 incl. only ).  Do not delete anything in column E as the result =0, is still based on a formula. If any data in say cell E 5 were deleted, then re-enter the formula:  =C5*D5 in that cell ).

 

In cell G 14, enter the formula:    =SUM(E5:E14)   As soon as you press: Enter, the answer appears = 330100. That is the total investment in recorded NZ stocks.

 

To check the work or to correct any errors: Click on View ( See top toolbar ). Click on Formulas and a spreadsheet with formulae appears. Return to View, click on Formulas  and the spreadsheet with numbers returns.( At any stage, the work with the numbers can be saved or printed. The page with formulae should only be printed and be stored for future reference eg. to correct an error.  

 

Note that if you wanted to add say another two rows, ie the last row is number 16, then you would need to delete that formula in cell G 14 and enter a new one in cell G 16:    = SUM(E5:E16)    As a consequence, the location of further entries and the references of formulae will also be changed.

 

4.3 Foreign Company data and Formulae, using font Arial, size 10. 

4.3.1 Australian Companies. Rows 17-26 incl.

    

Cells D 16 and E 16, enter ( Capitals):   $ A       and in cell F 16 enter:    Rate         Starting with cell A 17 and across, enter:  LACO       30/9         5000      3.3        .Refer to item 2.1 and enter letter:    A    in cells:  A 18 to A 26 incl.   Enter:   30/9    in cell B18 to B26 incl. Enter number:   0    in cells C 18 to C 26, incl.   Also in cells D 18 to D 26 incl.

 

 Cell E 17, enter formula:     = C17*D17     ( A result of 16500 is shown ). Using the procedure in item 2.2: drag this formula down just past cell E 26 incl.. 

 

The Teletext on page 384 will give you the wholesale mid - exchange rate in 4 decimals. Assume that it is  0.8077. Enter this in cell F 17 and drag this just past cell F 26 incl. Note that columns F 17 to F 26 always need this - or any other mid - rate. Without it, ERR messages appear in the G column. 

 

Cell G 17, enter:     =E17/F17       and ( see item 2.2 ) drag this down just past cell G 26 incl.    

Cell G 27, enter:     =SUM(G17:G26)          Result: 20428.36787  This is the total value of Australian stocks in $ NZ. 

 

4.3.2 Other Foreign companies: Rows 29- 36 incl. For the time being, we assume that these are English stocks.

Cells D 28 and E 28, enter the word:       Pounds       Cell F 28, enter the word:   Rate 

 

Cell A 29 and across, enter:   ABC    31/12       3000     3.15    . Enter number:    A     in cells  A 30 to A 36 incl. Enter:   31/12    in cells B 30 to B 36 incl. Enter number     0      in cells  C 30 to C 36 incl.   Also in cells D 30 to D 36 incl.  

 

Cell E 29, enter:    =C29*D29       drag this formula ( See item 2.2 )  just past cell E 36 incl.   Cell F 29, enter: the wholesale mid- exchange rate in 4 decimals: Say, it were 0.2904. Drag this past cell F 36 incl.  Cell G 29, enter:     =E29/F29     Drag this formula past cell G 36 incl.

 

Cell G 37, enter:     =SUM(G29:G36)       Result: 32541.322

Remarks: You may have several currencies in this group. In that case, you may want to delete the data ( Pounds etc.) from row 28. Column F will then have more exchange rates. Any spare cells in this column need a notional exchange rate entered, as otherwise the message ERR will occur in the G column. 

 

 Cell G 39, enter:       =SUM(G14+G27+G37)      The result= 3,883,069.699   this is the total of your 3 sets of investments expressed in NZ dollars.

 

Cell B 39, enter the word:      DATE            Cell C 39, enter say:    10/8/01    Cell D 39, enter: TOT.$NZ  

Cell F 39, enter:    STOCKS         Cell I 39, enter:     CASH     Cell J 39, enter:    1500000          Cell L 39, enter:      LOCO      Cell M 39, enter:  200000       Cell E 39, enter:    = SUM(G39+J39+M39) 

 

The result = 3883069.699 and this is the total of all your recorded investments in $NZ .

 

Remarks: Cell J 39: This is the sum total of all cash holdings at banks, brokers or wherever, converted to $ NZ.    Cell M39: Total investments ( in $ NZ ) in Local Bodies, ports debentures and other instruments.

 

Enter the number      0      in any of these 2 cells if you don't have any numbers to enter. These two cells can be used for totals of other investments. 

 

Cell T 39 has no entries at present. The whole column may be used for entry of coded financial data and the sum in $ NZ can be entered in cell T 39. If this were done, then change the formula in cell E 39 into:        =SUM(G39+J39+M39+T39)      

 

In that case, should  later on, cell T 39 have nil value, then enter number    0     in that cell. Alternatively, change the then formula in cell E 39. 

 

 5. Other. Suggest you save this spreadsheet on a floppy disc.This, together with printing of this spreadsheet and formulae can be referred to in the future. Obviously, once you overwrite or enter your own data, you will need to check the results with a calculator from time to time.

 

Gerry

---

 I cannot be held responsible for any errors in this email or for any damages arising from the reading of and use of this text and spreadsheet. Any outcome will be entirely at the reader's or user's own risk.