Question

In: Statistics and Probability

Project Selection Your company is prepared to undertake several new projects over the next five years....

Project Selection

Your company is prepared to undertake several new projects over the next five years. Each Division has submitted a number of project proposals that include the annual budgets and anticipated profit. Your company’s annual budgets for the next five years are as follows. (Amounts are X$1000)

YEAR	2016	2017	2018	2019	2020
FUNDS	3500	4500	4000	3450	4500

The project proposals (for a total of fifteen possible projects) are as follows:

Project	2016	2017	2018	2019	2020	Profit
1	400	230	330	400	230	1500
2	500	270	260	500	270	2000
3	200	430	430	200	430	2500
4	300	220	270	300	220	7000
5	450	500	400	450	500	4000
6	650	450	320	650	450	3000
7	350	530	330	350	530	4500
8	300	700	260	300	700	3500
9	500	800	430	500	800	1500
10	600	250	270	600	250	2000
11	150	500	400	150	500	2500
12	250	340	320	250	340	7000
13	220	400	250	220	270	4000
14	170	300	300	170	300	3000
15	420	400	260	420	400	4500

What project selection should your company choose? (You decide what criterion to use, but recall that this Business school.)

Please show/explain using excel solver

Expert Solution

Answer: Projects Chosen: 3,4,5,6,7,8,12,13,14,15

Profit : 43000

Step 1: Decision Variables of the Problem are as follows-

Here, we have 15 decision Variables i.e Projects chosen. Let's say Projects to be X1, X2,.......X15

Step 2: Objective Function: Maximize Profit by the selection of the projects.

1500*X1+2000*X2+2500*X3+7000*X4+4000*X5+3000*X6+4500*X7+3500*X8+1500*X9+2000*X10+2500*X11+7000*X12+4000*X13+3000*X14+4500*X15

Step 3: Constraints:

for 2016:

1) 400*X1+500*X2+200*X3+300*X4+450*X5+650*X6+350*X7+300*X8+500*X9+600*X10+150*X11+250*X12+220*X13+170*X14+420*X15 <= 3500

2) for 2017

230*X1+270*X2+430*X3+220*X4+500*X5+450*X6+530*X7+700*X8+800*X9+250*X10+500*X11+340*X12+400*X13+300*X14+400*X15<= 4500

3) for 2018

330*X1260*X2+430*X3+270*X4+400*X5+320*X6+330*X7+260*X8+430*X9+270*X10+400*X11+320*X12+250*X13+300*X14+260*X15<=4000

4) for 2019

400*X1+500*X2+200*X3+300*X4+450*X5+650*X6+350*X7+300*X8+500*X9+600*X10+150*X11+250*X12+220*X13+170*X14+420*X15 <=3450

5) for 2020

230*X1+270*X2+430*X3+220*X4+500*X5+450*X6+530*X7+700*X8+800*X9+250*X10+500*X11+340*X12+270*X13+300*X14+400*X15 <=4500

6)Xi = binary, i=1,2,3,.......15

Below, I have attached the solved excel worksheet in the solver

The decision should be made in order to maximize the profit by the undertaken projects and funds needed by the chosen projects need to be within funds available to the company.

Thank You!

orchestra answered 2 years ago

A company plans to invest in a new manufacturing project over the next 12 years. The...

A company plans to invest in a new manufacturing project over the next 12 years. The project will require an initial investment of $65,000, and an additional investment of $26,000 in Year 5. Starting in Year 2, the company will reduce their labour costs by $14,500 a year for the next 5 years, and $9000 a year for the next 4 years. At the end of the project, there will be a residual value of $24,000. If the company’s cost...

HKV company is considering investing in several projects that have varying capital requirements over the next...

HKV company is considering investing in several projects that have varying capital requirements over the next four years. The net present value of each project, cash outflows required over the years and capital available per year are given (in millions) in the table below: Plant 1 Expansion Plant 2 Expansion New Product Development Automation Annual Capital Available Year 1 8 5 4 7 20 Year 2 10 7 2 5 25 Year 3 10 10 1 5 20 Year 4...

For a new project of a company to increase the manufacturing capacity for five years, it...

For a new project of a company to increase the manufacturing capacity for five years, it will purchase new equipment for $1,500,000 and be housed in a building purchased eight years ago for $4,200,000. The building will be retooled for the new project at a cost of $500,000, including building permit fees of $25,000. The purchased equipment will be depreciated using Modified Accelerated Cost Recovery System (MACRS) depreciation schedule, and sold for $250,000 in year 5. The projected revenue for...

RedTick Incorporation is a start-up company. It plans to pay no dividends over the next five years

RedTick Incorporation is a start-up company. It plans to pay no dividends over the next five years because it must reinvest all earnings in the company to finance growth. The company plans to begin dividends of RM3 per share starting in year 6, then the dividend is expected to grow at 10 per cent per year for the next three years, and 6 per cent per year in perpetuity beyond that. If the required return on RedTick’s stock is 15...

Your company is about to undertake a major investment project. The project will require an initial...

Your company is about to undertake a major investment project. The project will require an initial outlay of $100 million for fixed assets plus another $50 million for working capital. Tax authorities will allow you to depreciate the fixed assets on a straight-line basis over four years to a salvage value of zero. In fact, however, you expect that you can sell the fixed assets for $25 million at the end of Year 4. You also expect that you can...

Apply the buyer five stages in the decision process to your selection of a destination for your next vacation.

Mortgage Selection Corin Tucker, a real estate developer, is considering several new projects. To finance these...

Mortgage Selection Corin Tucker, a real estate developer, is considering several new projects. To finance these purchases, she will take out either a fixed rate or an adjustable rate mortgage (ARM). Although the ARM has a lower interest rate, it comes with the added risk of the rate increasing over time. Corin thus considers two scenarios: favorable future interest rates and unfavorable future interest rates. If the future rates are favorable, then the present value of financing the projects with...

DGO Corporation has to decide whether to undertake project 1 or project 2. The projects have...

DGO Corporation has to decide whether to undertake project 1 or project 2. The projects have the following initial investments and future net revenues: . Project 1 Project 2 Initial investment $34,000 $40,000 Net revenue in 2021 $25,000 $35,000 Net revenue in 2022 $30,000 $45,000 Using IP method and a discount rate of 12%, which project would you recommend? Why or why not. This in reference to Profitability Index

Your company expects cash flows of $4,000 per week over the next 2 years

Your company expects cash flows of $4,000 per week over the next 2 years. Calculate the present value of these cash flows if the discount rate provided to you by your accountant is 11%.

City officials agreed _______________ a plan to raise the minimum wage over the next five years....

City officials agreed _______________ a plan to raise the minimum wage over the next five years. a. to b. with c. on Our supervisor ____________ offered us a more robust benefits package if she had the choice. a. would have b. would of Congress _______________ come up with a budget sooner if members had been willing to work together. a. could of b. could have Everyone except him and __________ uses text messaging to communicate with our clients. a. me...