Question
In: Accounting
Wedgewood Candle Co. Container Candle Division Operating Results for Quarter 2 (Following Year) Revenues $ 5,193,500...
| Wedgewood Candle Co. | ||
| Container Candle Division | ||
| Operating Results for Quarter 2 (Following Year) | ||
| Revenues | $ 5,193,500 | |
| Cost of Goods Sold: | ||
| DM | $ 1,419,075 | |
| DL | $ 354,025 | |
| MOH | $ 1,294,125 | $ 3,067,225 |
| Gross Margin | $ 2,126,275 | |
| Selling and Admin | $ 1,017,500 | |
| Division Operating Income | $ 1,108,775 | |
| % Variable Manufacturing Overhead rate | 40% | |
| % Fixed selling and Administrative costs | 90% | |
| Increase in sales | 30% | |
| Units sold | 325,500 | |
Create a new spreadsheet and name it “CVP”. (6 points) Using all formulas, starting in row 4 Column A where you list the label:
Calculate the contribution margin ratio
Calculate the breakeven in units
Calculate the breakeven in dollars
Calculate margin of safety in dollars
Calculate margin of safety %
Calculate degree of operating leverage
Calculate the increase in net income % if sales increase by 30%
Calculate the dollar increase in net income if sales increase by 30%
Total net income after increase in sales of 30%
Create a new spreadsheet and name it “CVP-Chart. (5 points)Create a table listing quantities and calculating revenues, total costs, fixed costs and variable costs for the quantity listed. Start with quantities of 0 and increase by increments of 50,000 until you get to 600,000.
Use absolute references when calculating sales, variable costs and fixed costs
Using this table, create a breakeven chart – clearly labeling the following
Fixed costs
Total costs
Total revenue
Breakeven point
Loss area
Gain area
X axis
Y axis
Chart title
Create a new spreadsheet and name it “Data Table” and create a what-if data table. (5 points)
Use increments of 45,000 for quantity starting from 0 to 495,000
Use increments of $1 for selling price, starting from 1 to 13
Use conditional formatting to highlight in red the all unprofitable combinations.
Format this spreadsheet with difference colors and labels for each analysis and ensure that it is easy to read. Highlight all final answers in yellow. (1 points)
On each tab of your spreadsheet, in row 1, type in the purpose, procedure and conclusion. You should use center across all columns and wrapping if appropriate. (1 points)
Check Figure: Operating leverage 2.53
Please attach Excel file for the solution.
Solutions
Expert Solution
| 1 | Contribution Margin Ratio | ||||
| Particulars | $ | $ | |||
| Sales | 5193500 | ||||
| Less: Variable costs | |||||
| DM | 1419075 | ||||
| DL | 354025 | ||||
| Variable MOH | 517650 | ||||
| Variable Selling costs | 101750 | 2392500 | |||
| Contribution | 2801000 | ||||
| Contribution Margin ratio | 54% | ||||
| 2 | Break even point (units) | ||||
| Formula | Fixed Costs/contribution per unit | ||||
| Fixed costs | |||||
| MOH | 776475 | ||||
| Selling and admin | 915750 | ||||
| Total Fixed costs | 1692225 | ||||
| contribution | 2801000 | ||||
| No of units sold | 325500 | ||||
| Contribution per unit | 8.61 | (2801000/325500) | |||
| Break even point (units) | 196651 | units | |||
| 3 | Break even point (in $) | Fixed costs/Contribution ratio | |||
| Fixed costs | 1692225 | ||||
| Contribution | 54% | ||||
| Break even Point | 3137654.601 | $ | |||
| 4 | Margin of Safety (in $) | Sales- Break even sales | |||
| Particulars | $ | ||||
| Sales | 5193500 | ||||
| Break even sales | 3137654.601 | ||||
| Margin of safety | 2055845.399 | ||||
| 5 | Margin of safety in % | (Sales- Break even sales)/Sales | |||
| Sales | 5193500 | ||||
| Break even sales | 3137654.601 | ||||
| Marging of safety % | 40% | ||||
| 6 | When sales increases by 30% | ||||
| Current sales Volume | 325500 | units | |||
| Add: 30% increase | 97650 | units | |||
| Increased sales volume | 423150 | units | |||
| Particulars | $ | ||||
| Sales revenue | 6751550 | ||||
| Less: | Variable costs * | ||||
| DM | -1844797.5 | ||||
| DL | -460232.5 | ||||
| VMOH | -672945 | ||||
| Variable selling and admin cost | -132275 | ||||
| Contribution | 3641300 | ||||
| Fixed costs | 1692225 | ||||
| Net operating Income in $ | 1949075 | ||||
| Operating Leverage | Change in operating income/change in sales | ||||
| Particulars | old | Revised | Change | % change | |
| Sales | 5193500 | 6751550 | 1558050 | 0.3 | |
| Operating Income | 1108775 | 1949075 | 840300 | 0.757863 | |
| Operating Leverage | 2.53 | ||||
| Increase in net income in % | 76% | ||||
| Increase in net income in $ | 840300 |
| 2)CVP Chart | |||||||||||||
| Quantity | 0 | 50000 | 100000 | 150000 | 200000 | 250000 | 300000 | 350000 | 400000 | 450000 | 500000 | 550000 | 600000 |
| Revenue-$16 p.u | $ - | $ 8,00,000.00 | $ 16,00,000.00 | $ 24,00,000.00 | $ 32,00,000.00 | $ 40,00,000.00 | $ 48,00,000.00 | $ 56,00,000.00 | $ 64,00,000.00 | $ 72,00,000.00 | $ 80,00,000.00 | $ 88,00,000.00 | $ 96,00,000.00 |
| Total costs | $ 16,92,225.00 | $ 20,59,590.59 | $ 24,26,956.18 | $ 27,94,321.77 | $ 31,61,687.37 | $ 35,29,052.96 | $ 38,96,418.55 | $ 42,63,784.14 | $ 46,31,149.73 | $ 49,98,515.32 | $ 53,65,880.91 | $ 57,33,246.51 | $ 61,00,612.10 |
| Variable costs | |||||||||||||
| DM-$4 per unit | $ - | $ 2,17,983.87 | $ 4,35,967.74 | $ 6,53,951.61 | $ 8,71,935.48 | $ 10,89,919.35 | $ 13,07,903.23 | $ 15,25,887.10 | $ 17,43,870.97 | $ 19,61,854.84 | $ 21,79,838.71 | $ 23,97,822.58 | $ 26,15,806.45 |
| DL-$1 per unit | $ - | $ 54,381.72 | $ 1,08,763.44 | $ 1,63,145.16 | $ 2,17,526.88 | $ 2,71,908.60 | $ 3,26,290.32 | $ 3,80,672.04 | $ 4,35,053.76 | $ 4,89,435.48 | $ 5,43,817.20 | $ 5,98,198.92 | $ 6,52,580.65 |
| VMOH- $1.6 per unit | $ - | $ 80,000.00 | $ 1,60,000.00 | $ 2,40,000.00 | $ 3,20,000.00 | $ 4,00,000.00 | $ 4,80,000.00 | $ 5,60,000.00 | $ 6,40,000.00 | $ 7,20,000.00 | $ 8,00,000.00 | $ 8,80,000.00 | $ 9,60,000.00 |
| Variable selling costs-$0.3 per unit | $ - | $ 15,000.00 | $ 30,000.00 | $ 45,000.00 | $ 60,000.00 | $ 75,000.00 | $ 90,000.00 | $ 1,05,000.00 | $ 1,20,000.00 | $ 1,35,000.00 | $ 1,50,000.00 | $ 1,65,000.00 | $ 1,80,000.00 |
| Variable costs | $ - | $ 3,67,365.59 | $ 7,34,731.18 | $ 11,02,096.77 | $ 14,69,462.37 | $ 18,36,827.96 | $ 22,04,193.55 | $ 25,71,559.14 | $ 29,38,924.73 | $ 33,06,290.32 | $ 36,73,655.91 | $ 40,41,021.51 | $ 44,08,387.10 |
| Contribution | $ - | $ 4,32,634.41 | $ 8,65,268.82 | $ 12,97,903.23 | $ 17,30,537.63 | $ 21,63,172.04 | $ 25,95,806.45 | $ 30,28,440.86 | $ 34,61,075.27 | $ 38,93,709.68 | $ 43,26,344.09 | $ 47,58,978.49 | $ 51,91,612.90 |
| Fixed Costs | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 | $ 16,92,225.00 |
| Net Income | $ -16,92,225.00 | $ -12,59,590.59 | $ -8,26,956.18 | $ -3,94,321.77 | $ 38,312.63 | $ 4,70,947.04 | $ 9,03,581.45 | $ 13,36,215.86 | $ 17,68,850.27 | $ 22,01,484.68 | $ 26,34,119.09 | $ 30,66,753.49 | $ 34,99,387.90 |
| Break even point | Fixed costs/contribution per unit | ||||||||||||
| Fixed cost | $ 16,92,225.00 | ||||||||||||
| Contribution per unit | 9.1 | ||||||||||||
| Break even units | 185959 | units | |||||||||||
| Break even sales | 2975341 | $ | |||||||||||
ekkarill92 answered 1 year agoRelated Solutions
Division Profitability Quarter 1 Quarter 2 Quarter 3 Quarter 4 graph GROSS PROFIT Revenues 0 0...
Division Profitability
Quarter 1
Quarter 2
Quarter 3
Quarter 4
graph
GROSS PROFIT
Revenues
0
0
1,056,604
1,597,853
- Rebates
0
0
16,500
23,750
- Cost of Goods Sold
0
0
410,486
581,268
= Gross Profit
0
0
629,618
992,835
EXPENSES
Store Leases
0
0
90,000
144,000
+ Sales and Service Personnel Expense
0
0
99,571
149,429
+ Brand Promotions
0
0
0
0
+ Special Programs
0
0
0
0
+ Ad Creation/Revision
0
0
24,000
12,000
+ Point...
Blossom Co. reports revenues of $201,000 and operating expenses of $109,000 in its first year of...
Blossom Co. reports revenues of $201,000 and operating expenses
of $109,000 in its first year of operations, 2017. Accounts
receivable and accounts payable at year-end were $71,500 and
$28,800, respectively. Assume that the accounts payable related to
operating expenses. (Ignore income taxes.)
Using the direct method, compute net cash provided (used) by
operating activities. (Show amounts that decrease cash
flow with either a - sign e.g. -15,000 or in parenthesis e.g.
(15,000).)
Net cash
usedprovided
by operating activities
$
Situation...
Last year’s results for the Oak Division of Forest Company are: Average operating assets  
Last year’s results for the Oak Division of Forest Company
are:
Average operating
assets $250,000
Operating
profit
45,000
Sales
300,000
The required cost of capital for Forest Company is 15%.
Required: Answer the following questions:
1. Compute the residual income for the Oak Division.
2. Compute the return on investment for the Oak Division.
3. Calculate the profit margin ratio and asset turnover ratio
for Oak Division using the original numbers.
4. Would Oak Division choose to increase profit by $20,000...
HighTech, Inc., and OldTime Co. compete within the same industry and had the following operating results...
HighTech, Inc., and
OldTime Co. compete within the same industry and had the following
operating results in 2015:
HighTech, Inc.
OldTime Co.
Sales
$
3,300,000
$
3,300,000
Variable
expenses
660,000
1,980,000
Contribution
margin
$
2,640,000
$
1,320,000
Fixed
expenses
2,140,000
820,000
Operating
income
$
500,000
$
500,000
Required:
a.
Calculate the break-even point for each firm in terms of revenue.
(Do not round intermediate calculations.)
b.
What observations can you draw by examining the break-even point of
each firm given...
HighTech, Inc., and OldTime Co. compete within the same industry and had the following operating results...
HighTech, Inc., and OldTime Co. compete within the same industry
and had the following operating results in 2015:
HighTech, Inc.
OldTime Co.
Sales
$
3,500,000
$
3,500,000
Variable expenses
700,000
2,100,000
Contribution margin
$
2,800,000
$
1,400,000
Fixed expenses
2,270,000
870,000
Operating income
$
530,000
$
530,000
Required:
a. Calculate the break-even point for each firm
in terms of revenue. (Do not round intermediate
calculations.)
b. What observations can you draw by examining
the break-even point of each firm given...
HighTech, Inc., and OldTime Co. compete within the same industry and had the following operating results...
HighTech, Inc., and OldTime Co.
compete within the same industry and had the following operating
results in 2012:
HighTech,
Inc.
OldTime
Co.
Sales
$
2,300,000
$
2,300,000
Variable
expenses
490,000
1,400,000
Contribution
margin
$
1,810,000
$
900,000
Fixed expenses
1,570,000
650,000
Operating
income
$
240,000
$
250,000
Required:
a.
Calculate the break-even point for each firm in terms of
revenue. (Do not round intermediate calculations and round
your final answers to the nearest whole dollar.)
b.
What observations...
Year 1 Year 2 Revenues 128.9 151.6 COGS and Operating Expenses (other than depreciation) 49.6 52.7...
Year 1 Year 2
Revenues 128.9 151.6
COGS and Operating Expenses (other than depreciation) 49.6
52.7
Depreciation 24.9 43.5
Increase in Net Working Capital 3.4 8.1
Capital Expenditures 27.3 43.2
Marginal Corporate Tax Rate 35% 35%
a. What are the incremental earnings for this project for years
1 and 2? (Note: Assume any incremental cost of goods sold is
included as part of operating expenses.)
b. What are the free cash flows for this project for years 1
and 2?...
Year 1 Year 2 Revenues 120.8 150.6 COGS and Operating Expenses (other than depreciation) 46.1 54.5...
Year 1
Year 2
Revenues
120.8
150.6
COGS and Operating Expenses (other than depreciation)
46.1
54.5
Depreciation
24.9
32.9
Increase in Net Working Capital
2.8
8.2
Capital Expenditures
29.2
38.4
Marginal Corporate Tax Rate
35%
35%
a. What are the incremental earnings for this project for years
1 and 2? (Note: Assume any incremental cost of goods sold is
included as part of operating expenses.)
b. What are the free cash flows for this project for years 1
and 2?
Year 1 Year 2 Revenues 126.1126.1 155.6155.6 COGS and Operating Expenses (other than depreciation) 36.236.2 57.157.1...
Year 1
Year 2
Revenues
126.1126.1
155.6155.6
COGS and Operating Expenses (other than depreciation)
36.236.2
57.157.1
Depreciation
28.128.1
39.139.1
Increase in Net Working Capital
2.32.3
8.68.6
Capital Expenditures
28.528.5
37.637.6
Marginal Corporate Tax Rate
3535%
3535%
a. What are the incremental earnings for this project for years
1 and 2? (Note: Assume any incremental cost of goods sold is
included as part of operating expenses.)
b. What are the free cash flows for this project for years 1
and 2?...
2. [EXCEL] Karl Duncker’s results on his ‘Candle Problem’ were published posthumously in 1945. Participants were...
2. [EXCEL] Karl Duncker’s results on his ‘Candle Problem’ were
published posthumously in 1945. Participants were asked to mount a
candle on a wall in an upright position so that it would burn
normally. One group of participants was given a candle, a book of
matches, and a box full of tacks. A different, independent group of
participants were given the same items, except that the box and the
tacks were presented separately. The solution is to use the tacks...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Asteroids can be classified into two broad groups based on their composition and location: carbon-rich asteroids...
- 5._____ Which of the following statements about MBO is FALSE? A) It compares quantifiable target goals...
- Trust, or lack of trust, is an increasingly important issue in today’s organizations.71 In today’s uncertain...
- In this programming assignment, you will write C code that performs recursion. For the purpose of...
- Suppose you purchase one WM May strike=102 call contract at $5 and write one WM May...
- The three call options issued by Tesla Inc are listed below. Suppose that the current price...
- 1. Explain why times of economic distress caused classical liberalism to fall out of favor.
ADVERTISEMENT