Doing calculations with relevant formulae (from lectures) and calculator is worth 8 marks, and spreadsheet work is

Fantastic news! We've Found the answer you've been seeking!

Question:

Doing calculations with relevant formulae (from lectures) and calculator is worth 8 marks, and spreadsheet work is worth 4 marks. For part (a), the spreadsheet work requires presenting this as an optimisation problem to the Solver, and then using the Solver to solve it.

Make sure to include your answers in the relevant .pdf file and (in various tabs) in the relevant .xls file: FamilyName-StudentId-2ndSem2018FIT5097.{pdf, xls} .

The time period given below is a year, but it could equally well have been something different – such as, e.g., a decade. For the problem to follow, we will assume that the time period is 1 year.

We assume that demand occurs at a constant rate of 42,250,000 per year.

Our production facility can produce at a rate of 285,610,000 per year, but the set-up costs of starting the machine are $24 for each run.

There is a cost of $15 per item and a holding amount (or fraction, or percentage) 0.1 = 10% per item per year.

a) What is the optimum value Q* of the production quantity, Q?

b) If we were to change Q from Q* to 100,000, what would the resultant cost be, and how much worse would the resultant cost be when compared to Q = Q* in part (a)?

A note about your Spreadsheet Model

When building your model, bear in mind the goals and guidelines for good spreadsheet design as discussed in Lecture 3. Marks are given for good spreadsheet design. Marks will possibly also be given for originality. Format both your models clearly with comments (and, if possible, shading), etc. so that it is easy for the user to distinguish which cells are occupied by decision variables, LHS and RHS constraints, and the objective function. Include a textbox in each worksheet that describes the formulation in terms of cell references in your model.

Instructions:

You are to upload your submission on the FIT5097 Moodle site and should include the following:

1. A text-based .pdf document (save as: FamilyName-StudentId- 2ndSem2018FIT5097.pdf) that includes all your answers to Questions 1 and 2 and 3 (except for the Microsoft Excel Solver part of each question); and

2. A Microsoft Excel workbook (save as: FamilyName-StudentId- 2ndSem2018FIT5097.xlsx) that includes the following spreadsheets: the spreadsheet model for Question 1;

Sensitivity Rep – the sensitivity report for the Question 1 model (and any other relevant parts);