Miller Mis - Recently Updated Pages

Rhona's Contribution

rhonavaldez — Mon, 18 Feb 2008 06:40:10 CST

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.

PVF=1/(1+i)^n

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.

How will discount rates of 10, 12, and 14 percent affect the project’s feasibility?
If management stipulates that the internal rate of return must be equal to or greater than the discount rate, is this project still justifiable?
How will eliminating an additional staff position of $25,000 affect the economic feasibility assessment?
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?

Rhona's Contribution 3

rhonavaldez — Mon, 18 Feb 2008 06:38:51 CST

Test Your Design

1. What recommendations would you make if the useful life of the project is three years instead of five years? SIx years?

If the useful life of the project is only three years, the project will not achieve the break even cause at 10%, 12% and 14% discount rate the break even is between the third and the fourth year. So I recommend the discount rate that will be used is less than or equal to 4% so that the break even is between the second and the third year.

If the useful life of the project is six years, the break even will not be change and the IRR will be equal to the discount rate at 11%, thus the project is considered feasible when the discount rate is less tahn 11%.

2. identify at least three additional benefits that might be derived from this project. Estimate their value and include the values in your analysis. What impact do these new 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 Contribution 2

rhonavaldez — Mon, 18 Feb 2008 06:27:45 CST

Information Specification

1. How will discount rates 10, 12 and 14 percent affect the project's feasibility?

As the discount rate increases the Overall Net Present Value and the IRR decreases.

Economic Feasibility Summary at 10% discount rate

Economic Feasibility Summary at 12% discount rate

Economic Feasibility Summary at 14% discount rate

2. If management stipulates that the internal rate of return must be equal to or greater than the discount rate, is the project still justifiable?

It has been said that the project is feasible if its IRR is greater the discount rate which only occur at 10% disocunt rate which resulted to 12 % IRR. If the IRR is equal to the discount rate which occurs at 11% discount rate, the project is neither feasible or not and thus the NPV analysis will be used.

3. How will eliminating an additional staff position of $25,000 affect the economic feasibility assessment?

Recurring Costs

By decreasing the staff position of $25,000 the recurring cost decreases thus increase the feasibility of the project. Before the elimination of the staff position the project is only considered feasible at 10% discount rate base on the IRR analysis, now the project is also feasible at 12% discount rate.

Economic Feasibility Summary at 10% discount rate

Economic Feasibility Summary at 12% discount rate

Economic Feasibility Summary at 14% discount rate

4. Assume that the staff position mentioned in Step 3 is eliminated and the site preparation cost increases to $95,ooo. What impact will these changes have on the project;'s feasibility?

One-Time Costs

Recurring Costs

If the one time costs increases by $95,000 and teh staff position will be removed, it decreases the feasibility of the project. The project will not be feasible at 10%, 12% and 14% discount rate based on the IRR analysis. The IRR will be equal to the discount rate at 8%, thus the project will not be feasible if the discount rate will be greater than 8%.

Amlyn's Contribution

amlynr — Thu, 14 Feb 2008 04:02:06 CST

a) worksheet for five years

b) worksheet for 6 years

c) worksheet for 3 years

As you can see above, if the useful life of the project is only three years, the overall net present value is lesser than the overall net present value for five years and has a negative value. However, if the useful life of the project is six years, the overall net present value is higher than the overall net present value for five years.
For my recommendations which I based from the overall net present value,

If the useful life of the project will only be three years, to have a positive overall net present value,
There should be no discount at all if there will be no changes to the amounts of benefits and costs; or
If there will be no changes to the amount of costs and to the discount rate, there should be additional benefits which will make the amount of benefits higher than the amount of cost; or
If there will be no changes to the amount of benefits and to the discount rate, some cost should be eliminated so that the amount of costs will be lesser than the amount of benefits.
If the useful life will be five years, there is no need for changing the amount of benefits or costs and/or discount rate because the overall net present value is really high which is greater than the overall net present value for five years.

Purpose

mark.crisostomo — Sun, 10 Feb 2008 19:46:50 CST

1. What is the purpose of Piedmont in updating their information system?
2. Why does Piedmont have to make their Information System different from other institutions?
3. What is the real purpose of Piedmont Trailers?

Input

rhonavaldez — Sun, 10 Feb 2008 17:41:20 CST

Prepared by:

Benedick Cabitac

Kim Lagangan

Rhona R. Valdez

1. What are the inputs of the custom order tracking system?

2. What inputs are irrelevant?

3. Who are the suppliers of these inputs? How did they arrive to these inputs?

4. When would be the best time to update the inputs?

5. What if there is lack of input, how will it affect the system?

6. Who has the authority to prove that the given inputs are correct?

7. How would the variation of inputs affect the economic feasibility?

8. What will be the effects if another input like inventory will be added?

Output

lemuelkiong — Sun, 10 Feb 2008 08:11:07 CST

By: Aries Gabilan, Jonathan Magsaysay, Jayson Pabalan and Lemuel Kiong

Questions:

1. What are the benefits of having a custom order tracking system?

2. Who will benefit more from the output of the custom order tracking system? The customers or the company?

