COSC1702-PROJECT3_V3
pdf
keyboard_arrow_up
School
Algoma University *
*We aren’t endorsed by this school
Course
COSC1702
Subject
Industrial Engineering
Date
Apr 3, 2024
Type
Pages
7
Uploaded by CoachWillpower14600
COSC1702
Project #3
Ver 3
PROJECT #3 – COSC1702
IMPORTANT RULES:
A) Please read everything carefully in advance. Complete both questions and upload the
individual Excel solution files for each by the due date.
B) Use the provided Start-Files for your group. Do not use Start-Files from any other
group/section.
C) You MUST complete steps 1 & 2 before doing any other work – skipping will result in zero.
D) Failure to follow these rules will result in penalties up to and including zero on the
question/assignment.
Problem 1: [60 marks] Northern Tables - Find the Optimal Production Combos Using Solver
Problem Description:
You have started a small manufacturing company which makes small tables.
You’re a new start-up and have decided to keep your production line small to start – limiting
operations to only four types of tables: End Tables, Night Stands, Coffee Tables and Console Tables.
The chart below details the costs associated with each product:
Your facilities are divided between two workshops. Workshop one is a smaller facility and can only
accommodate three people – a skilled supervisor and two regular labourers with an average hourly
cost of $18. Because this workshop is smaller, it can only manufacture End Tables and Coffee
Tables. Workshop two is a newer and larger facility large enough to manufacture all four different
products. The second workshop is staffed by one skilled supervisor and 5 regular labourers and has
a lower average hourly labour cost of $16. Finally, each shop has overhead costs for lights, heat,
power, etc. Workshop one costs $24 per day to use whereas workshop two costs $48 per day
because of its larger size. Both workshops operate 10 hours per day.
COSC1702
Project #3
Ver 3
Instructions:
Perform the following tasks:
1. Start Excel. Open the “Project3-NorthernTables-StartFile.xlsx” file from the starter files provided.
Change the document properties to include:
a your name as Author,
b “Project 3, Q1 – Northern Tables” as the Title,
c COSC1702X where X is your section code as the Subject,
d Any three appropriate keywords
e Your name and email address in the comment section.
2. Save the workbook using the file name “yourname-Project3-NorthernTables” where “yourname” is
your last name followed by your first name. (Remove the word Start-File in the name)
3. Add Data Validation to cells C8 & C10, and D8:D11 to ensure that only positive whole numbers
can be entered. Apply the same Data Validation rule to cells C28:C31 representing the Order
Details. For cell C32, any positive number will be allowed. For C9 and C11, restrict entries to only
the number 0. Grey out cells C9 and C11 using a dark grey text background colour.
4. Click on the Assumptions Tab and enter the isolated assumption values using the information from
the problem description above. Every cell should have a value.
5. Back on the Order Planner tab, the Hours per Day Available for Workshop1 is calculated in cell
C5 as the Number of Shop Workers times the Workshop Hours per Day – both assumptions found
on the Assumption tab. Using the mouse, construct the formula so it look like:
=Assumptions!C3*Assumptions!C4
Modify the formula so that the cell references are partially absolute – that is the row number is
anchored (has a $ sign in front of it) but the column letter is not.
6. Calculate in C6 the Daily Labour Cost for Workshop1 as the Hours per Day Available times the
Workshop Labour Cost per Hour (an assumption). Again, this should use only a partial absolute
reference to the assumption value – anchoring just the row number.
7. In cell C13, we need to calculate the total number of hours it will take to produce the item
indicated in cells C8:C11. Even though cells C9 and C11 will only ever be 0, we want to be sure the
formula covers all options. Create a formula that adds together the products of the Number of Items
being made times the Time to Make assumption associated with that item. The formula should look
something like this (not the complete formula):
= C8*Assumptions!$C$6 + C9*Assumptions!$C$7 +
…
8. The Total Wholesale Value in cell C16 and the Total Material Cost in cell C19 are calculated in a
similar manner. The Wholesale Value is the result of multiplying the number of each item by its
respective Wholesale Price found in the assumptions – and then adding all those products together.
Likewise, the Total Material Cost multiplies the number of each item being produced by its
associated Material Cost value in the assumptions and adds those together. Enter formulas for both
cells and make sure all references to assumptions are fully anchored so they are absolute
references
COSC1702
Project #3
Ver 3
9. Next calculate the Total Shop Days in cell C14 for the Small Workshop as the Production Man
Hours divided by the Hours per Day Available for the shop.
10.The Total Labour Cost in cell C18 is found by multiplying the Production Man Hours by Workshop
Labour Cost per Hour. Finally, the Total Overhead Cost is found by multiplying the Total Shop Days
by the associated Workshop Daily Overhead Cost. All references to assumptions must be partially
anchored references (only on the row).
11.Now sum up the Total Costs in C21 using a sum function – summing up cells C18, C19 & C20.
Next calculate the Gross Profit as Total Wholesale Value (C16) minus Total Costs (C21).
12.Copy cells C5:C6 into D5:D6. Likewise, copy C13:C23 into D13:D23.
13.The final step in creating this Order Planner worksheet is to create all the Total formulas. In cell
F8, enter =C8+D8. Do the same in cells F9:F11, F13:F14, F16, F18:F21, and F23. If you populate
cells C9, C11, D8:D11 with ones, the final version of the worksheet should look like Figure Q1-1. You
may need to fix the formatting to look like the Figure below.
14.Save your work at this point.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
COSC1702
Project #3
Ver 3
15.Next, we want to use the tool to determine several order plans. Below is a table detailing three
order plans. The instructions will help you work through the first one – and then you will complete the
other two – following which, you will generate a Scenario Manager Summary Report
16.Enter the Order 1 (or the next order) details into cells C28:C32.
17.Enter zeros into cells C8, C10, and D8:D11.
18.Now we want to open the Solver tool. Select Solver from the Analyze Group on the Data Ribbon.
Our objective for this Order Planner tool will be to have maximum gross profit while determining the
values for cells C8, C10, D8, D9, D10 and D11. In the solver tool,
a Select the Set Objective box, then click on cell F23 to enter this as the objective cell.
b Next select Max to indicate that we want this cell to be as large as possible.
c Select the By Changing Variable Cells box – and then while holding down the Ctrl Key, select cells
C8, C10, D8, D9, D10 and D11 (the variable cells).
d Click the Add button to start adding constraints. You will need the following:
i 6 constraints requiring the variable cells to be integers (int)
ii 6 constraints requiring the variable cells to be greater than or equal to zero.
iii 4 constraints requiring each of the totals in cells F8:F11 to be equal to the order requirements in
cells C28:C31 respectively. The cell F8 must equal cell C28, cell F9 must equal cell C29, etc…
iv 2 constraints restricting the Total Shop Days values in C14 and D14 to be less than or equal to the
Max Days to Complete value in C32.
19.Using the Simplex LP method, solve for a solution to this problem. If a solution is found, first save
the solution using the Save Scenario button – call it Order1. Then select Keep the Solution and click
on Activity (or Answer) to generate the Activity(or Answer) report. Finally, select OK.
20.Open the Scenario Manager (under the What-If-Tools list), select Order 1 and then click Edit. In
the Changing Variables field, add the additional cells of C28:C32. Click OK and then verify that these
cells were added to the bottom of the Scenario Values dialog box (at the bottom). Once satisfied
they were added, click OK once more.
COSC1702
Project #3
Ver 3
21.Change the values in cells C28:C32 for the next order and repeat steps 16, 17, 18, 19 and 20
until all three orders are done
22.After the three orders are complete, name the following cells:
F13 to Total_Man_Hours,
C14 to Workshop_1_Days,
D14 to Workshop_2_Days,
F16 to Revenue,
F21 to Costs, and
F23 to Gross_Profit.
23.Finally, we want to generate a Scenario Manager Summary report. Select the Scenario Manager
tool, select the Summary button, and then select cells F13, C14, D14, F16, F21, and F23 as the
result cells to include in the report. Select Scenario Summary and then click OK.
24.You should have the following tabs in your final workbook:
25.Save the changes to the workbook and submit the results to the course website.
COSC1702
Project #3
Ver 3
Q2 [40 marks] Alice’s Ice Cream Hut - Determine the Sales Goals Using Goal Seek:
You have been given the task of determining how your small business can make $7500 selling a
combination of three different types of frozen treats. Complete the following instructions to complete
this task:
1. Start Excel. Open the “Project3-AlicesIceCream-StartFile.xlsx” file from the starter files provided
on the LMS – the contents of which is shown in Figure Q2-1 below. Change the document properties
to include:
i Author: your full name
ii Title: “Project 3, Q2 – Alice’s Ice Cream, Part 1
iii Subject: COSC1702X where X is your section code
iv Keywords: Any three appropriate values
v Comments: Your name and email address in this section.
2. Save the workbook using the file name “yourname-Project3-AlicesIceCream_P1” where
“yourname” is your last name followed by your first name
3. Select cell B5 and change the format of the number shown so that it has no decimal place –
making it 0 instead of 0.00
4. With cell B5 still selected, click on the Data tab, and then select Data Validation in the Data Tools
group to open the Data Validation dialog box. The validation should allow only a Whole Number
greater than or equal to zero. On the Input Message tab, enter “Enter Positive Number” as the title
and “Only values >= 0” as the input message. Finally, on the Error Alert tab, keep the Style as
“Stop”, enter “Positive Numbers Only” for the title and “The value must be greater than or equal to
zero” for the Error Message. Click OK to apply the new Data Validation rule to B5. Apply the same
validation rules cells C5 and D5.
5. The Unit Cost needs to be updated to reflect the discounted cost – as the Sales Goal increases
(which is the number of units sold), the cost per unit decreases. Use the vlookup function as follows:
VLOOKUP(lookup_value, table_array, col_index_num)
WHERE: lookup_value
is the Sales Goal for the Ice Cream (cell B5)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
COSC1702
Project #3
Ver 3
table_array
is the lookup table (located in cells G3 to J10)
col_index_number
is the second column (so use 2)
Putting this all together, the formula in cell B8 would be:
= 1.95 * (1 - VLOOKUP(B5, $G$3:$J$10, 2))
5. Using the same logic, provide the Unit Costs with a discount for both the Frozen Yogurt and
Popsicles. If you put 1000 in each of the cells B5, C5 and D5, then the unit costs should be 1.85,
1.76, and 0.72 respectively. Set cells B5, C5 and D5 back to zero to confirm the starting values are
correct.
6. Using Goal Seek, determine the Sales Goal necessary to achieve a profit of $4000 from the sale
of Ice Cream. In short, set the cell B11 (the profit for Ice Cream) to the value of 4000 by changing
the value in cell B5 (the Sales Goal for Ice Cream).
7. Repeat the process from step 6 above for Frozen Yogurt and Popsicles – where the goal for
Frozen Yogurt is to make a profit of $2750 and for Popsicles, the profit goal is $750. Fix any issues
with numbers not displaying properly.
8. You can only buy whole unit values. Use the Circle Invalid Data option on the pull-down menu for
Data Validation to show the three errors created when using Goal Seek. Round the values found by
Goal Seek up to the nearest whole number. Recheck for Invalid Data
9.
Save the changes to the workbook.
10.Change the Title in the document properties to “Project 3, Sneed’s Seeds Part2”.
Now re-save
the file again –
but this time using the name
“yourname-Project3- AlicesIceCream_P2”.
NOTE
THE NEW FILE NAME.
11.After talking to your supplier, you learn that yogurt is sold in boxes of 50 servings, and it is a very
popular product. She says the most you can purchase this season for yogurt is 1000 units. You tell
her that you will take the 1000 yogurt servings (20 boxes) but you will need to call her back with the
rest of the order. You still want to make a profit of $7500 for the season, so you recalculate the
number of Ice Cream servings you need to sell (using Goal Seek). Don’t forget to round this number
up as well so it is a whole number.
12.You called your supplier back and you learned that the Ice Cream treats are sold in cases of 25
whereas popsicles are sold in packs of 100. You say you will phone back again in a few minutes and
return to your worksheet. You round the value in D5 to the nearest unit of 100 (up in this case) and
you see your profit has now risen above the target. Use Goal Seek to find the number of Ice Cream
you need to sell. Finally, round this number up to the next full unit of 25.
13.Use the Circle Invalid Data tool again to make sure your entries are valid.
14.Save the file one more time – then submit both files (part1 and part2) to the course website