REVIEW VIEW FILE HOME INSERT PAGE LAYOUT FORMULAS DAN 1 X cut MS Sans Serif 10 DA * = = U Copy - Paste - Format Painter BIU- -A. Clipboard Font Wrap Tert + Merge Center - $ % ) 1 2 Conditional Formatas Formatting Table Alignment Number C D E H 4 A B 1 SHADELL MITCHELL 2 2865 I PART 2 Cost Volume Relationships - Profit Planning 8 Big Al is about to begin work on the budget for 20x2 and they have requested that you prepare an analysis based on the following assumptions 10 Note: Remember, that we cannot sell part of a lamp, therefore to find the number of units you have to round 11 up to the next complete unit. Furthuremore, to find the required sales in dollars it may be easier to find the 12 number of units and then multiply by the selling price per unit. 15 16 1. For 20x2 the selling price per lamp will be $45.00. What is the projected contribution margin and contribution margin ratio for each lamp sold? ) I Contribution Margin per unit (Round to seven places. St 5 6 7 8 9 S 19.2400000 15 16 17 (501) 18 ... 10 11 12 13 14 JE FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW 1 X cut MS Sans Serif 10 HAA Wrap Text e Copy Paste Format Painter BTU - SAEEE Mere Merge & Center - Clipboard Font Alignment 133 - X fr $ - % , * Conditio Formattin Number 31 32 33 Contribution Margin Ratio (Round to seven places.% is two of those places MN %) 42.76000% 5.02) 36 37 2. For 20x2 the selling price per lamp will be $45.00. How many lamps must be sold to breakeven? Breakeven sales in units (Round up to zero places, www units) (503) 59 3. For 20x2 the selling price per lamp will be $45.00. The desired net income in 20x2 is $274,000. What 60 would sales in units have to be in 20x2 to reach the profit goal? 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ... FILE HOME INSERT PAGE LAYOUT FORMULAS DAN Xcut MS Sans Serif 10A A = == Copy Format Painter BIU ->AEE Clipboard Font REVIEW VIEW . Wrap Text Merge Center - Alignment *8.99 $ . % Number Conditional Formatting Number A B C D 59 3. For 20x2 the selling price per lamp will be $45.00. The desired net income in 20x2 is $274,000. What would sales in units have to be in 20x2 to reach the profit goal? Sales in units (Round up to zero places, ######units) 1504 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ... MS Sans Serif 18.5AA o Wrap Text ob Cut e Copy Format Painter Paste BIU- - - A- Merge & Center - $ . % *8.99 Conditional Formatting Number Clipboard Alignment A1 1 x fx 4 2 2065 4 4. For 20x2 the selling price per lamp will be $45.00. The company would like to have a net income equal to 23.00% of sales. If that is to be achieved, what would be the sales in units in 20x2? {6.01) 19 Sales in units (Round up to zero places, ### ### units) 25 5. If the company believed that it could only sell 25,000 lamps, what would the new selling price have to be so that the new contribution margin per unit is equal to last year's contribution margin per unit? New Selling Price (Round up to two places, S######## {6.02} 18 ... ... 1 2 3 4 5 8 6 7 9 16 17 10 13 14 15 11 12 READY SMWYM Compatibility Mode] - Excel DATA REVIEW VIEW FILE HOME INSERT PAGE LAYOUT X Cut MS Sans Serif - 85 Copy Paste Format Painter BIU EB- Clipboard A1 X fx A B C D FORMULAS A = A. E Wrap Text Merge & Center - = = E + $ % , Condition Formatti Alignment Number E F G H 41 6. For 20x2 the selling price per lamp will be $45.00. How many units must be sold to generated a net income of $200,000? 43 Sales in units (Round up to zero places, ## ### units) (6.03] 60 7 If the company believes that the demand will be 27.500 units for the year. What selling price per lamp, rounded to two places, would generate a net income of $823.500? New selling price per lamp (Round up to two places. S####### {6.04 ... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ... READY * Type here to search o e o MULAD NEVE Wrap Text Cut Da Copy Format Painter Clipboard MS Sans Serif 10 === BIU... AF Font E Merge & Center - $ . % 8.99 Conditional Fo Formatting Alignment Number A B C D E PART 2 Cost Volume Relationships - Profit Planning Big Al is about to begin work on the budget for 20x2 and they have requested that you prepare an analysis based on the following assumptions. Note: Remember, that we cannot sell part of a lamp, therefore to find the number of units you have to round up to the next complete unit. Furthuremore, to find the required sales in dollars it may be easier to find the number of units and then multiply by the selling price per unit 1. For 20x2 the selling price per lamp will be $45.00. What is the projected contribution margin and contribution margin ratio for each lamp sold? Contribution Margin per unit (Round to seven places, $ #) $19.2400000 {5.01} Contribution Margin Ratio (Round to seven places, % is two of those places ### ##%) 42.76000% {5.02) ... 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Present Value T... DAN REVIEW VIEW Ja Copy 2. Wrap Text FILE HOME INSERT PAGE LAYOUT FORMULAS Cut MS Sans Serif - 10 JAA Paste Format Painter B IU . . . A. Clipboard Font 133 - X for A B C D E F 3 Merge Center . $. % . 3 Formatting Alignment Number Table Styles 32 Contribution Margin Ratio (Round to seven places, % is two of those places ww w %) 42 76000% (502) 36 37 2. For 20x2 the selling price per lamp will be $45.00. How many lamps must be sold to breakeven? Breakeven sales in units (Round up to zero places, ### #units) {5.03 59 3. For 20x2 the selling price per lamp will be $45.00. The desired net income in 20x2 is $274,000. What would sales in units have to be in 20x2 to reach the profit goal? .. 4 5 6 7 8 9 10 11 12 13 15 16 17 18 Present Value T... 14 . READY Formatting FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Cut MS Sans Serif 10 AA Wrap Text Je Copy Paste - Format Painter BIU - A. Et Merge & Center - $ . % ; *88 Clipboard Font Alignment Number 133 : X fr A B C D E 54 57 58 59 3. For 20x2 the selling price per lamp will be $45.00. The desired net income in 20x2 is $274,000. What would sales in units have to be in 20x2 to reach the profit goal? Sales in units (Round up to zero places, ### ### units) (5.04 . 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Present Value T ... READY 4 4. For 20x2 the selling price per lamp will be $15.00. The company would like to have a net income equal to 23.00% of sales. If that is to be achieved, what would be the sales in units in 20x2? Sales in units (Round up to zero places, ### ### units) {6.01) 25 5. If the company believed that it could only sell 25,000 lamps, what would the new selling price have to be so that the new contribution margin per unit is equal to last year's contribution margin per unit? New Selling Price (Round up to two places, S ... 4 5 6 7 8 9 10 .## 11 12 13 14 15 16 17 {6.02 18 Present Value T... READY WA NEEEYYY Wrap Text Merge & Center - = $ . % *8 8 Conditional Formatting Cut MS Sans Serif -8.5 AN Copy - Paste *Format Painter B IU. - A Clipboard Font X fir IBC Alignment Number Sales in units (Round up to zero places, ### # units) (6.03) If the company believes that the demand will be 27.500 units for the year. What selling price per lamp, rounded to two places, would generate a net income of $823,500? New selling price per lamp (Round up to two places, $########) {6.04 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Present Value T... READY E F G H See The Light Projected Income Statement For the Period Ending December 31, 20x1 $ 1,125,000.00 723,250.00 $ 401,750.00 Sales 25,000 lamps @ $45.00 Cost of Goods Sold @ $28.93 Gross Profit Selling Expenses: Fixed Variable (Commission per unit) @ $3.15 Administrative Expenses: Fixed Variable @ $0.06 Total Selling and Administrative Expenses Net Profit $ 23,000.00 78.750.00 $ 101,750.00 $ 42,000.00 1,500.00 43,500.00 145,250.00 256,500.00 $ HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Spy Arial BIU - -10 - A A - A. Es Wrap Text Merge & Center - $ . % 48. ormat Painter sard Font Alignment Number B C D I See The Light Projected Balance Sheet As of December 31, 20x1 34.710.00 67,500.00 Current Assets Cash Accounts Receivable Inventory Raw Material Figurines Electrical Sets Work in Process Finished Goods Total Current Assets 500 @ 500 @ $9. $1.25 4,600.00 625.00 3000 @ $28.93 86.790.00 194 225.00 Fixed Assets Equipment Accumulated Depreciation Total Fixed Assets Total Assets $ 20,000.00 6,800.00 $ 13,200.00 207 425.00 Current Liabilities Accounts Payable Total Liabilities Stockholder's Equity Common Stock 2 3 4 5 $ $ 54,000.00 54,000.00 12 S. 12 000 13 . 14 15 16 17 18 Pres... SM2594 (Compatibility Mode] - Excel REVIEW VIEW Wrap Text OME INSERT PAGE LAYOUT FORMULAS DAN Arial -10 A N = py - mat Painter BISA FE= Font Merge & Center - $ . % *222 Cond Form 2 Number LE 1 $ 34,710.00 67,500.00 B C D Current Assets Cash Accounts Receivable Inventory Raw Material Figurines Electrical Sets Work in Process Finished Goods Total Current Assets 500 @ 500 @ $9.20 $1.25 4,600.00 625.00 3000 @ $28.93 86.790.00 194 225.00 $ Fixed Assets Equipment Accumulated Depreciation Total Fixed Assets Total Assets $ 20,000.00 6,800.00 $ 13.200.00 207.425.00 54 000.00 54,000.00 Current Liabilities Accounts Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equit $ 12,000.00 141.425 00 153 425.00 $ 207425 00 PART 1 Fixed and Variable Cost Determinations Unit Cost Calculations 8 The projected cost of a lamp is calculated based upon the projected increases or decreases to 9 current costs. The present costs to manufacture one lamp are 10 17 18 19 Figurines Electrical Sets Lamp Shade Direct Labor Variable Overhead: Fixed Overhead: $9.2000000 per lamp 1.2500000 per lamp 6.0000000 per lamp 2.2500000 per lamp (4 lamps/hr.) 0.2250000 per lamp 10.0000000 per lamp (based on normal capacity of 25.000 lamps) Cost per lamp: $28.9250000 per lamp 31 32 Expected increases for 20x2 39 When calculating projected increases round to SEVEN decimal places.S0.0000000. + ... 2 3 4 5 6 7 8 9 10 11 12 13 READY 14 15 17 18 o e o -8.5 A A = D. E Wrap Text Merge & Center - - - A-EE E $. ob Cut MS Sans Serif to Be Copy aste Format Painter BIU - Clipboard 1 :X fr A B C Font Alignment D E F G 2 Expected increases for 20x2 When calculating projected increases round to SEVEN decimal places, 50.0000000. 1. Material Costs are expected to increase by 2.00% WN- 2. Labor Costs are expected to increase by 3.00% 3. Variable Overhead is expected to increase by 4.50%. 4. Fixed Overhead is expected to increase to $270,000 5. Fixed Administrative expenses are expected to increase to $60,000. 8 3 9 6. Variable selling expenses (measured on a per lamp basis) are expected to increase by 3.50% 7. Fixed selling expenses are expected to be $39,000 in 20x2 NOS 8. Variable administrative expenses (measured a per lamp basis) are expected to increase by 5.00% 83 On the following schedule develop the following figures 1- 20x2 Projected Variable Manufacturing Unit Cost of a 2- 20x2 Projected Variable Unit Cost per lamp SE O 86 READY SM2594 [Compatibility Me DATA REVIEW VIEW = = Wrap Text Merge & Center - ali IBS = FILE HOME INSERT PAGE LAYOUT FORMULAS * Cut MS Sans Serif -8.5 A A = Copy - 4 Format Painter BTU : A Clipboard Font A1 : X fix A B C D E 2. Labor Costs are expected to increase by 3.00% E $ - 9 Alignment Number TI F G 3. Variable Overhead is expected to increase by 4.50%. 4. Fixed Overhead is expected to increase to $270,000 5. Fixed Administrative expenses are expected to increase to $60,000. 52 6. Variable selling expenses (measured on a per lamp basis) are expected to increase by 3.50%. 7. Fixed selling expenses are expected to be $39.000 in 20x2. 73 8. Variable administrative expenses (measured a per lamp basis) are expected to increase by 5.00%. 83 On the following schedule develop the following figures: 1- 20x2 Projected Variable Manufacturing Unit Cost of a lamp 2- 20x2 Projected Variable Unit Cost per lamp 3- 20x2 Projected Fixed Costs. w I See The Light, Inc Schedule of Projected Costs 9 Variable Manufacturing Unit Cost 20x1 Cost Projected Percent Increase 20x2 Cost Rounded to 7 Decimal Places 9.38400001 1.2750000 6.1200000 2 2950000 0 .2295000 {4.01) (4.02) {4.03) (404) {4.05} 1 11 Figurines 13 Electrical Sets 14 Lamp Shade 15 Labor 16 Variable Overhead 17 19 Projected Variable Manufacturing Cost Per Unit 20 121 22 23 Total Variable Cost Per I Init ... 2 3 4 5 READY 19.3035000 [4.06] 10 11 12 13 14 15 16 17 18 Pres. SM2594 [Compatibility Mode] - Exce FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Arial - 10 -A N = == Wrap Text Paste Cut Copy Format Painter Clipboard B I U E Merge & Center - $ - % Alignment Number DAG :X 23 Total Variable Cost Per Unit 20x1 Cost Projected Percent Increase 25 26 Variable Selling 27 Variable Administrative 28 Projected Variable Manufacturing Unit Cost 20x2 Cost Rounded to 7 Decimal Places 3.2130000 0.0612000 19.3035000 {4.07) (4.08} (4.06} 29 1 2 2.5777000) {4.097 32 Projected Total Variable Cost Per Unit 33 37 Schedule of Fixed Costs 20x1 Cost Projected Percent Increase ED 20x2 Cost Rounded to 2 Decimal Places 270000.00 4.103 38 39 Fixed Overhead 40 (normal capacity of _ 41 Fixed Selling 43 Fixed Administrative _ lamps @_) 3900000 6000000 [4.11] [4.12} 45 Projected Total Fixed Costs ... 2 3 4 369000.00 16 17 (413) 18 Pres... 5 6 7 8 9 13 14 15 READY FILE PAGE LAYOUT HOME INSERT * Cut Copy Format Painter Clipboard Ariol 110 SM2594 [Compatibility Mode] - FORMULAS DATA REVIEW VIEW A * ===> Wrap Test Et Merge & Center - $ - % , *8 Alignment Paste B TU- Alignment Number D46 . X for B C D 37 Schedule of Fixed Costs 20x1 Cost Projected Percent ent Increase 20x2 Cost Rounded to 2 Decimal Places 2 70000.00 S {4.103 _lamps @ 39 Fixed Overhead 40 (normal capacity of _ 41 Fixed Selling 43 Fixed Administrative 44 45 Projected Total Fixed Costs P 39000.00 60000.00 [4.11] {4.12) D 369000.00 - {4.13) 6 7 8 9 14 15 16 17 18 Pres READY