3. How efficient would this system be?

4. What if the company still stick with its traditional manual based rather than the proposed system?

5. What if the proposed system does not work?

6. Is there a back-up system?

7. What if the output is not desirable?

8. What would be the effect of the proposed system when it comes to Piedmont's inventory?

9. What would be the output if Milligan used the proposed system to improve the company's inventory management methods?

10. How feasible would it be?

11. What are the requirements in coming up with an efficient custom order tracking system?

Constraints

jessaruth_42na — Sun, 10 Feb 2008 07:54:04 CST

How should you get the present value of the benefits?
What if the discount rate change?
What if the present value formula is not given, what are the possible formulas to use?
Why automation is necessary to improve Piedmont's manual-based process?
How come another manual-based process is not the answer to Piedmont's problem?
How would the new system decrease Piedmont's storage, staff, and order rework expenses?
What does the workbook must contain?
Why does the workbook need to be flexible?
What does documentation worksheet must contain?
How many columnsdo the one-time cost, recurring cost, and benefit worksheet must have?
Referring to number 10, what item/s in a column must contain?
What cell should be protected?

prepared by: Jessa Ruth Fortuna

Siegfred Collado
Stephen Ugay

Boundary

yvonnejazzey — Sun, 10 Feb 2008 01:39:40 CST

"Ecosystem of Questions"

Task: BOUNDARY

Prepared by: MC Cajucom and Yvonne Melo

INVENTORY
(Milligan)

CUSTOM ORDER TRACKING (Piedmont)

Case Scenario: Assuming that Piedmont is getting parts from Milligan to produce its custom trailers, Milligan offers parts to Piedmont.

Questions:

1. What are the products that Piedmont needs from Milligan?
Answer:

Milligan's Data	Unit Cost	Unit Selling Price
Rubber Tires	$150	$200
Steel Rims	$250	$300
Aluminum tube 10ft.	$80	$100
Aluminum tube 12ft.	$100	$120
Metal Sheet 12'X20'	$140	$180
Metal Sheet 8'X4'	$100	$140
Metal Sheet 10'X4'	$120	$160
Metal Beams	$160	$200

2. Would Piedmont save as much if they get certain parts from Milligan and used it in producing custom trailers?

Answer: It is much cheaper to avail parts from Milligan in producing a custom trailer as compared to this given supplier data (see the table below).

Supplier's Data	Unit Cost	Unit Selling Price
Rubber Tires	$200	$250
Steel Rims	$300	$350
Aluminum tube 10ft.	$100	$150
Aluminum tube 12ft.	$120	$150
Metal Sheet 12'X20'	$160	$200
Metal Sheet 8'X4'	$120	$180
Metal Sheet 10'X4'	$180	$200
Metal Beams	$200	$250

3. Wouldn't it be more convenient for Piemont to get the parts needed from the Milligan?

Answer: It is more convenient for Piedmont to get parts from Milligan because Milligan is offering a cheaper and much lower parts. Milligan also has this deal of offering 60 to 90 days to pay.

4. How much would it cost to produce one model of custom trailer? How much net could Piedmont gain?

Answer:

Model	Selling Price	Cost of Good Sold	Net
YvonneAB30	$1400	$1000	$400
MCModel5KB	$1600	$1200	$400
ABCModel	$1800	$1400	$400
XYZModel	$2000	$1500	$500
LMOModel	$2500	$2000	$500

5. Is the highest selling product of Piedmont provides lesser cost and a higher benefit where they are getting its product parts or raw materials from Milligan?

Yes. Since Milligan is offering a cheaper parts as compared to other suppliers Piedmont was able to gain higher net than of getting its part to other suppliers with a lesser net. Piedmont after using its custom order tracking they were able to monitor that for every model they made they see to it as optimal when it comes to minimizing cost and maximizing profit.

Information: To answer this question, Milligan is offering what Piedmont needs in order to produce the product. Milligan is using its inventory system to track whether they have the parts needed to serve Piedmont. While Piedmont is using its custom order tracking to monitor whether they are getting the optimal parts in producing their products, minimizing its cost and at the same time maximizing its benefit.

Patsy, Phoebe and Hazel's contribution

patsyocampo — Sat, 09 Feb 2008 07:49:25 CST

We attached our answers to the Case #2. See below.

Components

eyzi_08 — Wed, 06 Feb 2008 20:16:58 CST

1. What is the definition of components provided in the cases' context?

2. What are the major components of Milligan's Backyard Storage Kit case and Piedmont's Trailer Manufacturing company?

3. What are the minor components of the 2 cases?

4. Who are the potential problem makers & decision makers in both cases?

5. Who operates the Piedmont Trailer Manufacturing Company and Milligan's Backyard Storage Kits?

6. What are the common components in the inventory and order tracking system?

7. What are the importance of the inventory management system and order tracking system?

8. What if these components are not present in the case?

9. What are the procedures in running these component?

10. How will these components relate to their respective systems?

11. If applicable, where is the location of the component in the system?

12. When does these components take effect when running the system?

13. who are the possible persons who can create hindrances both of the project and the components?

