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: Flex AI - Your personal assistant for all your inquiries!
Become a Member

Other questions asked by students

Advance Math
1.2K views

Solve the initial value problem: y''' - 12y'' + 48y' - 72y = 0 ; y(0)...

General Management
1.2K views

Differentiate between R&D project management and conventional project management.

Biology
1.2K views

How can there be so many similarities between organisms, but also so many different types...

Physics
1.2K views

A 100 32 resistance and a capacitor of 100 Q reactance are connected in series...

Calculus
1.2K views

Find an equation for the level curve of the function f x y that passes...

Algebra
1.2K views

Survey researchers design and conduct surveys and analyze data Some survey researchers design public opinion...

Accounting
1.2K views

When an investor owns between 20% and 50% of the common stock of a corporation,...

Accounting
1.2K views

Which one of the following transactions does NOT involve a cash flow? A prepayment of...

Accounting
1.2K views

Galen owns a rental house, which he rents at 250 per month. Similar house in...

Accounting
1.2K views

May B.D. Whispering invests $3,680 cash in exchange for common stock in Whispering Company, a...

Accounting
1.2K views

rou place an order for 1,500 units of inventory at a unit price of $52....

Finance
1.2K views

A risk-averse investor has the following two stocks, A and B, their characteristics are presented...