Question: Solver Parameters Set Objective: To: Max By Changing Variable Cells: $B$5:SG$6 Subject to the Constraints: $B$10:$G$10 >= 0 $B$5:$G$5 A B C D 1

Solver Parameters Set Objective: To: Max By Changing Variable Cells: $B$5:SG$6 Subject to the Constraints: $B$10:$G$10 >= 0 $B$5:$G$5 A B C D 1 Period May June July 2 Forecast 50 60 70 3 Output 4 Regular 60 60 60 5 Overtime 6 Subcontract 10 0 10 10 0 0 7 Output - Forecast =SUM(B4:B6)-B2 |=SUM(C4:C6)-C2 =SUM(D4:D6)-D2 8 Inventory 9 Beginning 10 =B10 10 Ending 11 Average 12 Backlog 0 13 Costs |0 |=B4*1000 |=C4*1000 =C5*1600 =C11*2000 -C12*5 14 Regular @ 1,000 15 Overtime @1,600 |=B5*1600 16 Inventory @ 2,000 =B11*2000 17 Backlog @ 5,000 18 Total B12*5 |=SUM(B14:B17) |=SUM(C14:C17) =D4*1000 =D5*1600 -D11*2000 -D12 5 |=SUM(D14:D17) =(E9+E10)/2 0 |=E4*1000 |=E5*1600 -E11 2000 -E12 5 =SUM(E14:E17) |=SUM(B4:B6)-B2 |=SUM(C4:C6)+C9-C2 |=SUM(D4:D6)+D9-D2 |=SUM(E4:E6)+E9-E2 =(B9+B10)/2 |=(C9+C10)/2 =C10 |=(D9+D10)/2 0 E August 90 60 10 0 =SUM(E4:E6)-E2 =D10 1 Period 2 Forecast 3 Output A September F October 80 70 4 Regular 60 60 5 Overtime 10 10 G H Total =SUM(B2:G2) |=SUM(B4:G4) |=SUM(B5:G5) 6 Subcontract 0 7 Output - Forecast =SUM(F4:F6)-F2 |=SUM(G4:G6)-G2 8 Inventory 9 Beginning 10 Ending 11 Average =E10 =F10 |=SUM(F4:F6)+F9-F2 |=SUM(G4:G6)+G9-G2 |=(F9+F10)/2 |=(G9+G10)/2 |=SUM(B11:G11) 12 Backlog 0 |0 13 Costs 14 Regular @ 1,000 15 Overtime @1,600 =F5*1600 16 Inventory @ 2,000 =F11*2000 17 Backlog @ 5,000 18 Total |=F4*1000 =G4*1000 |=SUM(B14:G14) |=G5*1600 |=SUM(B15:G15) |=G11*2000 |=SUM(B16:G16) =F12*5 |=G12*5 |=SUM(B17:G17) =SUM(F14:F17) |=SUM(G14:G17) |=SUM(B18:G18) F H 1 Period September October Total 2 Forecast 80 70 =SUM(B2:G2) 3 Output 4 Regular 60 60 |=SUM(B4:G4) 5 Overtime 0 10 |=SUM(B5:G5) 6 Subcontract =F2-F4-F9 0 7 Output - Forecast |=SUM(F4:F6)-F2 |=SUM(G4:G6)-G2 8 Inventory 9 Beginning 10 Ending 11 Average =E10 |=SUM(F4:F6)+F9-F2 |=SUM(G4:G6)+G9-G2 |=(F9+F10)/2 12 Backlog 0 =F10 |=(G9+G10)/2 0 |=SUM(B11:G11) 13 Costs 14 Regular @ 1,000 =F4*1000 15 Overtime @1,600 =F5*1600 16 Inventory @ 2,000 =F11*2000 17 Backlog @ 5,000 18 Total F12*5 =SUM(F14:F17) |=G4*1000 |=SUM(B14:G14) |=G5*1600 |=SUM(B15:G15) |=G11*2000 =SUM(B16:G16) |=G12*5 |=SUM(B17:G17) =SUM(G14:G17) |=SUM(B18:G18) A B C D E FL G H 1 Period 2 Forecast 3 Output May June July August September October Total 50 60 70 90 80 70 420 4 Regular 60 60 60 60 60 60 360 5 Overtime 0 0 0 0 0 10 10 6 Subcontract 0 0 0 30 20 0 7 Output Forecast 10 0 -10 0 0 0 8 Inventory 9 Beginning 0 10 10 0 0 0 10 Ending 10 10 0 0 0 0 11 Average 5 10 5 0 0 0 20 12 Backlog 0 0 0 0 0 0 13 Costs 14 Regular @ 1,000 60000 60000 60000 60000 60000 60000 360000 15 Overtime @1,600 0 0 0 0 0 16000 16000 16 Inventory @ 2,000 10000 20000 10000 17 Backlog @ 5,000 18 Total 0 0 0 40000 0 0 0 70000 80000 70000 60000 0 0 0 0 60000 76000 416000 1 Period 2 Forecast 3 Output 4 Regular A B C May June 50 60 60 60 5 Overtime 6 Subcontract 0 0 800 7 Output - Forecast =SUM(B4:B6)-B2 |=SUM(C4:C6)-C2 July D E 70 August 90 60 60 0 0 0 -E2-E4-E9 =SUM(D4:D6)-D2 =SUM(E4:E6)-E2 8 Inventory 9 Beginning 10 Ending 11 Average 12 Backlog 13 Costs 0 -B10 -C10 -D10 =SUM(B4:B6)-B2 |=SUM(C4:C6)+C9-C2 |=SUM(D4:D6)+D9-D2 |=SUM(E4:E6)+E9-E2 =(B9+B10)/2 |=(C9+C10)/2 =(D9+D10)/2 =(E9+E10)/2 0 0 14 Regular @ 1,000 0 0 |=B4*1000 |=C4*1000 |=C5*1600 |=C11*2000 =C12*5 15 Overtime @1,600 =B5*1600 16 Inventory @ 2,000-B11*2000 17 Backlog @ 5,000 =B12*5 18 Total |=SUM(B14:B17) |=SUM(C14:C17) |=D4*1000 =D5 1600 |=D11*2000 -D12*5 =SUM(D14:D17) =E4*1000 =E5*1600 -E11*2000 -E12*5 =SUM(E14:E17) A B C D E F G H 1 Period 2 Forecast 3 Output 4 Regular May June July August September October Total 50 60 70 90 80 70 420 60 60 60 60 60 60 360 5 Overtime 10 10 10 10 10 10 60 6 Subcontract 0 0 0 0 0 0 7 Output Forecast 20 10 0 -20 -10 0 8 Inventory 9 Beginning 0 20 30 30 10 0 10 Ending 20 30 30 10 0 0 11 Average 10 25 30 20 5 0 90 12 Backlog 0 0 0 0 0 0 13 Costs 14 Regular @ 1,000 60000 60000 60000 60000 15 Overtime @1,600 16000 16000 16000 16000 16 Inventory @ 2,000 20000 50000 60000 40000 17 Backlog @ 5,000 0 0 0 0 18 Total 96000 126000 136000 116000 60000 60000 360000 16000 16000 96000 10000 0 180000 0 0 0 86000 76000 636000 A B C D E F G H 1 Period 2 Forecast 3 Output May June July August September October Total 50 60 70 90 80 70 420 4 Regular 60 60 60 60 60 60 360 5 Overtime 0 5 15 15 15 10 60 6 Subcontract 0 0 0 0 0 0 7 Output Forecast 10 5 5 -15 -5 0 8 Inventory 9 Beginning 0 10 15 20 5 0 10 Ending 10 15 20 5 0 0 11 Average 5 12.5 17.5 12.5 2.5 0 50 12 Backlog 0 0 0 0 0 0 13 Costs 14 Regular @ 1,000 60000 60000 60000 60000 60000 60000 360000 15 Overtime @1,600 0 0 8000 24000 24000 16 Inventory @ 2,000 10000 25000 35000 25000 17 Backlog @ 5,000 18 Total 24000 16000 96000 5000 0 100000 0 0 0 70000 93000 119000 109000 89000 0 0 0 76000 556000 1 Period 2 Forecast 3 Output 4 Regular A 80 5 Overtime 6 Subcontract September 60 15 0 F G H October Total 70 |=SUM(B2:G2) 60 10 0 7 Output - Forecast =SUM(F4:F6)-F2 |=SUM(G4:G6)-G2 =SUM(B4:G4) |=SUM(B5:G5) 8 Inventory 9 Beginning =E10 |=F10 10 Ending 11 Average |=SUM(F4:F6)+F9-F2 |=SUM(G4:G6)+G9-G2 |=(F9+F10)/2 |=(G9+G10)/2 =SUM(B11:G11) 12 Backlog 0 0 13 Costs 14 Regular @ 1,000 |=F4*1000 15 Overtime @1,600 F5*1600 16 Inventory @ 2,000 =F11*2000 17 Backlog @ 5,000 18 Total |=G4*1000 |=SUM(B14:G14) |=G5*1600 |=SUM(B15:G15) =G11*2000 |=SUM(B16:G16) =F12*5 =SUM(F14:F17) |=G12*5 |=SUM(B17:G17) |=SUM(G14:G17) |=SUM(B18:G18) A B D E 1 Period May June July August 2 Forecast 3 Output 4 Regular 50 60 70 90 60 60 60 60 5 Overtime 6 Subcontract 0 0 5 15 15 0 0 0 7 Output - Forecast =SUM(B4:B6)-B2 |=SUM(C4:C6)-C2 |=SUM(D4:D6)-D2 |=SUM(E4:E6)-E2 8 Inventory 9 Beginning 0 -B10 -C10 -D10 10 Ending 11 Average =SUM(B4:B6)-B2 =SUM(C4:C6)+C9-C2 20 =(B9+B10)/2 |=SUM(E4:E6)+E9-E2 |=(C9+C10)/2 =(D9+D10)/2 |=(E9+E10)/2 12 Backlog 0 0 0 0 13 Costs 14 Regular @ 1,000 |=B4*1000 15 Overtime @1,600 |=B5*1600 16 Inventory @ 2,000 =B11 2000 17 Backlog @ 5,000 =B12*5 18 Total |=C11*2000 |=C12*5 =SUM(B14:B17) =SUM(C14:C17) =C4*1000 |-C5*1600 |=D4*1000 =D5*1600 -E4 1000 |=E5*1600 |=D11*2000 |=D12*5 =E11*2000 |=E12*5 |=SUM(D14:D17) =SUM(E14:E17)

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

