Dear Tutor,

Could you please show me how to solve this problem? I desperately want to learn how to solve this problems with excel. I really appreciate for your help.

Thank you

Question: Seasongood & Mayer

Seasongood & Mayer, established in 1887, is an investment securities firm that engages in the following areas of municipal finance:

1. Underwriting new issues of municipal bonds 2. Trading-for example, acting as a market maker for the buying and selling of previously issued bonds

3. Investment banking-that is, the process of obtaining money from the capital markets at the lowest possible cost.

The major applications of quantitative methods at Seasongood & Mayer are in the investment banking area. One particular application involved the use of PERT/ CPM in the introduction of a $31 million hospital revenue bond issue.

Scheduling the Introduction of a Bond Issue

Any major building project involves certain common steps:

1. Defining the project

2. Determining the cost of the project

3. Financing the project

The investment banker's role in building projects is to develop the method of financing that will result in the owner's receiving the necessary funds in a timely manner. In a hospital building project, the typical method of financing is tax-free hospital revenue bonds.

The construction cost for the building project is an important factor in determining the best approach to financing. Normally, the construction cost is based on a bid submitted by a contractor or a construction manager. However, this cost is usually guaranteed only for a specified period of time, such as 60--90 days. The major function of the hospital's investment banker is to arrange the timing of the financing in such a way that the proceeds of the bond issue can be made available within the time limit of the guaranteed-price construction bid. Since most hospitals must have the proceeds of their permanent long-term financing in hand prior to committing to major construction contracts, the investment banker's role is a significant one.

To arrange for the financing, the investment banker must coordinate the activities of hospital attorneys, the bond counsel, and so on. The cooperation of all parties and the coordination of project activities are best achieved if everyone recognizes the interdependency of the activities and the necessity of completing individual tasks in a timely manner. Seasongood & Mayer has found PERT/CPM to be useful in scheduling and coordinating such a project.

As managing underwriter for a $31,050,000 issue of Hospital Facilities Revenue Bonds for Providence Hospital in Hamilton County, Ohio, Seasongood & Mayer used the PERT/CPM critical path procedure to coordinate and schedule the project financing activities. Descriptions of the activities, times required, and immediate predecessors are given in the attached table. Specific schedules showing start and finish times for all activities were used to keep the entire project on track. The use of PERT/CPM was instrumental in helping Seasongood & Mayer obtain financing for this project within the time specified in the construction bid. The time required as stipulated in the table is for a CPM method of assessing the completion time of the project:

However, the firm, due to some uncertainties,wishes to consider reviewing the project using the PERT method. For this purpose, the following table provides time estimates for an optimistic, pessimistic, and most probable possible times along with costs associated with the Most Probable and also optimistic scenarios. If the company would wish to complete each activity faster and at the crashed times, then the completion cost will increase as stipulated in the following tables.

Report Requirements:

After a closer review of the project, the precedent activities, the numbers for the completion time, and the cost of the project under the new probabilistic conditions including crash, costs have been finalized and summarized in the table on the previous page.Answer all of the following questions listed below:

1What are the expected completion time and the critical path(s) of the project under the CPM method?

2What are the expected completion time and the critical path(s) of the project under the PERT method (round all expected times to the first decimal point)?

3The investors wish to complete the project where the expected completion time has a 90% probability for the completing the project within the times found in table 3.By how much should the project completion time be reduced and what will be the optimum cost (provided obtaining a 90% probability is possible)?Find the new expected completion time for the project and list all the critical paths and their variances (variances found as per data in table 2).

4Following on questions 2 and 3, find the fastest completion time for the project (the shortest possible completion time) and determine the total optimum crash cost of the project (fractional week is allowed)?At this completion time, what would be the probability of finishing the project within the time found in question 2 and its costs?

For every question, support your answers with the appropriate project networks (questions 1 to 4), writing the algebraic LP model (questions 2 & 3) , and using the Excel spread sheet to solve the model for the optimum result (questions 1 to 4). Assume the variances will remain unchanged for the reduced activity times (same as they were obtained before crashing of the activity times in question 2). Also need list of all the critical paths in each network for each question. You can use a spreadsheet to draw your network and the calculation of the activity times.

You must have your complete work on a single Excel file with the following guidelines"