Prepared by:
Paul Victor Junio
Mary Grace Habulan
Louie Kagingin

Task: Input

kimilagangan — Wed, 06 Feb 2008 20:15:49 CST

1. What are the inputs of the custom order tracking system?

2. What input/s is/are irrelevant?

3. Who are the suppliers of these inputs? How did they arrive to these inputs?

4. When would be the best time to update the inputs?

5. What if there is lack of input, how will it affect the system?

6. Who has the authority to prove that the given inputs are correct?

7. How would the variation of inputs affect the economic feasibility?

8. What will be the effects if another input like inventory will be added?

Prepared by Benedick Cabitac, Kim Lagangan and Ronaliza Valdez

Environment

forch — Wed, 06 Feb 2008 20:09:10 CST

Prepared by: Tin Juanzon, Amlyn Rey, Fortune Garcia

1. How long should the useful life of the project be in order for the environment to grow?

2. Who are capable of making and changing the environment?

3. What are the factors to be considered in putting up an efficient environment?

4. What could be the factors in the general environment that may negatively affect the project's feasibility?

5. How can the environment affect the other MIS elements?

6. How do you describe a "Perfect environment?"

Ecosystem of Questions

yvonnejazzey — Wed, 06 Feb 2008 18:31:42 CST

9 MIS elements

1. Input
2. Components
3. Interrelations
4. Interface
5. Boundary
6.Constraints
7. Environment
8. Output
9. Purpose

Click your corresponding element, place and edit your page.

Interrelations

KristineErika — Mon, 04 Feb 2008 20:14:15 CST

Questions:

1. How come the overall net present value (NPV) of Piedmont Company decreases when the discount rate increases?

2. How come the yearly NPV cash flow of Piedmont Company is decreasing per year?

3. What should the manager of Piedmont Company do if the project turns out to be not feasible based on the worksheet created?

4. How should Piedmont Company minimize their costs and maximize their benefits to increase their NPV?

5. Would an additional inventory increase the annual gross profit of Milligan's Company?

Prepared by: Kristine Canlas and Patricia Ocampo

Interface

yvonnejazzey — Sun, 03 Feb 2008 23:53:37 CST

There is no abstract available for this page revision.

Kristine's Contribution

yvonnejazzey — Sun, 03 Feb 2008 18:28:17 CST

Case 2: Piedmont Trailer Manufacturing Company

Answers to Ms. Pablo's Questions (Information Specifications)

1. How will discount rates of 10, 12, and 14 affect the project's feasibility?

By looking at the overall net present value, we can determine if the project adds value to the firm. Based on the results above, as the discount rate increases, the overall net present value decreases. Thus, a higher discount rate will make the project less feasible as it will not contribute more value to the company.

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?

When the discount rate is 14%, the corresponding IRR is 9%. To manipulate this as required by the problem, I used "goal seek" which is part of the what-if analysis feature of MS Excel. Based on the results, if the IRR is equal to 14%, the discount rate decreases to 9%. On the other hand, if the IRR is 18% which is greater than 14%, the discount rate further decreases to 5%. The project is still justifiable since both inputs yielded a higher overall net present value.

3. How will eliminating an additional staff position of $25,000 affect the economic feasibility assessment?

To eliminate the additional staff position, I added $25,000 to the staff reduction benefit and got $70,000. This increased the total yearly recurring benefits to $320,000.

As a result, the overall net present value and the IRR drastically increased making the project more feasible.

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?

To make the site preparation cost $95,000, I added $36,500 to its original value. The total costs increased to $293,157.

Considering that the staff position was eliminated and the costs increased, the resulting overall net present value and the IRR decreased making the project less feasible.

Note: My Excel file is attached below.

Team Members

rhonavaldez — Sun, 03 Feb 2008 06:22:20 CST

*NOTE: If your group is posting names and phone numbers here, please go into your site's "Settings" and make the site PRIVATE so it cannot be viewed except by the invited members of your wiki.

Name	Email	Phone Number	Role
Joe Student	joe@college.edu	555-585-8888
Ronaliza R. Valdez	rhonavaldez@gmail.com/ rhonavaldez@yahoo.com.ph	0905-6289971	Contributor

Wiki Tip:
To add more rows to a table, put your cursor in the last row of the table and right-click with your mouse. Select "Add row" from the tool tips menu.

Aries' Contribution

ariesgabilan — Sun, 03 Feb 2008 03:16:25 CST

Test your design: (Answers to No. 3)

To view the answers, please click the attachment below. tnx!!!

Project Schedule

yvonnejazzey — Thu, 31 Jan 2008 22:38:13 CST

Project due date: No specific date yet.

Progress tracker:

Milestone:	Date due:	Date completed:
1. Everyone should contribute on this case. Share what you know, any idea publish it here.
2. Add page for your contribution
3. Time to time visit our wetpaint to see updates on the case.
4. Record what number you are working on at the assignment page under the home page.
5.
6.
7.
8.
9.

See also: The Assignment | Research | Rough Draft | Team Members

Wiki Tip:
Did you know you can add Excel spreadsheets and PDF attachments to any page? Click "Add an attachment" from the Page Toolbox.