NOTE: An excel file is attached with this page. Pls. feel free to edit.
I add the links to the contribution of other INFOSYS students. <page1...page7>
BY: Rhona Valdez


page 1
CASE BACKGROUND

Piedmont Trailer Manufacturing Company, a nationally recognized trailer manufacturer, produces a wide range of quality standard and custom-built trailers, ranging from gooseneck to bumper pull trailers. Although the Piedmont Trailer Manufacturing Company uses state of the art information systems for most of its business processes, its custom order tracking process is primarily manual-based and requires major renovations. In an effort to improve the custom order tracking process, a systems analysis and design project is currently underway. As part of the systems development team, one of your responsibilities is to prepare an economic feasibility analysis for an upcoming presentation to management.

Ms. Geraldine Pablo, the project manager, asks you to construct an Economic Feasibility workbook. The purpose of this workbook is to summarize and analyze the benefits and costs associated with the proposed custom order tracking project. The preparation of an Economic Feasibility workbook requires you to design five worksheets, use several formulas and functions, use basic cell and worksheet formatting, and consolidate data from multiple worksheets into a summary worksheet.

page 2
CASE SCENARIO

Quality trailers and excellent customer service are the two primary reasons why the Piedmont Trailer Manufacturing Company is the nation’s largest manufacturer of standard and custom-built trailers. Although the majority of the company’s income is derived from the sale of standard trailers, the number of custom orders is on the rise. When the custom order is placed, the request is captured on the several paper forms and then routed to the production department. Often it takes three months before a custom order is released to the production. This is due in part to the careful attention given to the customer by helping him select the right finishes, fixtures, trailer size, and other amenities. Management has decided that the custom order tracking process, your project team is assigned the task of developing a custom order tracking system.

During the planning phase, your project team identified several tangible benefits and costs. The new custom order tracking system will save the company money by decreasing storage, staff, and order rework expenses. Additionally, the proposed system should increase sales, summarizes these benefits and their respective savings.

The proposed custom order tracking system will incur both one-time and recurring costs. From one of your business courses, you recall the one-time costs often occur during the start up and development of a project and recurring costs occur throughout the useful life of personnel, training, project-related technology purchases, site-preparation, and miscellaneous costs. Table 2 lists the one-time costs and their estimated dollar values. Recurring costs include software maintenance, hardware, supplies, a new information technology position, and site rental costs. Table 3 summarizes these recurring costs.

page 3
Design Specifications

Since the project is in the early stages of development, you want your workbook to be as flexible as possible, so that additional costs and benefits, when identified, are easily added to the Economic Feasibility workbook. You decide that the Economic Feasibility workbook should contain at least five worksheets: Documentation, One-Time Cost, Recurring Cost, Tangible Benefit, and Economic Feasibility Summary. The Documentation worksheet provides information about the creator, each individual worksheet, and the date created. (Your professor will provide additional guidelines for the Documentation worksheet.)

You decide to construct the One-Time Cost, recurring Cost, and Tangible Benefit worksheets first, since these worksheets have a simple design. These worksheets each contain two columns, with the first column identifying the items in the category, and the second column containing the dollar values associated with the items. Each worksheet totals the dollar values; these totals are then used in the Economic Feasibility Summary worksheet.

As Figure 1 shows, the Economic Summary worksheet has a more complex design. Since the Economic Feasibility Summary worksheet is a summary worksheet, it consolidates data from the One-Time Cost, Recurring Cost, and Tangible Benefit worksheets, requiring you to reference specific cells on these worksheets.




page 4
ECONOMIC FEASIBILITY STUDY

As part of the Economic Feasibility Summary Worksheet design, you must discount the recurring benefits and costs to their present values. Although several ways exist to determine the present value of the benefits, you decide to multiply the recurring benefit (or cost) by a present value factor. Since each year requires a different present value factor, the worksheet must compute the present value factor for each year. You decide to use the formula provided below to determine each year's present value factor. (In following formula, "i" refers to the discount rate, and "n" refers to the year. The worksheet shown in Figure 1 assumes that the project's useful life in five years.) To determine the present value of a benefit or cost for a particular year, you multply the recurring value of the benefit or cost for that year by the present value factor for that year. The net present value of all benefits (or costs) is a summation of the benefits (or costs) up to and including the current year. The overall net present value is the difference between the net present value of all benefits and the net present value of all costs. The cash flow section provides a summary of the cash flows on a yearly basis, as well as a summation of the overall cash flows.


