Question

ACC 202 Final Project Workbook - Excel - AutoSave Off HO File Home Insert B Draw : Page Layout Formulas Data Review View Help Search 0 X Comments 4 Ideas Calibri === - 10 - AA - - A, AutoSum 47 On E Lori Foster LF 0 Share 0 Fill Sort & Find & Clear Filter - Select Editing a Wrap Text Merge & Center - Accounting $ $,% % , a Paste BIU - 4 68 .00 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells Ideas D23 ДА В G H I J K L M N O P Q R D E F Show all steps and calculations to determine the break-even. Determine the break-even for the target profit levels as outlined in the instructions. Break-even = Fixed Costs / Contribution Margin DAY CARE 18.00 BOARDING 25.00 GROOMING 25.00 Sales Price $ $ Fixed Costs $ 858 $ 1,379 $ 2,368 Contribution Margin 13.25 $ 17.64 $ 15.03 Break-even Units (round up) E Target Profit $ 417.00 $ 583.00 $ 1,000.00 Break-even Units (round up) 96.00 111.00 224.00 Target Profit $ 667.00 $ 909.00 1,500.00 Break-even Units (round up) 115.00 130.00 257.00 Instructions - Milestone 2 Contribution Margin Analysis Cost Classification Variable_Fixed Break-Even Analysis Instructions - Milestone 3 Ready COS ... O ^ - + 100% - a 4) 9:36 PM Start a web search 1/28/2020 20

Answer 1

Fixed costs Break even = - Contribution margin Target profit + Fixed cost Unit contribution margin Uniit sales to attain the target profit = BOARDING DAY CARE GROOMING Sales price $25.00 $25.00 $18.00 Fixed costs $1,379 $858 $2,368 Contribution margin $13.25 $17.64 $15.03 Break-even units 65 79 158 $583.00 Target profit $417.00 $1000.00 Break-even units 97 112 225 $1,500.00 Target profit $667.00 $909.00 Break even units 116 130 258

To obtain the break-even units, fixed costs is divided by the contribution margin. The formula is inserted in cell H23

Bookl - Excel Home Insert Page Layout Formulas Data Review View Power Pivot V Tell me what you want to do... File X Cut Calibri 11 'Wrap Text General ED Copy Paste BI U $ - % Conditional Format 6.0 .00 C00 .0 Merge & Center , Format Painter Formatting- Table Styles Clipboard Font Alignment Number fe =ROUNDUP(H18/H20,0) H23 н Fixed costs Break even = 3 Contribution margin 5 Target profit + Fixed cost Unit contribution margin Unit sales to attain the target profit 10 11 12 DAY CARE BOARDING GROOMING 13 14 Sales price $18.00 $25.00 $25.00 15 16 17 Fixed oosts $858 $1,379 $2,368 18 19 Contribution margin $13.25 $17.64 $15.03 20 21 22 Break-even units 65 79 158 23 24

O Tell me what you want to do... Home Insert Page Layout Formulas Data Review View Power Pivot File Define Name- - Trace Precedents fx Σ A Use in Formula- Trace Dependents Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name R Remove Arrows Function Time Reference Trig- Functions Manager E Create from Selection Used - Function Library Defined Names Formula fe =ROUNDUP(H18/H20,0) COUNTIF н Fixed costs Break even = Contribution margin 4 Function Arguments ROUNDUP 10 11 = 64.75471698 Number Н18/Н20 12 GROOMING 13 Num_digits o 14 $25.00 15 = 65 16 Rounds a number up, away from zero. 17 Number is any real number that you want rounded up. 868 18 19 $15.03 20 21 22 Formula result = 65 158 23 24 Heln on this funtion ок Cancel

To obtain the break-even units for the target profit, the formula is entered into cell H32.

O Tell me what you want to Review Home Insert Page Layout Formulas Data View Power Pivot File O Define Name - Trac fx Σ Use in Formula- o Trac Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name R Rem Create from Selection Time- Reference Trig Functions Manager Used - Function Function Library Defined Names fe =ROUNDUP((H29+H18)/(H20),0) Н32 н 10 11 12 GROOMING DAY CARE BOARDING 13 14 Sales price $18.00 $25.00 $25.00 15 16 17 Fixed costs $858 $1,379 $2,368 18 19 Contribution margin $13.25 $17.64 $15.03 20 21 22 Break-even units 65 79 158 23 24 25 26 27 28 $1,000.00 Target profit $417.00 $583.00 29 30 31 Break-even units 97 32 33

To obtain the break-even units for the target profit, the formula is entered into cell H40.

$ - % Conditional Format as Paste в IU- +.0 ,00 .00 .0 Ce E Merge & Center , Format Painter Formatting - Table Style Clipboard Font Alignment Number Styles fe =ROUNDUP((H37+H18)/(H20),0) Н40 н BOARDING GROOMING DAY CARE 13 14 Sales price $25.00 $18.00 $25.00 15 16 17 $858 Fixed costs $1,379 $2,368 18 19 Contribution margin $13.25 $17.64 $15.03 20 21 22 Break-even units 65 79 158 23 24 25 26 27 28 Target profit $417.00 $583.00 $1,000.00 29 30 31 Break-even units 97 112 225 32 33 34 35 36 Target proft $667.00 $909.00 $1,500.00 37 38 39 1161 Break even units 130 258 40