The Vice President of Finance at Kramerica, Inc. has determined a capital investment budget of $8,000,000 for the next year. Unfortunately, this amount is not sufficient to accept all positive NPV projects available to the firm, which are listed in the table below.
1. Help the Vice President of Finance to choose which set of investments should be accepted.
a) Using the Solver, determine which projects should be accepted if the firm’s goal is to maximize shareholders wealth, subject to the maximum investment constraint. (Note: Make sure to set the Solving Method to Simplex LP.)
b) Now assume that the Vice President of Finance has informed you that the capital budget has been cut to $4,000,000 for the next year, and that at least one project must be accepted for each region. Change your Solver constraints to account for this new information and find the new solution.
c) In addition to the constraints from part b, the Vice President of Finance has now informed you that projects 3, 5, and 8 are mutually exclusive, so, at most, one of them can be selected. Change your Solver constraints to account for this new information and find the new solution.
a) 
Accept projects 2, 3, 5, 6, 7, and 8
The array formula in H4 is a good challenge for advanced students. It looks at the range E5:E14 and creates a comma separated list of accepted projects. The formula can be broken down as follows:
1) The array formula, (E5:E14=1)*B5:B14, gives a list of project numbers that were selected (either the project number or 0).
2) Use the Text function to convert that result into a list of strings, but because of the format code ("#" as opposed to "0") the zeros are changed into empty strings.
3) The output from step 2 is fed into the TextJoin function to concatenate the strings together. The delimiter is set to one space, and, crucially, this function is told to ignore empty strings. This results in " 2 3 5 6 7 8 ".
4) I use the Trim function to remove the leading and trailing spaces from the output of step 3. This results in "2 3 5 6 7 8".
5) Using the Substitute function, I replace the remaining spaces with a comma and a space. This results in "2, 3, 5, 6, 7, 8".
6) Next, I want to replace the last comma with ", and". I use the Substitute function again, but I tell it to replace only the last occurrence of a comma (by counting the number of selected projects and subtracting 1). This results in
"2, 3, 5, 6, 7, and 8". This will cause problems if there are fewer than 2 projects selected.
7) Finally, this result is concatenated with the string "Accept projects " which results in "Accept projects 2, 3, 5, 6, 7, and 8".
8) As can be seen on the next two sheets, this works for any number (>= 2) of selected projects.
b) 
Accept projects 3, 5, 7, 8, and 10
c) 
Accept projects 2, 7, and 8
You might also like to view...
A company determines whether to recognize an uncertain tax position by evaluating whether the tax position will "more likely than not" be upheld during a tax audit by the IRS, based on the technical merits of the position
Indicate whether the statement is true or false
Fantastic Futons manufactures futons. The estimated number of futon sales for the first three months of 2014 are as follows: January 40,000 February 50,000 March 60,000 Finished goods inventory at the end of 2013 was 10,000 units. On average, 25 percent of the futons to be sold in the next month are produced and kept as ending balance in finished goods inventory. The planned selling price is $150
per unit. Fantastic Futons buys direct materials for the futons in cloth rolls priced at $80 each. Each roll provides direct material for 40 futons. There was one roll in the direct materials inventory at the beginning of January, and the company expects to have four rolls in inventory at the end of the month. Assuming the production budget calls for 60,000 units to be produced in January, what would be the amount of the cloth rolls direct materials purchases budget for that month? A) $119,760 B) $114,000 C) $120,000 D) $120,240
Which element below is defined as the capacity to influence the actions of others?
a. Authority b. Competence c. Power d. Potential
?Distribution, price, promotion, and product are all elements of
A. ?marketing strategy. B. ?the marketing mix. C. ?a target market. D. ?a consumer good. E. ?a business strategy.