Create a new spreadsheet and name it “CVP”. (6 points) Using all formulas, start
ID: 2549701 • Letter: C
Question
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.
Explanation / Answer
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 $
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.