Assume your gross pay per pay period is $ 2 , 0 0 0 and you are in the 3 3 percent tax bracket. Calculate your net pay and spendable income if you save $ 2 0 0 per pay period after paying income tax...

Consider the Super Grain Corp. case study as presented in Section 3.1, including the spreadsheet in Figure 3.1 showing its formulation and optimal solution. Use Solver to generate the sensitivity...

leave feedback for my term paper by answering these questions. Include one comment noting what i have done really well. Include one comment noting what i could have done better to improve my paper....

3 Multiple Choice 3 points Review the following Solver Parameters dialog. Which of the following statements is incorrect? Solver Parameters Set Objective: To: Max By Changing Variable Cells: $B$ 1 4...

Wexpro, Incorporated, produces several products from processing 1 ton of clypton, a rare mineral. Material and processing costs total $55,000 per ton, one-fourth of which is allocated to product X15....

After running solver my total cost does not change it remains 0. The formula I used is =D1*D7+D2*D7. What am I doing wrong? Solver Parameters Set Objective: To: Max By Changing Variable Cells:...

1. Discuss solutions that will maximize profits for the organization based on the Solver analysis below. 2. Describe the significance of the sensitivity and limits reports. A 1 2 Profit 3 Bottles 4...

Suppose a hypothetical company recently incurred $1 million in capital expenditures (Capex) to purchase fixed assets. Per guidance from management, the fixed assets have a useful life of 20 years,...

