Скачати 229.93 Kb.

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 INTRODUCTIONSpreadsheets as tools for financial management Computerbased management information systems (MISs) are management tools that facilitate teamwork. 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 computerbased 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 corporatewide 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. ^ 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.
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 ^ . 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
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
Figure 3 – Insert dialog box
Figure 4 – Compound calculator worksheet Assignment 1
