16A Assignment1

Download as pdf or txt
Download as pdf or txt
You are on page 1of 5

National University of Computer & Emerging Sciences –FAST (FSD) MIS-Lab

CL310- MIS Lab

Lab Assignment 01
Section

Instructor Areeba
Waseem
Semester Spring 2024

Name

Roll#

FAST School of Management


EXCEL EXERCISE #1: Cost-Benefit Analysis

1. Enter the information in the spreadsheet below. Be sure that the information is entered
in the same cells as given, or the formulas will not work. The information is the stream of
costs and benefits (in millions) estimated for a proposed city baseball stadium. Year 0
represents the initial investment while costs for years 1-10 are the maintenance costs
incurred at the end of each year. The benefits are the revenues from sport team contracts
and revenues at the end of each year.

A B C D E F
1 Cost-Benefit Analysis: 15F-XXXX
2 Total Discount Present
3 Year Costs Benefits Benefits Factor Value
4 0 60 0
5 1 1 3
6 2 1 3
7 3 1 10
8 4 1 10
9 5 1.5 12.5
10 6 1.5 12.5
11 7 1.5 12.5
12 8 1.5 15
13 9 2 15
14 10 2 15
15
16 NPV =
17 Discount
18 Rate =

2. Highlight the cell range B4:B14. Open the FORMAT menu, select CELLS... Select
the category CURRENCY, select the format $1,234.10. Repeat this procedure for the
cell range F4:F16.

3. Highlight cell B18. Open the FORMAT menu, select CELLS... Select the category
PERCENT, select two decimal places.

4. To determine the desirability of the project from an efficiency criteria, first calculate the
Total Benefit for each year of the project. To do this, enter the following formula.

D4: =c4-b4

5. Copy the formula in cell D4 to the cell range D5:D14.

6. We must next discount future costs and benefits to put them into today’s value(i.e.,
find the present value). First, enter the following value for the discount rate (a 10%
discount rate).
2
B18: 0.1

7. Second, calculate the discount factor for each year. Enter the followingformula.

E4: =1/(1+$b$18)^a4

8. Copy the formula in cell E4 to the cell range E5:E14.

9. Third, multiply the total benefit for each year by the discount factor for each year.
Enter the following formula.

F4: =d4*e4

10. Copy the formula in cell F4 to the cell range F5:F14.

11. Find the Net Present Value. Add together the Present Values for each year. Enter the
following formula.

F16: =sum(f4:f14)

12. Enter the following information.


A20: Shortcut:
A21: NPV =
A22: IRR =

13. An easy way of calculating net present value can replace steps 7-12. Excel has anet
present value function as follows: =NPV(rate,value1,value2,...)
This function assumes that each value occurs at the end of consecutive years (i.e., year1,
year 2, year 3,...). Since any initial investments (i.e., costs) occur at the beginning of the
project and not at the end of year 1, these initial costs are added on to the result returned
by the NPV function. For this exercise enter the following formula.

B21: =npv(b18,d5:d14)+d4

14. A usual piece of information for a cost-benefit analysis is the discount rate that returns
a net present value of $0. This can be obtained with the IRR function as follows:
=IRR(values)

3
For this exercise enter the following formula.

B22: =irr(d4:d14)

Your completed spreadsheet should look like the one below.

A B C D E F
1 Cost-Benefit Analysis: City Stadium
2 Total Discount Present
3 Year Costs Benefits Benefits Factor Value
4 0 $60.00 $0.00 ($60.00) 1.00 ($60.00)
5 1 $1.00 $3.00 $2.00 0.91 $1.82
6 2 $1.00 $3.00 $2.00 0.83 $1.65
7 3 $1.00 $10.00 $9.00 0.75 $6.76
8 4 $1.00 $10.00 $9.00 0.68 $6.15
9 5 $1.50 $12.50 $11.00 0.62 $6.83
10 6 $1.50 $12.50 $11.00 0.56 $6.21
11 7 $1.50 $12.50 $11.00 0.51 $5.64
12 8 $1.50 $15.00 $13.50 0.47 $6.30
13 9 $2.00 $15.00 $13.00 0.42 $5.51
14 10 $2.00 $15.00 $13.00 0.39 $5.01
15
16 NPV = ($8.11)
17 Discount
18 Rate = 10.00%
19
20 Shortcut:
21 NPV = ($8.11)
22 IRR = 7.398%

15. Save the spreadsheet: Roll#_Section-A.XLS

EXCEL EXERCISE #2: Series of numbers


Sometimes you need to enter sequential data, such as 1, 2, 3, … or 2001, 2002, …
Create a new worksheet. Label two columns: X and Y.
For X, enter the first two rows: 1, 2.
Select both of the new rows (1, 2). If you use the mouse, release the mouse button but do not
click anywhere.
Carefully, use the mouse to select the small square at the bottom-right corner of the selection
box and drag it down until you have entered the values 1…20.
Hint: You can stop short and see the last number, then drag the corner again and add more
rows.
Dragging the selection box always uses a linear extension. It even works for numeric data—
using a simple regression forecast. Or, you can skip values to create useful patterns, such as
1, 3 to insert odd numbers.

For practice and to see the additional options, use the Edit/Fill command. Enter 1 and 2 in
the first two rows beneath the Y heading.
4
Select the rows beneath the Y heading down to row 20 for the X column. Choose
Edit/Fill/Series from the main menu.
Select the Growth option and check the Trend box. Click the OK button to get another series.

X Y
1 1
2 2
3 4
4 8
5 16
6 32
7 64
8 128
9 256
10 512
11 1024
12 2048
13 4096
14 8192
15 16384
16 32768
17 65536
18 131072
19 262144
20 524288

Save the spreadsheet: Roll#.XLS

You might also like