UNIT 3 CALCULATIONS in MS EXCEL

MODULE 5

UNIT

1 2 3 4

SESSION

1 2 3 4 5 6 7 8 9

10 11 12 13

Session 8-9 CREATING AND FORMATTING an INVOICE

• Entering the text data 84

• Finding total and Vat 90

•

• Adding and deleting rows 95

CREATING AND FORMATTING AN INVOICE

INTRODUCTION

Every entrepreneur who sells goods or services has to give his or her clients a document bearing the name of INVOICE or cheque. Usually, an invoice consists of two equal/similar parts. One of them is given to the client, the other remains to the entrepreneur.

The following data have to be written on an invoice:

• Name of the seller, his/her address and registration number or ID code;

• Number of invoice and the date of issuing it;

• Buyer’s name and address;

• Name of goods or services, quantity, price in EEKs and the total price of goods and/or services without VAT and VAT included;

• Date of delivering the goods or services in case it differs of writing /making out/composing the invoice; Signature of the private proprietor selling the goods/services.

ENTERING THE TEXT DATA

We will create the invoice in MS Excel table because there are always quantities and prices expressed in numbers on the invoice that have to be multiplied or summed up. You can find Excel Program in Start menu.

Î Î

Try to make up your first invoice after the sample. Afterwards when you have more experience you can make up your own invoice just as you like

.

Invoice is made up of two exactly similar parts/halves – one of them remains to the vendor (seller), the other is given to the customer (receiver).

You can create your invoice in Excel using its properties to

automatically change the data in the second half, if you alter some data (about customer, quantity of goods ...) in the first half.

Design the invoice so that both halves of it can be placed on A4. Firstly, draw up the first half, then copy it beneath the first and you get the second half of the invoice.

You write the order number of your invoice in the cell C2 (this is No 1); later the number increases according to how many invoices you have written.

In the cell E2 you write the date if issuing the invoice. If you wish the date to be renewed automatically every time when you write the next invoice, you should use the Excel automatic date function in the list of functions. Either find and press Function in the Insert menu, or toolbar button Paste Function .

1. 2.

The window of function categories opens. In the left column there is the list of function types (statistic, financial...), on the right lately used or most used functions.

Name of the automated date function is TODAY. Click on it an press OK. If there is no Today function on the right options list, choose All at the left and find it in the alphabetical list. A new box opens, click OK, and automated date is entered.

You have to open Print Preview for a while to understand where Excel page’s left margin and the bottom of the page are. So click on the

Print Preview .

To get the normal view back, click on Close. Now you can see a discontinuous line at the right margin of the page (in the chart between I and J). The same kind of line has emerged at the bottom of the page. These discontinuous lines show the printable area of the sheet. If you write something beyond these lines it will be printed out at another page. Always check this in the Print Preview!

Leave the third row empty. Now write your name in the cell A4 and press Enter. Usually the name is too long for the cell. Now you have to make the cell /column wider. Move the mouse pointer up between the separating line of the two columns. The pointer changes to the two-pointed arrow. Keep down the left button of the mouse and drag the column wider.

Write your private entrepreneur’s registration code (given by the Internal Revenue Office) in the cell B4. Beneath it write your ID code in the cell B5, and your address in B6. Drag this column also wider, like you acted with the name cell. You cannot drag the cell if you have not finished entering the text, i.e. the pointer cursor is blinking in the cell. To remove blinking, simply press Enter. When dragging

the columns wider, look after the right margin of the page - the date cannot be moved over the border. In the sample invoice (Chart 5.7.2) the date is at the right margin of the invoice. Try to place it at the same place dragging the columns – making some wider and some narrower. If something goes wrong or gets lost, press the Undo button on the toolbar at the top. By pressing this you cancel your latest moves.

The name (RUTS METS) and INVOICE No are in bold text on the sample. If you want to change the size, colour and shape of your text data, you have to first bespot the cells where you want change. In order to do it, move the mouse pointer on the cell and see that the blinking pointer changes to a large white cross . Click there – and the cell’s border line turns bolder. Now choose the bold (darker) character on the toolbar at the top.

