Excel modeling For Decision Making Assignment Help

Hoyas Electronics is under contract to make 10,000 computer game units. Hoyas must decide whether to make or buy the circuit board to which it will attach its proprietary microchip. There are two stages in making a circuit board: development and fabrication.

The chance that the Hoyas’ engineers will succeed in development is 90%. The costs of development are $50,000.

The fabrication stage has an 80% chance of success and will cost $30,000.

If both stages are successful, the variable cost of producing a circuit board will be $9 board.

The development and fabrication processes use different sets of engineers and can proceed independently (i.e., either could be done first or they could be done simultaneously).

If either stage is unsuccessful, Hoyas must purchase circuit boards from an outside vendor for a cost of $24 board.

If Hoyas decides to forego any internal development and/or fabrication, the outside vendor offered Hoyas a discount of $21 board if the order is placed today.

Excel modeling For Decision Making Assignment Help Through Online Tutoring and Guided Sessions from AssignmentHelp.Net

You are to create a decision support system that will help the decision maker at Hoyas

Electronics analyze the possible development and fabrication options. The manager must be able to change all of the probabilities and costs associated with each alternative. Note: you should consider 3 options: 1) in-house development first, if successful, in-house fabrication, 2) in-house fabrication first, if successful, in-house development and 3) outsource now. Given the parameters of the problem, a fourth option, conduct development and fabrication simultaneously, will be a dominated alternative.

Basic Analysis

For any set of inputs (i.e., the user should be able to change all 8 inputs), the decision support system should provide a clear recommendation that describes the difference in total costs for the three options given the user entered parameters.

Sensitivity Analysis

The decision maker would like to see several sensitivity graphs that show how sensitive the decision is to the various parameters. This graph needs to be “updated” in real-time to consider any other changes that have been made to the base-case data. Choose 3 parameters and perform sensitivity analysis on those parameters. At least 6 candidate parameters exist (e.g., probability of in-house development success, probability of in-house fabrication success, cost of in-house board, cost of out-sourced board, cost of buy now board and number of boards required.)

Other requirements

1. Calculations for sensitivity analysis should “use” the model that you constructed and loop through possible alternative outcomes given different inputs. Fewer points will be awarded for other implementations (i.e., data tables, etc.)

2. At least one User form is required (for something).

3. No specific Controls (i.e., scrollbars, option buttons, spin buttons, list boxes, user forms, etc.) are required, but 30% of the grade is for Controls so you must be creative.

4. The system should look good, be obvious to use and provide help to your user. 20% percent of your grade is for aesthetics and help. Make sure all your data is formatted nicely.

5. You may use the built-in Excel Data Validation feature if you desire.

6. You may use the spreadsheet for any calculations if you desire.