AG215 Lab Workbook 2021 v PDF

Preview

Summary

AG215 work...

Description

Department of Accounting & Finance

Business Finance AG215 Lab Work using Excel

Index Chapter

Page General Guidelines

2

1

Introduction to Microsoft Excel

3

2

Financial Formulas

36

3

Data Handling and Presentation

57

4

Capital Budgeting

88

5

Sensitivity Analysis

100

Version 2021.2

Department of Accounting & Finance

Undergraduate Finance

1

General Guidelines You should always save your work regularly, either to a flash-drive or to your H drive space (especially when you are on campus) or OneDrive. Keep a copy of all your work at least until the end of the semester.

The data required for this workbook is contained in AG215 - Workbook Data.xlsx and is available on Myplace. You will be directed when to use one of the worksheets in this file, and when to create your own workbook files.

Due to social distancing requirements, the lab sessions in weeks 2 & 4 will be replaced by Zoom sessions with the tutors. You should sign up to a Zoom session and only join in with that session.

You should work through the workbook in your own time and use the Zoom sessions and labs time for any questions you may have.

There will be a quiz placed on Myplace for each chapter in this book. You MUST complete these quizzes.

You should work through all exercises and activities, but none of the work needs to be handed in. The scope of the class test questions will cover everything shown in the workbook.

Lecturers: Patrick McColgan

Tutors:

[email protected]

Office hours:

See Myplace

Martin Kemmitt

[email protected]

Office hours:

See Myplace

Donald Campbell

[email protected]

Office hours:

See Myplace

Department of Accounting & Finance

Undergraduate Finance

Stenhouse 3.24

Stenhouse 3.03

Stenhouse 3.04

2

Business Finance

1

Chapter 1 – Introduction

Introduction to Microsoft Excel

This chapter serves as an introduction to Microsoft Excel. Different versions (PC/Mac) may have slight differences in the ribbons and look-and-feel, but otherwise the functionality remains the same as described here. This chapter will show you how to navigate around the layout and explain where most of the common features are now located.

Objectives: On completing this chapter, you should be able to… •

Navigate around the Excel interface, e.g. ribbon and menus

•

Enter different kinds of data, e.g. text, values, dates and times

•

Select ranges and manipulate data, e.g. copy, cut and paste

•

Edit and format data, e.g. decimals, currencies and dates

•

Work between different worksheets and workbooks

•

Use Excel’s built-in functions and create your own

•

Use the IF function to produce conditional results

Department of Accounting & Finance

Undergraduate Finance

3

Business Finance

Chapter 1 – Introduction

1.1 Getting started When you start up Excel by double-clicking on the Excel icon, or by selecting Excel from the Start : Programs : Microsoft Office menu, your screen should look something similar to that shown below. The icons on the toolbars may be in different positions, or apparently missing, but don’t worry about this at the moment.

Figure 1.1

In previous versions, worksheets were quite limited in the amount of data they could hold. Since version 2007, the number of columns has increased from 256 to 16,384 and the number of rows has increased from 65,536 to over a million.

Department of Accounting & Finance

Undergraduate Finance

4

Business Finance

Chapter 1 – Introduction

Let's have a look at the tabs that form the ribbon or toolbar above the grid area. The File tab/menu will be familiar to anyone who has used any other Windows or OSX application, so I'll just mention one useful File tab option which is... Options.

This allows you to customize the look and behaviour of Excel to your taste. For now, we won't change any of these settings, but when you've used Excel for a while, have another look at these and see if any of the options would make using Excel easier for you.

Figure 1.2

Figure 1.3 The Home tab. This tab contains most of the general formatting options along with quick links to several often-used options such as sort and find. In this workbook we will be concentrating on the Home, Insert and Data tabs.

We'll look a little more at the formatting sections in Chapter 2. For now, just notice that many of the icons have triangles beside them denoting drop-down menus containing more options associated with that function. Similarly, on some of the toolbar sections there is an arrow in the bottom right hand corner, as shown here:

Department of Accounting & Finance

Undergraduate Finance

5

Business Finance

Chapter 1 – Introduction

Clicking on the arrow brings up a dialogue box containing a full set of options, for example the Font arrow brings up the Format Cells dialogue box shown in Figure 1.4. You can also produce this by right-clicking on a cell and choosing Format Cells from the pop-up menu. Mac users can do this by holding down CRTL and mouse-clicking. Figure 1.4

Below the toolbar is the Name Bar/Formula Bar. The Name bar on the left-hand side is an indicator of which cell is currently selected in the main worksheet area. In Figure 1.5 we can see that we have cell A1 selected, that is Column A, Row 1.

Figure 1.5

To the right of this is the Formula bar, which is where any formulas, functions or values will appear for editing. The example in Figure 1.5 shows the formula contained in the current cell A1 while A1 itself shows the result of that calculation. You can edit a formula or value in either the cell or the formula bar. With very long and complicated formulas it's clearer to do this in the formula bar. The little

symbol to the left of the formula bar will

come in very useful later as this is a shortcut to the Insert Function wizard.

