Description
I have attached the homework file, and a solved example. Reach out to me if you need more informationFollow the instructions given in the INSTRUCTIONS sheet in the file, and calculate the yellow cells.
Unformatted Attachment Preview
Fixed and Variable Costs
Break even point and cost of various options at a specific volume
Fixed and Variable Costs
►
Fixed costs are costs that continue even if
no units are produced
►
►
Depreciation, taxes, debt, mortgage
payments, building lease
Variable costs are costs that vary with the
volume of units produced
►
Labor, materials, portion of utilities that can
be allocated to a specific unit
Total Cost Calculation
x = number of units produce (volume)
F = sum of fixed costs
V = sum of variable costs per unit
TC (total cost)= F + Vx
All variable costs must have the same unit of
measure usually $. If labor is given in hours per unit
materials in $ per unit and electricity in KW hours
per unit, labor and electricity would have to be
converted to $ per unit.
Total Cost Calculation
Example Option A
x = 100 units
F = Lease cost of $25000
V = Material cost $400 per unit +
electricity cost $5 per unit +
labor cost $50 per unit
TC (total cost)= F + Vx
=$25000 + (100 units x (400+5+50)= $70,500
Total Cost Calculation
Example Option B
x = 100 units
F = Lease cost of $20000
V = Material cost $410 per unit +
electricity cost $5 per unit +
labor cost $60 per unit
TC (total cost)= F + Vx
=$20000 + (100 units x (410+5+60)= $67,500
Total Cost Calculation
Example
TC (total cost) of Option A = $70,500
TC (total cost) of Option B = $67,500
Option B would be the best choice
Four Warehouse options have information about the various costs to help
make a good economic decision on which is the best choice. Use a formula to
calculate each of the yellow boxes on the Warehouse problem page. Note: On
variable cost you should use the cost per pallet info to get your final answer.
You may use cost per pound to calculate but I would not recommend doing
so. At present, the plan is to store 30 million pounds of frozen product in a
leased warehouse. Cells B13, D13, F13, and G13 are the final total cost for
each option. Make sure to show your formulas for full credit. All the
information and calculations may or may not be needed to calculate cost as
there are multiple ways and formulas to get to the correct answers. All of the
yellow and gray highlighted boxes must be populated even if you do not use
them in the final calculation. The text box at the bottom should contain a
brief explanation of why you chose a specific option. All options have good
access to the interstate highway system. Option 4 has a rail dock and the
others do not. The hours of operation at Options 1 and 2 are 5 am to 1 am
while Options 3 and 4 are open from 5:30 am to 1:30 am. Options 1 and 2
use the same outside freight hauler to make deliveries, Option 4 uses a
Warehouse Problem
Lease cost
Pallet charge in
Pallet charge out
Per Pallet storage charge per month
Freight charge per truck from warehouse to
plant
Miles to plant
Option 1
Option 2
$
$
245,000
12.00
12.00
5.00
125.00
15
100,000
14.00
12.50
10.00
150.00
23
Variable cost per pallet
Fixed cost
Total cost at 30 million pounds
Volume (lbs)
30,000,000
Best option at 30 million pounds
Each pallet will incur a pallet charge in, a pallet ch
Why did you select this option?
See instruction tab for further information
Option 3
$
Option 4
750,000
7.50
20.00
100.00
8
150.00
9
20.00
a pallet charge in, a pallet charge out, and a pallet storage charge for one month
lbs per
Pallets per Pallets per Trucks per trucks per trucks per
total pounds
pallet
truck
year
year
month
week
30,000,000
1250
20
Vendor Example of Total Cost O
Option 1
Option 2
Fixed 1
Fixed 2
Fixed 3
$
$
$
1,000
–
$
$
$
1,600
–
Variable 1
Variable 2
Variable 3
$
$
$
12
–
$
$
$
10
–
Variable cost per unit
$
12
$
10
Fixed cost
$
1,000
Option 1 and 2
$
Option 2 and 3
(F2-F1)/(V1-V2)
300
(F2-F1)/(V1-V2)
Option 1 best at
less than 300
units
Why did you select this option?
1,600
450
Option 2 best between
300 and 450
ple of Total Cost Options
Option 3
Units
Option 1
0 $
50 $
100 $
150 $
200 $
250 $
300 $
350 $
400 $
1,000
1,600
2,200
2,800
3,400
4,000
4,600
5,200
5,800
8
450 $
6,400
$
2,500
Option 3 and 1
500 $
550 $
600 $
375 $
7,000
7,600
8,200
5,500
$
$
$
2,500
–
$
$
$
–
$
8
(F2-F1)/(V1-V2)
375
Chart Title
Option 3 best at
greater than 450
$9,000
$8,000
$7,000
$6,000
$5,000
$4,000
$3,000
$2,000
$1,000
$0
100
200
Option 1
300
Option 2
Option 2
$
$
$
$
$
$
$
$
$
1,600
2,100
2,600
3,100
3,600
4,100
4,600
5,100
5,600
Option 3
$
$
$
$
$
$
$
$
$
2,500
2,900
3,300
3,700
4,100
4,500
4,900
5,300
5,700
$
6,100
$
6,100
$
$
$
$
6,600
7,100
7,600
5,350
$
$
$
$
6,500
6,900
7,300
5,500
Chart Title
300
Option 2
400
500
Option 3
600
700
Options 4 and 1
(F2-F1)/(V1-V2)
#DIV/0!
Vendor Example of Total Cost O
Option 1
Option 2
Fixed 1
Fixed 2
Fixed 3
$
$
$
1,000
100
65
$
$
$
1,600
350
–
Variable 1
Variable 2
Variable 3
$
$
$
12
5
–
$
$
$
10
–
Variable cost per unit
$
17
$
10
Fixed cost
$
1,165
Option 1 and 2
$
Option 2 and 3
(F2-F1)/(V1-V2)
112.1428571
(F2-F1)/(V1-V2)
Option 1 best at
less than 300
units
Why did you select this option?
1,950
550
Option 2 best between
300 and 450
ple of Total Cost Options
Option 3
Option 4
Option 5
$
$
$
2,500
–
$
$
$
1,000
100
60
$
6,000
$
$
$
9
–
$
$
$
3
5
$
$
$
8
6
7
$
–
9
$
8
$
21
$
2,500
Option 3 and 1
$
1,160
$
6,000
(F2-F1)/(V1-V2)
166.875
Chart Title
Option 3 best at
greater than 450
$7,000
$6,000
$5,000
$4,000
$3,000
$2,000
$1,000
$0
0.2
0.4
Option 5
Option 6
$
$
1,500
210
$
$
$
12
2
2
$
16
$
1,710
Chart Title
0.6
Option 6
0.8
1
1.2
Options 4 and 1
(F2-F1)/(V1-V2)
#DIV/0!
Purchase answer to see full
attachment
