Abacus SFX

Located in Hollywood, Abacus SFX is a small startup specializing in creating digital special effects for science fiction movies. Abacus costs for a given film project, if accepted, include $20,000 to set-up a dedicated graphics server for the project, plus $25,000 for each animator assigned to that projects team. Offsetting these costs are project revenues in two forms: a good faith payment for accepting a given project and project billings.

As is customary in the industry, when Abacus accepts a project, it receives the initial good faith cash payment from the films producer. The good faith payment amounts vary by project. Abacus is considering accepting up to five projects for the next month, with the good faith amounts for each project shown in the table below.

Project	Good Faith Payment ($000s)
1	$18
2	$20
3	$8
4	$26
5	$22

In addition, since assigning more animators to a given project shortens the time to complete the effects required for the project, Abacus charges producers a fee of $90,000 per animator assigned to each accepted project, beyond the initial good faith payment.

If Abacus accepts a project it must assign at least one animator to the project (and if a project is not accepted, you need to ensure that no animators are assigned to that project). Abacus has a total of eight animators available next month to assign to accepted projects, and wishes to allocate the animators to projects to create the highest total profit. Assume that all available animators must be assigned to projects, and that Abacus does not allow animators to be split across projects, therefore only an integral number of animators can be assigned to each project.

Tasks:

This assignment will involve two pieces: an Excel workbook and a Word document. The tasks outlined below are in order which they should be performed, and you will switch between each file to complete this assignment.

This report should be written in such a way that a decision maker could read the report and understand everything without having to open the Excel file. As such, please include screenshots as requested (the screenshots should be cropped to facilitate ease of understanding for the reader). Additionally, your answers to these questions should consist of complete sentences, not simple one word or one number answers. Your writing should be professional and grammatically correct. 10 points are allocated for clarity, grammar, and other writing and layout issues (such as quality of screenshots) in the Word document. The remaining 90 points are allocated in the tasks below:

1. [Excel Document, 20 points] Build, but do NOT solve, a Nonlinear Optimization model to solve this problem.
   • HINT: setting this up correctly will require the use of Chapter 5 materials regarding Integer Optimization.
2. [Word Document, 10 points] Include two screenshots: one of the unsolved model (i.e. Decision Variable values are blank or zero) and one of the Solver dialog box. Explain your Nonlinear Optimization Model clearly and comprehensively. The reader should be clear what the model components are for this problem without having to open the Excel workbook for further examination.
3. [Excel Document & Word Document, 5 points] Solve the model using two distinct starting points. First, assume no projects have been accepted and no animators have been assigned; second, assume each project has been selected and one animator has been assigned to each project.
   • Produce a table in the Word Document showing the following information for both starting points: whether each project is accepted or not, the number of animators allocated to each project, the net profit for each project, and the totals for the three previous values. Answer the following question:
     • Do the two starting points lead to the same optimal solution? Why or why not? If not, how would you suggest handling this differences?
4. [Excel Document & Word Document, 10 points] Given potential changes to the schedules in ongoing projects, Abacus is now estimating that there could be as few as five, and as many as eleven, animators available next month.
   • Solve for each possible available number of animators. The starting point for each iteration should be the same: each project has been selected and one animator has been assigned to each project (we are not going to focus on different start points for each iteration at this time).
   • Produce a table in the Word Document showing the following information for each iteration: whether each project is accepted or not, the number of animators allocated to each project, the net profit for each project, and the totals for the three previous values. Answer the following question based on this table:
     • How sensitive is profit to the number of animators available?
5. [Excel Document, 20 points] Abacus has decided that billing full rates for each animator is not realistic. All costs, good faith payments, and minimum of at least one animator per accepted project remain unchanged. However, the billing for a given project team is to be deflated by multiplying it by a billing rate set by Abacus management, given in the table below. For example, if a project team has three members, then billing for that project = $90,000 * 3 * 0.63 = $170,100.

Team Size	Billing Rate
1	1.00
2	0.77
3	0.63
4	0.54
5	0.46
6	0.40
7	0.35
8	0.31
9	0.27
10	0.23
11	0.20

5. • Create a copy of your previous model. To do this, right-click on the worksheet name tab at the bottom of the screen, select Move or Copy and click Create a Copy. This will move all of the Solver formulation to the new worksheet.
   • Modify your previous model to include this billing revision through the use of a VLOOKUP (or equivalent) function. Using this function means that the model should only be solved using Evolutionary Solver. You will need to make changes to your previous Nonlinear optimization model, both in the spreadsheet and in Solver to comply with the guidelines for effective use of Evolutionary Solver, this include removing some constraints, adding/revising formulas, etc. This might require some trial and error to get correct.
     • HINT: you can do this with or without the use of Penalties depending on how you approach this. But one thing to think about is using an Excel function to enforce the relationship between projects and animators, and how you use Decision Variables.
6. [Word Document, 10 points] Include two screenshots: one of the unsolved model (i.e. Decision Variable values are blank or zero) and one of the Solver dialog box. Focus on discussing the differences, both on the spreadsheet and in Solver, between the previous Nonlinear version and this new Evolutionary model.
7. [Excel Document & Word Document, 5 points] Assume we believe there are eight animators available for next month. Before solving this new model, select the solution you think would provide the best starting point for this new model based on your results in Task 3 (which used the Nonlinear model for eight animators). Enter those Decision Variable values into this new model where appropriate. You will then solve this model using Evolutionary Solver with those values as the starting points. Produce a table in the Word Document showing the following information for both the pasted Nonlinear solution and the solution found using Evolutionary Solver: whether each project is accepted or not, the number of animators allocated to each project, the net profit for each project, and the totals for the three previous values. Answer the following question:
   • Did Evolutionary Solver improve the Objective Function value? Why or why not?
8. [Excel Document & Word Document, 10 points] Assume that there is still uncertainty about the number of animators available next month, with a range of five to eleven.
   • Solve for each possible available number of animators. The starting point for each iteration should be the same: one animator has been assigned to each project, thus each project has been selected.
   • Produce a table in the Word Document showing the projects selected, allocations of animators to projects, and total profit for each iteration, and answer the following question:
     • How sensitive is profit to the number of animators available?
     • How do these values compare to those found using Evolutionary in Task 4?