Accounting Scandals The presentations should cover: Details of the accounting practice related to the scandal. For example, explain how Enron used special purpose vehicles/entities or what is Repo...

Can someone tell me what I'm doing wrong? The answer should be about $413.00 (rounded to the nearest dollar), but I'm getting $433. I'm trying to figure out which company will bring me the most cash...

Suppose that you are the accounting manager of a division in a large company and your remuneration is based partly on meeting an earnings target. In the current year, it seems certain that your...

The electric potential in a region of space is given by V = (2 V/m2)x2 + (1 V/m2)yz . Find the electric field at the point x = 2 m, y = 1 m, z = 2 m.

Use the method for solving homogeneous equations t o solve the following differential equation. d x d t = 3 x 2 + 7 t 9 t 2 + x 2 2 3 t x Express the equation i n the form d x d t = F ( x t ) . F ( x...

Thomas and Richard formed the Happy Go Lucky Partnership four years ago. Because they decided the company needed some expertise in multimedia presentations, they offered Sarah a 1/3 interest in...

The following Figure-1 provide you with the isometric view that contains dimensions. Draw the multi- views in third angle of projection layout on the graph paper provided with the question paper. Use...

A health system has forecast net patient revenue in the first 3 months of the year as follows: January, $60; February, $80; March, $100; 60% of services are usually paid for in the month that they...

For each of the following genetic topics, indicate whether it focuses on transmission genetics, molecular genetics, genomics or population genetics. Use all lower case for answers. A. Analysis of...

Determine if the overhead allocated to the product relates to a single plantwide overhead rate method, multiple production department factory overhead rate method, or activity-based costing...

15.25 GPAs The Georgia Student Survey data file has data on college GPA and high school GPA for 59 University of Georgia students. a. If you wanted to use a nonparametric test to check your friends...

15.22 Comparing therapies for anorexia The Anorexia data file on the books website shows weight change for 72 anorexic teenage girls who were randomly assigned to one of three psychological...

15.24 Browsing the Internet Refer to the Georgia Student Survey data file on the books website. Use a method from this chapter to test whether the amount of time spent browsing the Internet is...