Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and icon

Instructions for practical training on "Informatics and Computer Technique" " The use of ms excel 2003 for financial analysis and




Скачати 229.93 Kb.
НазваInstructions for practical training on "Informatics and Computer Technique" " The use of ms excel 2003 for financial analysis and
Сторінка1/9
Дата26.05.2013
Розмір229.93 Kb.
ТипInstructions
  1   2   3   4   5   6   7   8   9

Ministry of Education and Science of Ukraine

Sumy State University


METHODOLOGICAL INSTRUCTIONS

for practical training

on ”Informatics and Computer Technique”

The use of MS Excel 2003 for financial analysis and

decision making

for foreign students of the Department of

Economics and Management


Sumy

“Sumy State University Publishers”

2010

Methodological instructions for practical training on ”Informatics and Computer Technique” on the topic “The use of MS Excel 2003 for financial analysis and decision making”/Compiler A.S. Kornyushchenko – Sumy: Sumy State University Publishers, 2010. – 51 p.


Modeling of Complex Systems Chair


CONTENTS


INTRODUCTION 4

1. Entering of data and formulas into worksheet 5

2. Simple spreadsheet creation 9

3. Worksheet formatting 13

3.1 Font type, size and style choosing 13

3.2 Text alignment changing 14

3.3. Addition of borders to cells 15

3.4 Colors and patterns application 16

4. Excel formulas and functions 17

4.1. Relative versus absolute cell references 17

4.2. AutoFill and series features 23

5. Conditional formatting of worksheet 29

6. Chart creation and charts elements manipulation 33

6.1. Column chart creation 34

6.2. Chart elements manipulation 36

6.3 Pie chart creation 37

6.4. Line chart creation 40

6.5. Advanced chart 44

7. Document previewing before printing 46

7.1 Document margins adjusting 47

7.2. Center on page 47

7.3. Sheet printing options setting 47

7.4. Adding of header and footer to the reports 48

7.5 Printing of selected area of spreadsheet 50

7.6 Page orientation changing and document scaling 50

REFERENCES 51



INTRODUCTION



Spreadsheets as tools for financial management


Computer-based management information systems (MISs) are management tools that facilitate team-work. MISs collect detailed data on a variety of costs, how well services and goods satisfy quality requirements, how well customers are satisfied, and other criteria used to evaluate business operations. They accumulate the data in huge databases. Spreadsheets and special software are used to withdraw values from the databases and convert them to information, and then assemble the information in the form of reports, tables, and charts.

Information technology and MISs have expanded the boundaries of teamwork. Nowadays, members of international teams communicate in computer-based languages they all understand, draw data for analysis from common databases, and exchange information at electronic speeds. Spreadsheets are much more than sophisticated calculators. They are of great for both communicating and calculating. They can help provide transparency into a corporation’s workings. They are easily incorporated into reports and management presentations.

Today, the functional elements of large corporations are linked together by system of computers and software called enterprise management systems. The largest and best known of these are ORACLE and SAS. Such systems do the “heavy lifting” for managing corporate-wide operations. Yet, even in corporations with such systems, many managers have installed Excel on their office computers and use it for accessing information from corporate databases, analyzing it, and preparing reports. Excel is entirely adequate for handling many business problems. It is more convenient, more accessible, and less costly than enterprise management systems.

We will consider using Excel on the example of building income statement. Income statements provide a financial summary of a firm’s operation for a specified period, such as one year ending at the date specified in the statement’s title. They show the total revenues and expenses during that time. An income statement is sometimes called a “profit and loss statement”, an “operating statement”, or a “statement of operations”. Essentially, it tells whether the firm is making money or not.

^

1. Entering of data and formulas into worksheet



To open Excel click Start button on the taskbar. Than point to Programs and from opened list choose Microsoft office and click Microsoft office Excel 2003. When you start Excel, the program window opens with a new workbook so that you can begin working in it. The default name of Excel workbook is Book 1. Study elements of Excel window. Click on the menu items to reveal a submenu of actions, each of them can have a sub submenu of actions and so on.

The workbook is arranged as series of worksheets. The default worksheet has three new workbooks called Sheet 1, Sheet 2, Sheet 3.

  1. Delete Sheet 2 and Sheet 3:

    • right click on Sheet 2 tab, context menu opens;

    • from context menu select Delete;

    • repeat the same actions for Sheet 3.

  2. Rename Sheet 1 and give it name Budget Summary:

    • right click on Sheet 1 to open menu;

    • from context menu choose Rename;

    • type new name of the workbook Budget Summary.

  3. To make your worksheet easier to read on the screen, you can zoom in and out. In Zoom drop-down list on the Formatting Toolbar choose 150% to make your worksheet easier to read.


Example 1 (Explains entering number, label and formula into cells)

Once you have created a workbook, you can begin entering data. The simplest way to enter data is to click a cell and type a value. You can enter three kinds of data into Excel cell: numbers, labels, formulas.

1. Entering a number