Although Ms Although Ms. Pablo is the primary user of the Economic feasibility workbook, other projec tteam member will have access to this workbook. Therefore, you decide that all cells other than input cells, should be protected. (You may wish to use your system’s online help feature to review worksheet protection.)


page 5
INFORMATION SPECIFICATIONS

Ms. Pablo wants to generate optimistic, realistic, and pessimistic views of the data, so she requests the ability to quickly change the discount rate. To satisfy this requirement, you include a cell on your economic feasibility summary worksheet to hold the discount rate is used in several formulas, so referencing this cell in a formula facilitates the economic feasibility analysis.

The economic feasibility summary worksheet summarizes the costs and benefits, shows the present values of the costs and benefits, calculates the overall net present value, and shows the yearly and overall cash flows for the project. Although not show in Figure 1, Ms. Pablo requests that the project’s breakeven point and internal rate of return be determined. During her presentation to management, Ms. Pablo will use the breakeven point to help justify the project’s viability and show how quickly management will recover its investment in the project. Since the internal rate of return provides an indication of the project’s profitability, Ms. Pablo will use the internal rate of return to help justify management’s investment in the project.

Ms. Pablo needs answers to the following questions. Using your newly designed economic feasibility workbook, provide Ms. Pablo with answers to her questions.

1. How will discount rates of 10, 12, and 14 percent affect the project’s feasibility?
2. If management stipulates that the internal rate of return must be equal to or greater than the discount rate, is this project still justifiable?
3. How will eliminating an additional staff position of $25,000 affect the economic feasibility assessment?
4. Assume that the staff position mentioned in Step 3 is eliminated and that the site preparation cost increases to $95,000. What impact will these changes have on the project’s Feasibility?

Rhona's Answers

page 6
IMPLEMENTATION CONCERNS

To design the Economic Feasibility workbook described in the case scenario, you will create a workbook consisting of five worksheets. You should create separate worksheets for the documentation, or e-time costs, recurring costs, recurring benefits, and economic feasibility summary. Since the Economic Feasibility Summary worksheet consolidates data from three of the worksheets, you should create this worksheet last. You should also consider using range names to simplify the consolidation process.

While you are free to work with the design of your worksheets, you should have a consistent professional appearance. You should also use proper formatting for the cells. For instance, dollar values should display with a dollar sign and be formatted to two decimal places.

TEST YOUR DESIGN

After creating the Economic Feasibility workbook described in the case scenario, you should test the design of your worksheets. Perform the following operations.

1. What recommendations would you make if the useful life of the project is three years instead of five years? Six years?
2. Identify at least three additional befits that might be derived from the project. Estimate their value and include the values in your analysis. What impact do these benefits have on your economic feasibility?
3. Identify at least one additional one-time cost and at least three additional recurring costs. Estimate their values and include these values in your analysis. What impact do these new costs have on your economic feasibility? Is the project still justifiable? Why or Why not?

Rhona's Answers

page 7
CASE DELIVERABLES

In order to satisfactorily complete this case, you should build the workbook as described in the case scenario and then prepare both written and oral presentations. Unless otherwise specified, submit the following deliverables to your professor. Also, unless otherwise specified, perform these steps after you have tested your design.

1. A written report discussing any assumptions you have made about the case and the key elements of the case. Additionally, what features did you add to make the worksheets more functional? User-friendly? (Please note that these assumptions cannot violate any of the requirements specified above and must be approved by your professor.)

2. A printout of each worksheet.

3. A printout of each worksheet's formulas

4. An electronic, working copy of your workbook that meets the criteria mentioned in the case scenario and specifications sections.

5. Results for each question posed above. (A memo to your instructor discussing these results should also be provided.)

6. As mentioned above, you should prepare an oral presentation. (Your instructor will establish the time allocated to your presentation.) You should use a presentation package and discuss the key features of your worksheets. Also, discuss how the workbook is beneficial for Ms. Pablo. What additional information should be included in the workbook to make it more useful?