You can change the size of the character just beside the previous button, clicking on the Font Size button . Choose size12.

If you want to select two neighbouring cells, move the mouse pointer on the first cell you want to select. Now drag over the needed cells, keeping the left mouse button down. When you loose the button, the selected cells turn blue (with the older program version they turn black), only the first selected cell always remains white. It always acts the same way!

Thus you can design several cells in similar way at the same time.

Now write the name of your bank in the cell E5, and beneath it the number of your bank account where the receiver of this invoice has to transfer the sum of money noted in the invoice. Now select both cells and format the text into italic , and align to right .

The text in the cells A8 and A9, the Payer and the Address are also in italic and right aligned.

In cells B8 and B9 there are data about the receiver of this invoice, the payer. You can write imaginary data there for the time being. Design according to the Chart 5.7.2. If you want to change the background colour of the cells, first select the cells.

You can choose colour on the toolbar at the top where you can find the paintpot button. .

See to it that you click on the tiny white triangular beside the

paintpot. The colour options open to you there. If you have a black-and-white printer, there is no difference whether you choose some shade of gray or another light colour – they all print out as gray. You only have to avoid too dark a background – the text will not be seen against that. Here in our sample the light gray has been chosen. You can shade the 8th and 9th row and the other cells in the same way.

Write the remaining part of the text and the numbers in the same way as shown in the example; excluding numbers that depend on the primary data – Subtotal, VAT , Total sum. Use all the formatting skills used before.

Let’s add printable lines next. As you saw in the Print Preview , the gray guiding lines, gridlines are usually not printed out. You have to choose now which lines you want to print.

Select the cells you want to have gridlines around and/or between them. Begin with the upper shaded area. This area has a thin border line around it.

You can select all needed lines on the upper toolbar by clicking on the tiny triangular beside the Borders button.

Different border and gridline options open: erases all lines

places bottom borderline to all selected text places left borderline to all selected text places right borderline to all selected text

places bottom double border to all selected text

you can already guess what kind a borderline will be printed

the most used style; adds outside borders around the whole selected area and gridlines between all rows and columns.

add only outside borders around the selected text.

As there are no printable gridlines needed with this shaded text; we need here only outside borders, select .

You have to keep a keen eye on where to further add printable grids. And do not forget the Undo button , if something goes wrong!

Remember: the gray gridlines between cells are not printed out, unless you add them!

Now write the name of commodities or services you are selling in the corresponding cell (e.g. Lambskin slippers). The column for

commodities’ names has to be wider, provided some names are longer (e.g. Small butter knife, made of juniper). Enter also the quantity.

Write the price, without decimals. You may also try to write 145.00, but most likely it is rounded to 145, because accounting sheets are usually formatted to display no decimal places. To add decimals you have to first select the cells where you want to display decimals. You may also select some other cells where you will later enter numbers with decimal fractions. And press Increase decimal button. Every mouse clicking on it adds a decimal place.

To remove or lessen the number of decimals places, you click on

Decrease decimal button .

If clicking on Increase Decimal does not work, there must be a data entering mistake, i.e. in some cases Excel does not understand that a cell contains a number (and you cannot add decimals to text data). If the content of column is left aligned, it implies that a mistake is made when entering the data and Excel does not read the entry as a numerical field.

Save the work that you have done!

FINDING TOTAL AND VAT

An Excel cell does not have to contain only numbers or text; it may also contain a formula that calculates the sum, multiplication, division ... of numbers in other cells. We have to find out the cost of the goods that we have ordered, taking into account their amount. So we have to multiply the price of goods by their number. To enter the

formula, click first in the cell where you want to see the sum (E12). Every formula in Excel begins with the equal sign. Find the equal sign on the keyboard on the same key where zero is. Keeping down the Shift key press equals key. The equal sign appears in the cell where you are entering the formula. Then click on the number you want to multiply (number 3 in cell C12); then press multiplication key at the right side of the keyboard (there is * or x on the key) ; then click on the other number you want to multiply by (145,00 in the cell D12). The following formula must appear: . If you had numbers in the different cells, you do not have C12 or D12 in your formula, but accordingly some other addresses of cells. Finish entering the formula by pressing Enter. To add decimals to your sum, click on the cell and then on Increase decimals button . You do not have to enter the formula again for calculating the rest of sums. You can simply copy the formula.

