lecture 8 spreadsheet applications

Report
Module 1 - Lecture 8
Spread sheet applications
and training evaluation
Dr. Sayed Kaseb
Associate Professor, MPED, FECU
Manger of Pathways to Higher Education
Grant Coordinator of VISION Project
1
Example 8.1
A new software will be injected in an existing company, It is
expected that the software will increase annual revenue over
that the current software by m.u.200000 for each of the next 2
years, and by m.u.300000 for each of years 3 and 4. The
planning horizon is only 4 years.
Develop spreadsheet to answer the following questions:
2
(a)Determine the equivalent future value in year 4 of the
increased cash flows, using an 8 % interest rate of return for
both simple and compound interest
(b) Rework part (a) if the flow estimates in year 3 and 4 increase
from m.u.300000 to m.u.600000.
(c) Consider the effects of 4% inflation in the analysis, hence the
rate of return from will be reduced from 8% to 3.85% per year
3
a
4
b
5
c
6
Example 8.2
A person buys a piece of property for m.u.5000 down payment
and deferred annual payments of m.u.500 per year for 6 years
starting 3 years from now. How much would he pay if he
decides to pay the entire price immediately? The interest rate is
8 %.
i= 8%
Solution:
P = 5000 + 500 (P/A, 8, 6) (P/F, 8, 2)
1.08
6
= 5000 + 500

 1
0.08 (1.08)6
= m.u. 6981.69
×
0
1
2
3
4
5
6
7
8
1
1.082
m.u.500/year
m.u.5000
7
8
Example 8.3
A principal P is deposited now in a bank with an interest rate of
4.5%. The principal is required to be completely recovered in the
form of equal annual withdrawal of m.u.200 per year for the first
5years starting one year after deposit; then a different annual
withdrawal of m.u.300/year for the following 3 years. Determine
the value of P.
A = m.u.300
i= 4.5 %
A = m.u.200
years
0
1
P = ??
2
3
4
5
6
7
8
9
10
Simple Solution
Just use NPV function
i
NPV
Cash Flow
4.50%
1539.77
200
200
200
200
200
300
300
300
11
Example 8.4
For a nominal interest rate of 15% calculate the effective interest
rate if the interest is compounded:
Biannually, Quarterly, Monthly , and Continuously (m= ∞)
12
Example 8.5
A company will make seven consecutive annual investments starting
3 years from now. The first investment is m.u.100000 and the
investments decrease at an annual rate of 10 %. What will be the
compound amount 14 years from now if the interest rate is 12 %?
Determine the single investment that should be made now in order
to yield the same amount 14 years ahead. Find the equivalent
uniform series of 14 year-end investments which would result in the
same amount at the same time. (NB: This example is same as that of
example 6.7, however, two spreadsheet solutions will be
introduced)
13
14
Simple Solution
Just use NPV function
E
10%
Years
Cash Flow
0
1
2
3
4
5
6
7
8
NPV
n
F
A
A
i
12.00%
Annual Payments
0
0 Payment No.
100000
1
90000
2
81000
3
72900
4
65610
5
59049
6
53144
7
283961.46
14
(P)
(F)
-1387752
-42842
42842
15
Example 8.6
A spreadsheet solution for example 7.6.
Plan A
Machine 1
Plan B
First cost, m.u.
Annual operating cost,
m.u./year
90,000
6,000
Machine 2
28,000
175,000
300
2,500
Salvage value, m.u.
Life, years
10,000
8
2,000
12
10,000
24
16
17
Example 8.7
Determine the annual cash flow analysis for each of
Plan based 8% interest rate for the cash flow shown in
the table below. Plans A, B, and C has a ten-year life
and a scrap value equal to 10% of its original cost. Also
find the capitalized profit for each plan.
18
Table 8.2: Cash flow of Plans A, B and C for example 8.7
Installed cost of equipment m.u.
Plan A
Plan B Plan C
15000
25000
33000
9000
14000
6000
6000
2500
3300
Material and labor saving per year 14000
m.u./year
Annual operating expenses
8000
m.u./year
Scrap Value m.u.
1500
19
Solution
Note: The annual cash flow in such case is the annual income
or annual inflow amount , therefore the plan which has the
maximum annual cash flow is the best one, and that is clear in
the spreadsheet solution. The capitalized profit is the present
equivalent amount for the profit gained from each plan through
its life.
20
21
Evaluation Form (EF)
Improve
output
Course EF
Trainee EF
Project EF
Observer EF
Self/Peer Assessment
Form
Duration EF
Seminar EF
Evaluators
Frequency of
filling the EF
4
Trainees
3
Trainer
Observer
2
Coordinator
Supervisor
Course/Module
Project
Program
As Required
1
Input
22
Criteria for
trainees selection in
second training week (26/2 – 3/3/2005)
and certificate delivery
1 – Attending the full module of first training week
2 – Getting high score in Trainee EF
3 – Submitting Trainer EF
4 – Submitting two reports:
A- Module #1
B- Module #2
23
Vision Website
Engineering-economy.com
[email protected]
Training Schedule
EE Educational Material
EE Exams and Projects
EE Office and Lab
Vision News
EE Team Home Pages
FAQs
Links
Contact us
Sitemap
eeem8
Main Activities
Trainees Main Data
Problems Bank
EE Graduation Projects
Evaluation System
Management and Monitoring
Vision
You can call us 5678907, but, just check the website,
you will find every thing, even a soft copy of the
Educational Material:
24

similar documents