Assignment- Cost Accounting
xlsx
keyboard_arrow_up
School
University of British Columbia *
*We aren’t endorsed by this school
Course
354
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
xlsx
Pages
17
Uploaded by confidenceagidi
Input Data
Mild
Medium
Hot
Total
Contribution
5000
2500
2000
9500
Machine 1
3
4
6
13
Machine 2
5
6
2
13
Machine 3
8
3
2
13
Determine the Optimal
Mild
Medium
Hot
Total
Constrain
Machine 1
7538.462 15384.62 1076.923
24000 >=
24000
Machine 2
12564.1 23076.92 358.9744
36000 >=
36000
Machine 3
20102.56 11538.46 358.9744
32000 >=
32000
Quantity
2512.821 3846.154 179.4872
Total Contribution 12564103
9615385 358974.4 22,538,461.54
Microsoft Excel 16.0 Answer Report
Worksheet: [Assignment- Cost Accounting.xlsx]Sheet1
Report Created: 2023-07-08 5:26:42 AM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.328 Seconds.
Iterations: 4 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell
Name
Original Value
Final Value
$E$17
Total Contribution Total
9500 22538461.5385
Variable Cells
Cell
Name
Original Value
Final Value
Integer
$B$16
Quantity Mild
1 2512.82051282 Contin
$C$16
Quantity Medium
1 3846.15384615 Contin
$D$16
Quantity Hot
1 179.487179487 Contin
Constraints
Cell
Name
Cell Value
Formula
Status
Slack
$E$10
Machine 1 Total
24000 $E$10>=$G$10 Binding
0
$E$11
Machine 2 Total
36000 $E$11>=$G$11 Binding
0
$E$12
Machine 3 Total
32000 $E$12>=$G$12 Binding
0
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Assignment- Cost Accounting.xlsx]Sheet1
Report Created: 2023-07-08 5:26:42 AM
Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$B$16
Quantity Mild
2512.8205128
0
5000
900
3000
$C$16
Quantity Medium
3846.1538462
0
2500
3500 214.28571429
$D$16
Quantity Hot
179.48717949
0
2000 391.30434783 636.36363636
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$E$10
Machine 1 Total
24000 134.61538462
24000 65333.333333 848.48484848
$E$11
Machine 2 Total
36000 57.692307692
36000 1217.3913043 14285.714286
$E$12
Machine 3 Total
32000 538.46153846
32000
25000
14000
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
Microsoft Excel 16.0 Limits Report
Worksheet: [Assignment- Cost Accounting.xlsx]Sheet1
Report Created: 2023-07-08 5:26:43 AM
Objective
Cell
Name
Value
$E$17
Total Contribution Total
22538461.538
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$B$16
Quantity Mild
2512.8205128
2512.8205128 22538461.538
#N/A
#N/A
$C$16
Quantity Medium
3846.1538462
3846.1538462 22538461.538
#N/A
#N/A
$D$16
Quantity Hot
179.48717949
179.48717949 22538461.538
#N/A
#N/A
Input DataMild
Medium
Hot
Total
Contributi
5000
2500
2000
9500
Machine 1
3
4
6
13
Machine 2
5
6
2
13
Machine 3
8
3
2
13
Determine
Mild
Medium
Hot
Total
Constrain
Machine 1
7538.462 15384.62 1076.923
24000 >=
24000
Machine 2
12564.1 23076.92 358.9744
36000 >=
36000
Machine 3
20102.56 11538.46 358.9744
32000 >=
32000
Demand C
300
300
300
900
Quantity
2512.821 3846.154 179.4872
Total Contr12564103
9615385 358974.4 22538462
Question 3
In question #2, how much would the contribution margin go up by if 1 additional machine hour of Machine 1
Answer
24000
1 became available
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
Question 3
In question #2, how much would the contribution margin go up by if 1 additional machine hour of Machine 1
Answer
134.62
1 became available
Microsoft Excel 16.0 Answer Report
Worksheet: [Assignment- Cost Accounting.xlsx]Question 4
Report Created: 2023-07-09 11:08:45 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.047 Seconds.
Iterations: 3 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Max)
Cell
Name
Original Value
Final Value
$E$19
Total Contribution Total
8603 22180903.8462
Variable Cells
Cell
Name
Original Value
Final Value
Integer
$B$18
Quantity Mild
1 2448.71794872 Contin
$C$18
Quantity Medium
1 4115.38461538 Contin
$D$18
Quantity Hot
1 532.051282051 Contin
Constraints
Cell
Name
Cell Value
Formula
Status
Slack
$E$12
Machine 1 Total
27000 $E$12<=$G$12 Binding
0
$E$13
Machine 2 Total
38000 $E$13<=$G$13 Binding
0
$E$14
Machine 3 Total
33000 $E$14<=$G$14 Binding
0
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
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Assignment- Cost Accounting.xlsx]Question 4
Report Created: 2023-07-09 11:08:45 PM
Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$B$18
Quantity Mild
2448.7179487
0
4978
334.5 3147.6428571
$C$18
Quantity Medium
4115.3846154
0
2250
334.5 311.78571429
$D$18
Quantity Hot
532.05128205
0
1375 569.34782609 60.818181818
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$E$12
Machine 1 Total
27000 12.865384615
27000 63666.666667 2515.1515152
$E$13
Machine 2 Total
38000 83.942307692
38000 3608.6956522 15285.714286
$E$14
Machine 3 Total
33000 564.96153846
33000
26750 13642.857143
Microsoft Excel 16.0 Limits Report
Worksheet: [Assignment- Cost Accounting.xlsx]Question 4
Report Created: 2023-07-09 11:08:45 PM
Objective
Cell
Name
Value
$E$19
Total Contribution Total
22180903.846
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$B$18
Quantity Mild
2448.7179487
0 9991185.8974
2448.7179487 22180903.846
$C$18
Quantity Medium
4115.3846154
0 12921288.462
4115.3846154 22180903.846
$D$18
Quantity Hot
532.05128205
0 21449333.333
532.05128205 22180903.846
Input DataMild
Medium
Hot
Total
22
250
625
Contributi
5000
2500
2000
4978
2250
1375
8603
Machine 1
3
4
6
13
Machine 2
5
6
2
13
Machine 3
8
3
2
13
Determine
Mild
Medium
Hot
Total
Constrain
Machine 17346.153846
16461.54 3192.308
27000 >=
27000
Machine 212243.58974
24692.31 1064.103
38000 >=
38000
Machine 319589.74359
12346.15 1064.103
33000 >=
33000
Quantity
2448.717949 4115.385 532.0513
Total Contr12189717.95
9259615 731570.5 22180904
Question 3
What should you do? How much will the contribution margin go up by? What is th
Answer
22180903.85
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
he new optimal production plan?
In addition
to the extra resources available for purchase in question #4, an external supplier has offered to supply 1,000 hours of Machine 3, at a cost of $750,000. Should You Spice Up My Life accept the offer or not? You must buy all 1,000 hours or none at all. Provide qualitative and quantitative analyses.
Microsoft Excel 16.0 Answer Report
Worksheet: [Assignment- Cost Accounting.xlsx]Sheet8
Report Created: 2023-07-09 9:17:52 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.047 Seconds.
Iterations: 4 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min)
Cell
Name
Original Value
Final Value
$E$17
Total Contribution Total
9500 22538461.5385
Variable Cells
Cell
Name
Original Value
Final Value
Integer
$B$16
Quantity Mild
1 2512.82051282 Contin
$C$16
Quantity Medium
1 3846.15384615 Contin
$D$16
Quantity Hot
1 179.487179487 Contin
Constraints
Cell
Name
Cell Value
Formula
Status
Slack
$E$10
Machine 1 Total
24000 $E$10>=$G$10 Binding
0
$E$11
Machine 2 Total
36000 $E$11>=$G$11 Binding
0
$E$12
Machine 3 Total
32000 $E$12>=$G$12 Binding
0
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
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Assignment- Cost Accounting.xlsx]Sheet8
Report Created: 2023-07-09 9:17:53 PM
Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$B$16
Quantity Mild
2512.8205128
0
5000
900
3000
$C$16
Quantity Medium
3846.1538462
0
2500
3500 214.28571429
$D$16
Quantity Hot
179.48717949
0
2000 391.30434783 636.36363636
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$E$10
Machine 1 Total
24000 134.61538462
24000 65333.333333 848.48484848
$E$11
Machine 2 Total
36000 57.692307692
36000 1217.3913043 14285.714286
$E$12
Machine 3 Total
32000 538.46153846
32000
25000
14000
Microsoft Excel 16.0 Limits Report
Worksheet: [Assignment- Cost Accounting.xlsx]Sheet8
Report Created: 2023-07-09 9:17:53 PM
Objective
Cell
Name
Value
$E$17
Total Contribution Total
22538461.538
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$B$16
Quantity Mild
2512.8205128
2512.8205128 22538461.538
#N/A
#N/A
$C$16
Quantity Medium
3846.1538462
3846.1538462 22538461.538
#N/A
#N/A
$D$16
Quantity Hot
179.48717949
179.48717949 22538461.538
#N/A
#N/A