Use a new sheet for each question where the network, solver LP model and its solution, and your explanations are placed.

To have a table of contents with the information on each sheet.

Have your name(s) written on the file name for fast recognition and also on the first sheet of the report.

Round the times to one decimal point, variances to four decimal points and dollars to a whole dollar amount.

Email your completed work with either as an attachment to an email or informing that is posted on the O: Drive (Hand in). The email must have your name(s).

Table 3 - Crashed times and Costs Activity Time Cost of Activity $ Activity Required (weeks) Most Crashed* Probable Optimistic Normal Crash A 4 2.70 28,000 73,500 B 3 2.00 21,000 55,650 O 2 1.30 14,000 36,750 8 5.30 56,000 147,000 Im 4 2.70 28,000 73,500 F 1 0.70 7,000 18,900 G 4 2.70 28,000 73,500 H 2 1.30 14,000 36,750 2 1.30 14,000 36,750 C 0.5 0.70 7,000 18,900 K 12 8.00 84,000 220,500 1.00 0.70 7,000 18,900 M 1 0.70 7,000 18,900 N 0.14 0.70 7,000 18,900 O 2.15 0.70 7,000 18,900 P 1 0.70 7,000 18,900 1 0.70 7,000 18,900 R 2 1.30 14,000 36,750 0 0.70 7,000 18,900 2 1.30 14,000 36,750 3 2.00 21,000 55,650 0.8 0.70 7,000 18,900 W 0.8 0.70 7,000 18,900Table 2 - ACTIVITIES OF THE SEASONGOOD & MAYER PROJECT For a Modified PERT Approach Activity Immediate Activity Time Required(weeks) Predecessor Optimistic Most Probable Pessimistic A 2.70 4.00 7.00 B 2.00 3.00 5.30 1.30 2.00 3.50 - 5.30 8.00 14.00 A 2.70 4.00 7.00 F C 0.70 1.00 1.80 D 2.70 4.00 7.00 H E.G 1.30 - 2.00 3.50 E 1.30 C 2.00 3.50 B 0.70 1.00 1.80 K 8.00 12.00 21.00 H.K 0.70 1.00 1.80 M F, I, J, L 0.70 HU JOUOZ 1.00 1.80 M 0.70 1.00 1.80 M 0.70 1.00 1.80 N 0.70 1.00 1.80 P 0.70 1.00 1.80 O.Q 1.30 2.00 3.50 R 0.70 1.00 1.80 S 1.30 C 2.00 3.50 S 2.00 3.00 5.30 T.U 0.70 1.00 1.80 W 0.70 1.00 1.80Activity Immediate Predecessor Description of Activity Required (weeks) A Drafting and distribution of legal documents 4 B Preparation and distribution of unaudited financial statements of hospital 3 Drafting and distribution of hospital history, description of C services, and existing facilities for Preliminary Official 2 Statement (POS) D Drafting and distribution of demand portion of feasibility study 10 E A Review (additions/deletions) and approval as to form of legal documents 4 F C Review (additions/deletions) and approval of history, etc., for PS - G D Review (additions/deletions) and approval of demand portion of feasibility study 4 H EG Drafting and distribution of financial portion (as to form) of feasibility study 2 E Drafting and distribution of plan of financing and all pertinent facts relevant to the bond transaction for PS 2 C B Review and approval of unaudited financial statements 0.5 K Receipt of firm price for project 12 L H,K Review (additions/deletions), approval, and completion of financial portion of feasibility study 1 M F , I, J, L Drafting of PS completed 1 N M Distribution of all material to bond rating services 0.14 0 M Printing and distribution of PS to all interested parties 2.13 P N Moody's) Presentation to bond rating services (Standard & Poor's, 1 Q P Receipt of bond rating 1 R O,Q Marketing of bonds 2 S R Execution of Purchase Contract 0 T S Authorization and completion of Final Official Statement, completion of legal documents 0.14 U S Fulfillment of all terms and conditions of Purchase Contract 3Marketing of bonds Execution of Purchase Contract m - S Authorization and completion of Final Ofcial Statement, 0 14 completion of legal documents ' Fulfillment of all terms and conditions of Purchase Contract 1,; Bond proceeds available to hospital Hospital able to sign construction contract