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