Make cell B1 to be active and type number 123.45 and press Enter. Notice that active cell changes when you press Enter, and now B2 is active. Make B1 active again and you can see it’s address in the name box and it’s content in formula bar.

2. Entering a label

English expressions called labels are the second kind of information. Click on sale A1 and type expression Unit price. As you type, the label appears in the formula bar. Press Enter. Now in cell A2 enter a label Units sold and in cell A3 Total Sales. Suppose that we’ve sold 25 units. So, enter 25 in cell B2.


3. Entering of formulas

The third kind of information is formula. We can find Total Sales using the formula: Total Sales = Units sold * Unit price.

To enter the formula, first make cell B3 active. Start formula with the sign of equality. There are two ways to enter cell address in formula. The first way: type B1, then multiplication sign *. The second way to enter a cell address is by clicking on corresponding cell. To complete data entrance press key which is placed between Name box and Formula bar. Note, that entrance is completed and cell B3 is still active.

Note!!! If you have made a mistake entering data into cell, press Esc key or Cancel button on the ^ Formatting Toolbar. A very useful button on the Standard toolbar is Undo button for undo the last action that you have done. For this purpose you can also use context menu: right click and choose Clear content.

In the cell we see numerical value, which is a result of the calculations, but the formula bar shows the actual cell content B1 * B2 (figure 1). Change value of Unit price to 130.00 and pay attention that calculated value of Total sales is changed automatically.



Figure 1 – Total sales calculation


In formula we can use the usual mathematical operations: addition, subtraction, multiplication, division, exponentiation (table 1).


Table 1 – Mathematical operations in Excel

Operation

Key

addition

+

subtraction

-

multiplication

*

division

/

exponentiation

^









4. Saving worksheet

Save your workbook with name Spreadsheets_your_name.xls. Open menu File then select Save as submenu. In Save as dialog box specify file name and choose location of your file on disc System_English in your group folder. Click Save button.

Set the Save AutoRecover Info Every 5 Minutes to save automatically data while you’re working so as to be able to recover from such disaster as Excel crashing or your computer losing power. This check box is selected by default and the default setting is 10 minutes. In Tools menu select Options to open Options dialog box (figure 2). In Save tab set value 5 minutes.



Figure 2 – Auto Recover features

Exercise 1

The exercise is to create a compound amount calculator, to answer the question: “If person invests original capital X dollars at I percent interest per period for n periods, how much will he have?”

Recall the formula from business mathematics for compound interest: The amount C that X will accumulate to in n periods at I percent per period is given by the formula:

Here X – original capital; I – interest rate; n – number of periods. In particular, suppose you have X = 2,000$ to invest at I = 6% per annum for n = 10 years.


Progress of work

  1. Inset a new worksheet: right mouse click on existing sheet Budget Summary and from appeared context menu select Insert. Insert dialog box appears (figure 3). In General tab click Worksheet and press OK. Rename a new Worksheet from Sheet 1 to Compound interest.




Figure 3 – Insert dialog box


  1. Enter the title Compound Calculator for your spreadsheet in cell A1.

  2. Enter the label Original Capital in cell A3. Change the column width to fit the label by dragging the boundary on the right side of the column heading.

  3. Enter the dollar amount 2000 for the original capital in cell B3.

  4. Enter the label Interest Rate in cell A4.

  5. Enter the value 0.06 in cell B4.

  6. Enter the label Number of Periods in cell A5.

  7. Enter the value 10 in cell B5.

  8. Enter the label Compound Capital in cell A6.

  9. Enter the formula for the compound amount in cell B6 (Figure 4).

  10. Change the interest rate to 10% to find the new Compound Capital. Use UNDO to return to the previous 6% interest.

  11. Save your workbook.




Figure 4 – Compound calculator worksheet


Assignment 1

  1. How to start Microsoft Office Excel? How to open a new workbook? How to open an existing workbook?

  2. Name the elements of Excel window.

  3. How to move around workbook? Name keys for navigating in a worksheet and their function.

  4. How to select cell? How to select contiguous and non-contiguous range of cells?

  5. How to edit cell content? How to clear cell content. Undoing and Redoing actions.

  6. How to save workbook? How to customize AutoSave options?

  7. How to enter labels, values and formulas on a Worksheet?
  1   2   3   4   5   6   7   8   9

Схожі:

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconInstructions for practical training in "Informatics and Computer Technique" on the topic "

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconAnalysis of pharmacies financial condition analysis of financial stability financial stability of the enterprise

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconAnalysis of financial economic activity pharmacies
Аnalysis of the financial performance and profitability of pharmacy learn to carry out
Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconInstructions for practical classes

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconThematic plan of practical training

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconThematic plan of practical training

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconThematic plan of practical training classes

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconThematic plan of the practical nursing training

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconInstructions for practical studies on the discipline "History of Medicine"

Instructions for practical training on \"Informatics and Computer Technique\" \" The use of ms excel 2003 for financial analysis and iconInstructions for practical studies on the discipline "History of Medicine"

Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©zavantag.com 2000-2013
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи