COSC1702-PROJECT3_V3

.pdf

School

Algoma University *

*We aren’t endorsed by this school

Course

COSC1702

Subject

Industrial Engineering

Date

Apr 3, 2024

Type

pdf

Pages

7

Uploaded by CoachWillpower14600

Report
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