Figure 1.6 shows the bottom of the Excel workbook. Each workbook can have multiple worksheets and by default these are named Sheet1, Sheet2, Sheet3… To change from one worksheet to another, click once on the appropriate tab and that worksheet will come to the front. To change the name of a worksheet, double-click on the tab and type in the new name. The triangular arrows to the left of the tabs let you move through tabs when there are too many to be shown at one time.

Department of Accounting & Finance

Undergraduate Finance

6

Business Finance

Chapter 1 – Introduction

Figure 1.6

Activity 1a Creating a simple worksheet.

Select cell A1 by clicking on it. Type Company Name and press Enter. [ ¿ ] The selected cell should now be A2. If not, use the arrow keys to move to A2.

Type in the following names under the heading you have just created… Rio Tinto BP Tesco Scottish Power Capita Group Pearson HBOS Reuters Group Next Boots Group Notice that the title “Company Name” runs over the edge of the cell into the next one and so appears to be cut off. To resize the cell, move your mouse pointer up to the column headers ( | A | B | C | …) and place it at the line between columns A and B. When you see the pointer change from a white cross to a black cross with arrows on it, click and hold your mouse button down and drag the column edge to the right until you see the whole column title. If you double click on the black cross, the column will automatically resize itself so that it is slightly wider than the widest value in that column.

Now select cell B1 and type the heading Base Date. This is the date on which the company was first listed on the commercial service. Type in the following dates below the Base Date heading. Type them carefully, because you will be changing the format of them later. Department of Accounting & Finance

Undergraduate Finance

7

Business Finance

Chapter 1 – Introduction

If Excel changes the format of any of these dates by itself, don’t worry; it’s just moving it to the closest date format it recognises.

30-Dec-64 30/12/1964 30/12/1964 17-Jun-91 24 April 1989 20-Aug-69 30-May-97 6/1/84 30-Dec-64 30/12/64

Now enter the following data:

Figure 1.7

Let’s tidy up the appearance of our data. If you haven’t already done so, resize each of the columns so that all values are visible. Next, we want the column headings to stand out a bit more from the data. Highlight all the headings cells from A1 to F1 by clicking on A1 and without releasing the mouse button, drag the pointer across to F1. Now click the Bold icon (

). While all those cells are still highlighted, click on the Centre icon (

).

Now we can format the Base Dates column. Highlight all of the date cells (B2 to B11).

Department of Accounting & Finance

Undergraduate Finance

8

Business Finance

Chapter 1 – Introduction

With the Home ribbon selected, select Format on the Cells category, and then select Format Cells… at the bottom. Click on the Date category on the left-hand side and choose the format as shown below.

Figure 1.8 Figure 1.9

You should now have a neat table of data like this:

Figure 1.10

Now that we have our basic data set up, we can start to use it to calculate new data.

Department of Accounting & Finance

Undergraduate Finance

9

Business Finance

Chapter 1 – Introduction

Activity 1b Performing calculations, referencing and number formatting.

We have been given the Market Value of each company, and the Number of Shares Issued. From this, we can easily work out the share price by dividing one into the other. Unfortunately, there is a complication; the data service provides market value in millions of pounds, and the number of shares in thousands.

Rio Tinto Given Value Actual Value

Market Value 28967 £28,967,000,000

Number of Shares 1061069 1,061,069,000

If we are going to use these values in our calculations, we have to take these multipliers into account.

First, we need to amend the headings to show the multipliers. Change C1 to read Market Value (£M’s). Rather than typing it all in again, click on C1, then click on the formula bar and just add to the text already there. Similarly, change F1 to No. of Shares (000’s).

Now, to aid in calculation, we will place the multipliers on the spreadsheet. Type in A13 the heading Multipliers, 1000000 into C13 and 1000 into F13. Now enter a heading for a new column into G1: Share Price. In cell G2, type the following formula (the letters don’t need to be upper case, Excel takes care of that once you enter the formula):

=(C2*$C$13)/(F2*$F$13) This needs some explanation… The equals sign tells Excel to interpret what follows as a calculation. C2 is the cell containing the first Market Value figure, which we want to multiply with the adjustment multiplier we put in C13. Excel follows the normal arithmetic precedence rules – multiply before add, etc. – but it is good practice to group your calculations with brackets. Apart from making it look tidier, it makes finding mistakes a lot easier.

The second section multiplies the Number of Shares with its adjustment factor and this is divided into the first part of the calculation.

Department of Accounting & Finance

Undergraduate Finance

10

Business Finance

Chapter 1 – Introduction

So why do C13 and F13 have $ signs inserted into them? This is called absolute referencing and has to do with copying formulas. When we copy and paste a formula – using either the icons on the toolbar, the options under the Edit menu, or Ctrl-C and Ctrl-V on the keyboard – Excel will automatically change the cell references to match the formula’s new position, unless we tell it not to by using an absolute cell reference.

