Logo
Home Study Material Contact Search
AI Chat FREE
  1. business
  2. operations management
  3. please submit as a single excel file. to receive c

Please submit as a single Excel file. To receive credit, appropriate detail needs to be provided....

Free

70.2K

Verified Solution

Question

Operations Management

Please submit as a single Excel file. To receive credit,appropriate detail needs to be provided. In addition, aprofessional presentation of findings is important.

#1       Determine the total cost forthis plan given the following forecast:

Month 1 2      3      4      5       6

Forecast 380   400   420  440   460   480

Use steady regular output of 400 units per month, use overtimeas needed for up to 40 units per month, and use subcontracting tomake up any needed output to match the forecast. Unit costsare:

Regular output $25

Overtime $40

Subcontract $60

Average Balance Inventory $15

Answer & Explanation Solved by verified expert
4.5 Ratings (839 Votes)

Month Forecast Production Level Inventory Adjusted inventory Overtime production Required Subcontracting Quantity Regular cost Inventory Cost Overtime cost Subcontract cost Total cost
1 380 400 20 20 0 0 $10,000 $300 $0 $0 $10,300
2 400 400 0 20 0 0 $10,000 $300 $0 $0 $10,300
3 420 400 -20 0 0 0 $10,000 $0 $0 $0 $10,000
4 440 400 -40 0 40 0 $10,000 $0 $1,600 $0 $11,600
5 460 400 -60 0 20 20 $10,000 $0 $800 $1,200 $12,000
6 480 400 -80 0 40 40 $10,000 $0 $1,600 $2,400 $14,000
Unit costs
Regular $25
Overtime $40
Subcontract $60
Inventory $15

The formula for excel calculation

Month Forecast Production Level Inventory Adjusted inventory Overtime production Required Subcontracting Quantity Regular cost Inventory Cost Overtime cost Subcontract cost Total cost
1 380 400 =D3-C3 20 0 =IF(ABS(E3)>40,ABS(E3)-40,0) =D3*$C$12 =F3*$C$15 =G3*$C$13 =H3*$C$14 =SUM(I3:L3)
2 400 400 =D4-C4 20 0 =IF(ABS(E4)>40,ABS(E4)-40,0) =D4*$C$12 =F4*$C$15 =G4*$C$13 =H4*$C$14 =SUM(I4:L4)
3 420 400 =D5-C5 =IF(E5<0,IF(F4>0,E5+F4,0),0) 0 =IF(ABS(E5)>40,ABS(E5)-40,0) =D5*$C$12 =F5*$C$15 =G5*$C$13 =H5*$C$14 =SUM(I5:L5)
4 440 400 =D6-C6 =IF(E6<0,IF(F5>0,E6+F5,0),0) =IF(E6>=-40,ABS(E6),ABS(E6-(-40))) =IF(ABS(E6)>40,ABS(E6)-40,0) =D6*$C$12 =F6*$C$15 =G6*$C$13 =H6*$C$14 =SUM(I6:L6)
5 460 400 =D7-C7 =IF(E7<0,IF(F6>0,E7+F6,0),0) =IF(E7>=-40,ABS(E7),ABS(E7-(-40))) =IF(ABS(E7)>40,ABS(E7)-40,0) =D7*$C$12 =F7*$C$15 =G7*$C$13 =H7*$C$14 =SUM(I7:L7)
6 480 400 =D8-C8 =IF(E8<0,IF(F7>0,E8+F7,0),0) =IF(E8>=-40,ABS(E8),ABS(E8-(-40))) =IF(ABS(E8)>40,ABS(E8)-40,0) =D8*$C$12 =F8*$C$15 =G8*$C$13 =H8*$C$14 =SUM(I8:L8)
Unit costs
Regular 25
Overtime 40
Subcontract 60
Inventory 15

Please comment if any doubt you have
Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:
  • Unlimited Question Access with detailed Answers
  • Zin AI - 3 Million Words
  • 10 Dall-E 3 Images
  • 20 Plot Generations
  • Conversation with Dialogue Memory
  • No Ads, Ever!
  • Access to Our Best AI Platform: Zin AI - Your personal assistant for all your inquiries!
Become a Member

Other questions asked by students

Q

A beaker with 195 mL of an acetic acid buffer with a pH of 5.000 is...

Chemistry
Q

. Explain why public organizations are said to be more difficult to evaluate than private organizations

General Management
Q

What is the difference between sonication and lyophilization Choose all that apply Sonication uses ultrasonic...

Biology
Q

1 Graph f x x 2 What are the domain and range of the function

Basic Math
Q

aluate the integral by making an appropriate change of variables 7 Cos 5 YX dA...

Calculus
Q

Compute the directional derivative of the function g x y sin x x 3y at...

Calculus
Q

Tony is going to invest to help with a down payment on a home How...

Algebra
Q

When the value of pension plan assets is greater than the...

Accounting
Q

4. Financial Accounting is done for providing information to the external stakeholders while the analysis...

Accounting
Q

1. Compile 5 well thought questions that you would like to ask management based on...

Accounting
Q

Units to be produced 6000 Total pounds needed for production 120000 Total Materials required 14900...

Accounting
Q

I have a balance sheet with these items listed: ...

Accounting