QUESTION 14 6 points Save Answer Consider the following scenario and image. You are planning the production schedule for a small factory making simple stationary items. Storage space for items awaiting shipping is a problem, as space is limited. Your task: Use Solver to decide how many of each item to make (Column ) so that all constraints are satisfied, and the storage space is completely full (20,000 items in total). Assume that all items are the same size. Constraints: 1. Pencils and Sharpeners are only sold in pairs, so we should have equal numbers of both. 2. We require a minimum of 6,000 of Pencils in the warehouse after the production run to satisfy existing orders. 3. We must not have more than 4,000 of Crayons after the production run, as we don't usually sell more than this. Hint: Be aware of any constraints which are not listed here, but are required for Solver to work. *The image is labelled "Question 14 image" in the Exam PDF (for students that can't see images). A B D E 1 Production Schedule 2 Item 3 Pencils 4 Pens 5 Crayons 6 Sharpeners 7 Totals Current stock Volume to make Total items in storage space 4,733 4,733 formula in D3 is =B3+C3 (copied down to D6) 2,997 2,997 2,870 2,870 1,126 1,126 11,726 0 11,726 formula in B7 is =SUM(B3:36) formula in C7 is =SUM(C3:06) formula in D7 is SUM(D3:06) 8 9 Your task: An image for the Solver Parameters is shown below with the necessary four parts for completion (A, B, C, D). What values should we enter in the fields (A, B, C, D) as highlighted in the figure below? For your answer, you must write the details of what you would enter into these four parts of the Solver window. You must ensure that you make clear what data goes into what part of the Solver Parameter window (for example "A contains $B$4" or "D contains $A$6 >=5" etc.). QUESTION 14 6 points Save Answer Consider the following scenario and image. You are planning the production schedule for a small factory making simple stationary items. Storage space for items awaiting shipping is a problem, as space is limited. Your task: Use Solver to decide how many of each item to make (Column ) so that all constraints are satisfied, and the storage space is completely full (20,000 items in total). Assume that all items are the same size. Constraints: 1. Pencils and Sharpeners are only sold in pairs, so we should have equal numbers of both. 2. We require a minimum of 6,000 of Pencils in the warehouse after the production run to satisfy existing orders. 3. We must not have more than 4,000 of Crayons after the production run, as we don't usually sell more than this. Hint: Be aware of any constraints which are not listed here, but are required for Solver to work. *The image is labelled "Question 14 image" in the Exam PDF (for students that can't see images). A B D E 1 Production Schedule 2 Item 3 Pencils 4 Pens 5 Crayons 6 Sharpeners 7 Totals Current stock Volume to make Total items in storage space 4,733 4,733 formula in D3 is =B3+C3 (copied down to D6) 2,997 2,997 2,870 2,870 1,126 1,126 11,726 0 11,726 formula in B7 is =SUM(B3:36) formula in C7 is =SUM(C3:06) formula in D7 is SUM(D3:06) 8 9 Your task: An image for the Solver Parameters is shown below with the necessary four parts for completion (A, B, C, D). What values should we enter in the fields (A, B, C, D) as highlighted in the figure below? For your answer, you must write the details of what you would enter into these four parts of the Solver window. You must ensure that you make clear what data goes into what part of the Solver Parameter window (for example "A contains $B$4" or "D contains $A$6 >=5" etc.)