Click on G2, copy the cell contents, click on G3 and paste the contents there. Your formula should now look like this: =(C3*$C$13)/(F3*$F$13) You can see that it is now using the Market Value and Number of Shares for BP instead of Rio Tinto, i.e. the row number is now 3 rather than 2, but is still using the multipliers on row 13. This is most useful when you want to copy a formula to a range. Click on your new formula in cell G3. On the bottom right corner of the cell outline, you should see a little black square. Click and hold your mouse button on that square and drag downwards. You will see a range outline appear. Drag right down until you reach the bottom of your data (G11). When you release the button, your formula has been copied all the way down. You will use this method a lot, so take time to get used to it, and to using absolute and relative cell referencing. 1

The prices we have calculated are in £s, so change the format of the cells in that column to currency. Use the same method as you did to change the date format, but select one of the currency options. Your spreadsheet should now look like this:

1

Tip: To easily make a cell reference absolute, click anywhere on the cell reference in the formula bar and press the f4 key.

Department of Accounting & Finance

Undergraduate Finance

11

Business Finance

Chapter 1 – Introduction

Figure 1.11 Activity 1c Using multiple worksheets For the next stage in our analysis, we only need a subset of the data we have created, so we’ll copy what we need to another worksheet.

Look at the bottom of the workbook display where the worksheet name-tabs are. Doubleclick on the tab labelled Sheet1. This will highlight the existing text, which we can then overwrite with a new name. Call this sheet Data. Now double-click on the Sheet2 tab and call this Statistics.

Click back on the Data tab, then highlight and copy the following columns over to columns A-D on the Statistics worksheet: Company Name Market Value (£M’s) Industry Code Share Price When you copy the Share Price column, instead of the prices, you will see a column full of #REF. This is because the cell references have adjusted to suit the new position, but the data to calculate these prices aren’t present on this sheet. What we have to do is Paste Special.

Paste Special allows you paste just the values removing the underlying formula. Remember if you have to change any of the share prices later, you would have to change it in both sheets. This is fine if you have a small sample, but for a large data set it could be a problem. In this case, pasting the value is okay. Department of Accounting & Finance

Undergraduate Finance

12

Business Finance

Chapter 1 – Introduction

On the Home tab, click on Paste and you will see the dropdown menu shown in Figure 1.12. Hovering over these icons will show a tool-tip highlighting what they are.

Figure 1.12 Alternatively, you can right-click on the cell in which you want to start your paste. Again, you should select Paste Special from the bottom of the paste options section.

Both of these methods produce the same dialogue box as shown in figure 1.14

Figure 1.13

Department of Accounting & Finance

Figure 1.14

Undergraduate Finance

13

Business Finance

Chapter 1 – Introduction

This can be a very useful tool, particularly the Transpose option which can turn a row of data into a column and vice versa. In this case we need the Values to be pasted in, so click on that option and then on OK.

Your Statistics sheet should now look like this:

Figure 1.15

Next, we will use the Insert Function Wizard to create some simple statistics on this data.

Activity 1d Simple Functions Starting in cell A15 and moving down, enter the following headings: Average Maximum Minimum Median Std.Dev.

Now move to cell B15 and open the Insert Function Wizard. You can do this by clicking on the Insert Function icon on the ribbon.

You will see the wizard as shown on the following page. Take some time to scroll through the different categories and functions. As each function is highlighted, you will see a short description at the bottom of the wizard.

Department of Accounting & Finance

Undergraduate Finance

14

Business Finance

Chapter 1 – Introduction

Figure 1.16 You can either type in the cell range as seen below, or click on the button to the right of Sometimes Excel will fill in the range for you, if the place you are putting the average value is close to some numerical data.

To begin with we will use the Average function. Select this as shown here and click OK. Each function you choose will bring up a window asking for you to enter the specific arguments that function needs. In the case of the Average function, we need to enter a list or range of numbers or cell references the range box, which allows you to highlight the cells you want in the spreadsheet itself.

Department of Accounting & Finance

Undergraduate Finance

15

Business Finance

Chapter 1 – Introduction

Figure 1.17

Once you have done this, click OK. Repeat this for the Maximum, Minimum, Median and Standard Deviation functions, and then do the same to calculate those statistics for the share prices as well. Format the results so that they are consistent. (i.e. the same number of decimal places, etc.)

Note that in Excel 2010 & above, STDEV has been superseded by STDEV.S though both calculate the sample standard deviation.

Tip: Once you have set up the functions for the market values, highlight those cells (B15:B19), then copy and paste them to column D – you only need to click on the first cell, D15, Excel will fill out the correct past area itself. As before, the cells within the functions will have adjusted to their new position on the spreadsheet..

Once you are familiar with a function, you can type it into the cell without using the wizard if you find that easier.

Department of Accounting & Finance

Undergraduate Finance

16

Business Finance

Chapter 1 – Introduction

To check all the formulas in your spreadsheet at once, click onto the Formulas ribbon, then click Show Formulas in the Formula Auditing section. Your sheet should look like Figure 1.18 below.

Figure 1.18

Click on Show Formulas again to return to showing values. One thing to note is that although we restricted the format of the share prices to 2 decimal places, the full precision of the result is still stored, just hidden.

Department of Accounti...