For copying it, first click on the cell where you just got the answer; then move the mouse pointer down to the right corner of the same cell until it turnes to a tiny black arrow ; Now keeping the mouse button down, drag to the VAT cell.

As you noticed, the empty cells in the Total column were filled with zeros (0,00). It happened because you copied the formula also into these cells. Afterwards, when you fill the Quantity and Price cells, the total sum will calculate itself automatically. Try it!

To calculate VAT you have to first sum up the numbers in price cells and multiply the sum by 18%. First mouse click on the cell where you want to see the number that indicates the amount of VAT (here E19); then press equal sign and write SUM (you may write the word „sum“ in lower case also); now press the Bracket key ( then write the cells range where the percentage is calculated E12:E18. You may also drag the mouse pointer over the according area. Now write another,

closing bracket); multiplying sign and the number 18. You find percentage sign % on the same key where number 5 lies. The formula must look like this . Press Enter to complete the formula.

To find out the Total sum, you have to select all cells,

and press AutoSum button .

If you want to get the kroon’s symbol, you have to select the according numbers first. And click the Currency Style button . You will find the symbol kr in case you have Estonian regional settings in your computer. (How to change settings you will find at the end of the chapter.) To make the total sum look more clean-cut, round the monetary data up to one decimal place .

Total in words you could write yourself as text. But you can also find a function in Internet that writes the number as text.

Leave the gray cells empty after Issued by and Received by. Signatures of the persons who made out the invoice and who accepted it will be written there afterwards.

THE SECOND HALF OF THE INVOICE; COPYING THE

INVOICE

Now copy the first half of the invoice on the same page, beneath itself. For that highlight the first part of the invoice and press Copy.

.

Then click in the beginning cell of the second half (A32) and press

Paste . As the result you have two absolutely identical invoices.

To avoid double work when you are working with your invoices, i.e. avoid filling in two similar halves, you have to make the cells of the lower part change at the same time when the data are changed in the upper part. Variables in our invoice are: Number of invoice, payer information, names of commodity items, quantity, price. Now you have to enter a formula to enable simultaneous changes in the second half of the invoice when you enter a new payer’s name in the first half. Click in cell B39 (OÜ Puri) and equalize it to the

according cell of the upper part - B8.

The formula is . Now do the same with the payer’s address cell, i.e. B40 equalize to B9. The number of invoice also keeps altering, so make it equalize, too. Do the same with the name of commodity. Make cell A43 equalize to cell A12. Then copy the formula

downwards, because the next changing cells are situated

cursor in the lower right corner of the cell surrounded by black

borderline turns into a tiny black cross. Keep the mouse button down and drag downwards.

Î

The cells that were empty in the upper half are filled with zeros. But as soon as you fill the upper half with the names of commodities, zeros are replaced by text.

Do the same with the cells of Quantity, Price and Total sum. Do not forget to equalize the cells of Total sum in words.

If you nevertheless do not wish to have zeros in your table, you are able to prevent them to be shown on the screen. For doing so, open Options... in the Tools menu.

You can specify on the View chart what must be shown and what not. Click on the tick, removing it and thus ordering not to show Zero

values Î .

Save your competed work in the specific folder.

ADDING AND DELEATING ROWS

When you make up your invoices, sometimes you need to enter more names of commodities than on the initial invoice. To add rows click on the gray number at the beginning of a row. The new row is always added before the selected row.

And, keeping down the Control button Ctrl , press Plus button .

Sometimes you may need to delete several empty rows. To delete rows, first highlight the whole row above the number. Keeping down Control button, press Minus button.

To save every new file, open File menu and click on Save

As... . You may, for example save every file under the name

of invoice number. This way you always have the initial version- template, and all the invoices that you have made up.

MODULE 5

UNIT

1 2 3 4

SESSION

1 2 3 4 5 6 7 8 9

10 11 12 13

CALCULATIONS

• Writing and designing

