Supply Chain Modelling and Design Assignment | Custom Essay Services

A Word file to explain your mathematical models, to report your answers, and to write recommendations for decision makers based on the solutions; and
• An Excel file to demonstrate how you use Solver to identify the solutions. You must ensure that the file is in a workable condition before the submission.

*Note, please feel free to use any number of Charts & Graphs , excel sheets and spreadsheet.
Also Explain how you implement the model in the Excel spreadsheet
i. Explain how you implement each equation in the model using what functions in Excel (e.g. SUMPRODUCT) and where you place it on the spreadsheet (e.g. at Cell B2). If constraints are of the same kind and implemented in the same way, you just need to explain the first one and summarise the rest.
b. Explain how you set up Solver in Excel
c. Do not forget to submit the Excel spreadsheet together with the Word report
4. Discussion
a. Discuss the optimal solution based on the results that you find
b. If you conduct sensitivity analysis, the results should be presented here.
5. Recommendations
a. List and discuss what action should be taken based on the discussion.
i. For example, the results may suggest that the optimal solution for the production problem is to make 3 units of X and 4 units of Y. In addition, sensitivity analysis indicates that an increase of each Resource A can increase the profit by $5, up to the maximum of 100 units of Resource A. Your first recommendation can be to produce 3 units of X and 4 units of Y, and the second recommendation is to procure additional 100 units of Resource A.
6. Presentation
a. Discussion and explanation are succinct
b. There is a link between each section. And when the report is read, the reader should not feel jumpy (i.e. some important information is omitted).
c. Cross-referencing and citations are correct, if any
d. All headings and sections are in place
e. Images or figures are clear
please see the attainment files indicate the problem details.

SCMD: Assignment 2
The childcare centre is a non-profit community and has been in the business for over 20 years.
It is located in Melbourne managed by a local university to provide high quality care for
children of university staff and students. Its aim is to provide a safe, supportive environment
for children to meet the needs of children and families.
The centre has four floors with five rooms. Rooms 1–3 are for children under the age of 3.
Room 4 is mainly for children at the age of 3 but this room has some 4-year-old children mixed
in due to a transition to a kindergarten. Room 5 is mainly for children at the age of 4. However,
some 5-year-old children still attend because they are waiting for a term to open in a
kindergarten. According to the Assistant Coordinator, the current numbers of children
attending each day in each room and the maximum room capacities are summarised in the
table below:
Room 1 Room 2 Room 3 Room 4 Room 5
Max Capacity 12 16 16 16 22
Age < 3 < 3 < 3 3–4 4+
Monday 9 7 12 12 9
Tuesday 12 11 15 14 14
Wednesday 10 11 14 13 18
Thursday 10 8 15 10 15
Friday 9 7 13 12 9
According to the National Quality Framework (NQF), the minimum ratio of educators to
children is 1:4 for 0–35 months and 1:11 for 36+ months. Although the NQF ratios are
calculated across the service, and not by individual rooms, for practical and safety reasons,
the centre calculates the ratio on a room-by-room basis.
Generally, there are two levels of qualifications required to work as educators: Certificate III
and Diploma. To comply with the NQF and to offer high quality care, in each room, the centre
requires one educator must have a Diploma qualification for every three educators. For
example, if Room 1 requires 3 educators, one of them must have a Diploma and the other
two may have either a Diploma or a Certificate III qualification. However, if Room 1 requires
4 educators, two of them must have a Diploma qualification.
The centre has 16 permanent educators which are summarised in the table below:
Educator Qualification Available Time Available Day
1 Diploma 09:30–18:00 Mon–Fri
2 Certificate III 09:00–16:30 Mon–Fri
3 Certificate III 10:00–17:30 Mon–Fri
4 Diploma 07:30–16:00 Mon–Thu
5 Certificate III 10:00–17:30 Mon–Fri
6 Diploma 07:30–16:00 Mon–Fri
7 Diploma 09:30–18:00 Mon–Fri
8 Certificate III 10:00–17:30 Mon–Fri
9 Certificate III 09:00–16:30 Mon–Fri
10 Diploma 07:30–16:00 Tue–Fri
11 Diploma 09:30–18:00 Mon–Fri
12 Certificate III 09:30–16:30 Mon–Fri
13 Certificate III 09:00–17:30 Mon–Fri
14 Diploma 07:30–16:00 Mon–Fri
15 Diploma 09:30–18:00 Tue–Fri
16 Certificate III 09:30–17:00 Mon–Fri
In addition, the centre has a contract with the agency. If the centre requires additional
qualified educators, either Certificate III or Diploma, they can contact the agency for an
arrangement. The agency educators are paid only when needed, and there is no on-going cost
required. Currently, the centre has booked eight educators on standby through the agency.
The centre pays the wages to the permanent educators according to the Children’s Services
Award 2010. The wages depend on the experience and qualifications of the educators which
are summarised below:
Level Experience Hourly Rate
3.1 On commencement $22.70
3.2 After 1 year $23.48
3.3 After 2 years $24.22
4.1 On commencement $26.74
4.2 After 1 year $27.14
4.3 After 2 years $27.55
Level-3 employees are Certificate-III qualified educators, and Level-4 employees are Diploma
qualified educators. All permanent educators in this centre have more than two years of
experience working in this industry. The hourly wage to hire an agency educator, whether
Certificate III or Diploma, is $51.77; and they will work for 8 hours.
Since one of the duties of educators is to engage and familiar with children, when they are
assigned to supervise one room, they will stay in the same room for the whole week. The
Centre Manager, Joan Gilmore, wants to create a roster for the educators with a minimumcost
plan. She wants the plan that (1) each permanent educator will be given a job to do and
(2) in case that there are not sufficient educators, an agency educator will be given a job:
a) Formulate and implement the mathematical model to solve this problem
b) How much will it cost for the plan?
c) Which educator will be assigned to which room?
d) Is there any available place for a new child to attend? Which room? Which day?