the table heading 99

• Entering ordinal numbers 101

• Entering the date 101

• Entering monetary data 102

• Calculating sums automatically 103

• Sorting the data according

to the date 104

• Printing 105

MONTHLY PROFIT-AND-LOSS CALCULATIONS

DAYBOOK

INTRODUCTION

You can create your own simple accountancy in MS Excel in order to have a survey of your monthly incomings and outgoings.

Bookkeeping was long ago invented for the reason that businessmen who were active in merchandise wanted to get a survey of their profits and losses. Nowadays single proprietors have to do bookkeeping for the same reasons, and also because they have to submit their annual income statement to the State Treasury Department.

Accountancy can be cash based or accrual based.

The cash based accounting reports income only when it is received and deducts expenses when they are actually paid.

The accrual method reports income when it is earned and deducts expenses as they are incurred, regardless of whether the money has actually entered or left the business yet. As soon as you have bought goods or services, you have to deduct expenses at once.

Cash based accounting can be made/kept by a sole proprietor whose annual sales turnover of goods and services for the previous calendar year do not exeed 250 000 kroons which is now in Estonia (March 2002) the limit for exemption from taxes.

Cash based accounting is not labour-intensive and is quite easy; every entrepreneur should be able to do that. The cash based

accounting is especially easy with the help of MS Excel. You should fill the earnings and outlays table at least once a week. If you deliver goods or services rarely, once a month is enough. This kind of reckoning is also called daybook keeping. All earnings and outlays occurred in your entrepeneurship that have documentary evidence are entered in the daybook.

PROFIT-LOSS SUMMARY TABLE

(RECKONING YOUR OUTGOINGS AND INCOMINGS)

Î Î

The following entries should appear as column headings in cash based accounting.

Date – the date when you received money in your account or transferred money yourself;

Document number – number that every incoming or outgoing document bears on it;

Received from/Payed to – here you write the name of the firm who paid/transferred you money or whom you yourself

paid/transferred money;

Address – write the address of the firm.

Incomings – money that has entered in your account.

Outgoings – money that has been transferred to sb. or payed for sth.

Explanations – you may specify what you spent the money on; or add some explanatory remarks about some action (e.g. when you distract 20% off the petrol cost)

WRITING AND DESIGNING THE TABLE HEADING

transactions you have done. Click in the cell A 1 and type the word „No“. Then press the Right Arrow button to move to the next cell

No“. In the cell D1 write „Received from/Payed to“. As you can see, the string of words is too long and stretches over the columns E and F.

When you have finished entering the text, press Arrow or Enter and drag the cell until all the text is in it. To do this, move the pointer cursor between the D and E cells. When the cursor turns into two-headed arrow , keep the mouse button down and drag to make the cell wider. You can even make it wider than needed for this text, because the names of firms may be even longer.

You can naturally change the width of the columns any time when you need it. If you want the text to fit in the column, double-click between the two columns .

Also enter the words Address, Incomings, Outgoings,

Explanation into the appropriate cells. Change the width of these columns, too. If you want to change the background colour, select the cells first. In Excel worksheet, the first selected cell always remains white!

Selecting the text, watch the pointer turning into broad white cross , and then drag over the cells, keeping the mouse button down. You can choose the background colour on the toolbar where a button depicts a paint can . Click on the tiny triangular at the right of the paint can. Colour options open to you. Click on the colour you like.

If something goes wrong or gets lost, click on the button Undo on the upper toolbar. You can also make the text in the table headings bold. Select the necessary rows and click Bold .

Now save the work that you have done. That means press Save

button. Give your file a name and

ENTERING ORDINAL NUMBERS

You need not enter manually e.g. 56 or 100 numbers one after another. You can enter the ordinal numbers automatically with the help of Excel. It’s enough to enter the first two numbers, 1 and 2 under each other. After that select both numbers. Now move the pointer downwards to the right corner of the selected area. The pointer cursor turns into a small black cross .

Î Î

Keeping the mouse button down, drag downwards. An appropriate number appears on the yellow background next to every row. Release the mouse button when you have enough rows.

You can also add numbers afterwards, selecting the last two numbers; e.g. select 8 and 9 and drag the small black cross downwards.

Don’t forget saving the work!

ENTERING THE DATE

In the Date cell write the date when the money actually entered your account. As you keep the Daybook for the current year only, you need not write the year; date and month is enough.

It is not important what format you use for entering the dates – 4th Jan or 4.01 or 04 January ., 4.1 or 04.01 - , Excel formats it automatically to 04. Jan. after you press Enter. And you can see the complete date format with the year number above the row. . This is simply the date format for Excel. You do not have to enter the data in the date order. Sorting the data

Enter the numbers of documents in Doc. No cells. You can find a number on every invoice. The name and the address of the firm are also always written there.

ENTERING MONETARY DATA

You have to pay attention to the columns Incomings and

Outgoings - enter the sums of money that you have received or paid to somebody under the right headings. There is no need to enter first all income sums and after that all outgoings, because the total sums will be calculated separately under the appropriate columns.

CALCULATING SUMS AUTOMATICALLY

To sum up all the incomings you have to select the cells with the income money numbers (do not select the word Incomings). Select also the empty cell where there should be the total sum. Then press the button AutoSum on the toolbar at the top.

Î

Do the same with the Outgoings. These are the sums you have to write in your income statement on the entrepreneur’s formular. To

make the Totals more conspicuous, make them bold. You can also write the word TOTAL before the line.

If you calculated the sum just after the last row, and you happen to get new data after that, you need to add an empty row before the sum row. To add a row, click on the gray number in front of the row.

The new row will always be added before the selected row. Keeping down the Control key Ctrl , press the Plus key .

If you wish to add three rows at a time, you should also first select three rows.

Don’t forget to save your work!

In order to be able to see the difference between incomings and outgoings on the screen, you can calculate that on a separate line. Click in the cell where you want to write the sum.

Every formula in Excel begins with the equals sign. You can find the sign (=) on the keyboard on the same button with zero. Keep down the shift key and press 0 - the equals sign appears in the cell. Then click in the cell with incomings total, then click on the minus key

, and after that click in the outgoings total cell. To finish entering the formula, press Enter.

Î

If you have used the formula once, you do not have to do it again. When you add new lines and fill in the new data, the total and the difference are renewed every time automatically. So you have a current overview about your spending.

SORTING THE DATA ACCORDING TO THE DATE

It is not important to enter your data according to the date at once. You can sort the data whenever you wish – at the end of the year, month ...

To sort the data, first select your table, leaving out the first, i.e. the ordinal numbers column, and the Total sum row.

In Data menu select Sort ...

A window opens where you can choose sorting options according to which your data will be sorted. The text in the table heading is seen under Sort by options.

Select here Date

Besides, you can also select, if you want to sort in (1,2,3,....) or (10, 9, 8...9) order.

If some entries bear the same date, you can determine another option: Then by.

And lastly, press OK .

Save your work and make a back-up copy, just in case. Open File menu and choose Save As... . Give the copy a different name.

PRINTING

PRINT PREVIEW

If you choose to print out your Daybook, look in the Print Preview first.

You can only see a part of the table in Print Preview. As the rows in the table heading are long, they cannot be placed on one sheet. Try

to pay attention to the information at the bottom of the page. It says

how many pages you have .

To see the next page, click on the button Next at the top of the page.

The next page with the rest of the data opens.

You can get back to the first page by clicking the button Previous.

You have to change the page orientation in order to place the whole table heading. You can find the page format information in Print Preview clicking the button Setup

You can see two possibilities for paper orientation at the top of the window: Portrait and Landscape. Click on the bullet by the word

Landscape, then press .

The whole table heading should fit on one page now. If you have many entries, you naturally have more pages. But the heading should be placed on one page.

PRINTABLE LINES/GRIDS

If you wish to draw separating lines between columns and rows

before printing, you have to close the Print preview. Then select the whole table, press Borders button and choose the lines that you need.

Choose the second sign in the bottom row - All Borders . In this case you get borders around the table and everywhere in between.

Clicking now Print preview you can see the added lines.

To print your work out, click Print .