File Home Insert Calibri - A A ACC 202 Final Project Workbook-1 - Excel Page Layout Formulas Data Review View Help O Search 110 > 29 Wrap Text General a-A Es Merge & Center - Conditional $ -%88- Formatting Font Alignment Number 9. Paste B1 U- Clipboard AutoSave O N21 * AB D MILESTONE 2 - Break-Even Analysis 5 6 7 B 9 10 11 12 13 INSTRUCTIONS: 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 Martin DAY CARE BOARDING GROOMING 15 17 Sales Price 3 10 Foxed Costs 5 Contribution Margin $ 5 $ 21 22 25 24 Break even Uits round upl 26 27 Target Pro 5 41700 5 583.00 1.000.00 Cotton Martina Break-liven Analys Type here to search O i C 120 16 69 ACC 202 Final Project Workbook-1 - Excel File Home Insert Page Layout Formulas Review View Help Search Paste Calibri 10 ' ' 2 Wrap Test BIU- OA FEE Merge de Center Fant Alarment = General $ %) *** Clipboard 15 AutoSave Conditional Format Formatting Table Styles Number N21 11 Breakevend Costs/Contribution Margin 13 14 15 DAY CARE BOARDINO GROOMING Sale Price $ $ 17 18 19 20 Fixed Costs Contribution Marin 5 3 22 23 24 Break even Units round up) 26 27 21 29 Torpet Profit 41700 589.00 1.000.00 Besteven Units fround 56700 000 1:50000 Bralinis round G C Even Analysis Pios 3 type here to search O FI 16 15 16 cm 17. 3 Clipboard Formatting Alignment Font 9. Number 5 MutoSave LSO X D MILESTONE 1 - Variable & Fixed Cost Exercise 4 AB 4 5 6 2 INSTRUCTIONS: Determine the per unit cost for each dog Fill in the blanks to get the per unit cost and fixed cost of each service. 9 10 11 12 Based on 5 rooms per day GROOMING item Shampoo Clippers Bowls Tourels Scissors 14 16 17 IR 19 20 Item Groomer Variable costs 101 137 0.72 5.83 1.01 Loan Utilities and Insurance Depreciation on heating system Cleaning Products: Odoban Cleaning Products: Sple Green Orow Total Fed Costs Fixed Costs 2.05000 3095 20.00 2857 305 2.08 2.36 200.00 2362291 Total Variable costs 25 25 9.97 5 39 Based on 22 dogs per day for six days a weekAsume 25 operating down for dennel and 22 hour work days for a fendant DAY CARE Variable Find Type here to search O 1 82 15 16 ce 5 2 3 4 5 & 7 6 8 W E R. U S D F G H file Home nsert Page Layout Formulas ACC 202 Final Project Workbook 1. Excel Help O Search Data View Calion Wrap Text Paste 10 - A A AAE General E BIU- Merge Center $ - % -88 Clipboard Font Conditional Format Formatting Table Styles Algement Number AutoSave - 150 4 26 29 23 14 35 36 Based on 22 dogs per day for six days a week. Assume 25 operating days for Kennel and 22 eight hour work days for kennel attendant DAY CARE Item Variable costs Fixed costs Day care attendant s 28 Rent $ Toys 232.14 0.27 Loan Bowls 150 00 0.10 Utilities and Insurance Towels 21420 140 Depreciation on heating system 29.64 Cleaning Products: Odoben 15 52 Cleaning Products: Simple Green 16.78 Draw Total Variatile Costa 4.65 Total Fixed Costs S 58.14 37 38 39 41 20000 95 27 49 50 Based on 12 dogs per day. Assume 22 sight hour work days for keel attendant BOARDINO tem Vale Costa Kennel dont 562 Doction on me Bow's CE Rent Towels 0.37 Utiline Depreciation on heating system Cenrouts Odoban Variable Fixed $ Couts 0-00 360 250 od 357.14 49.40 25 53 54 Type here to search OG i O E 15 17 S VA 2 2 3 5 7 w E ACC 202 Final Project Workbook-1 - Excel File Home Insert Page Layout Formulas Dot Review View Help O Search General UN Paste Calibri -10 - AA - Wrap Text BLU-O-A E E Merge a Center $ - % 38 Clipboard Conditional Form Formatting - Tab Styles Font Alignment Number AutoSave 0 150 X $ 33 34 35 BV C Day care attendant Toys dowi Towels 288 0.27 0.10 1.40 Hent Loan Utilities and Insurance Depreciation on heating system Cleaning Products: Odoban Cleaning Products: Simple Green Draw Total Fixed Costs 232.14 150.00 214 29 29.64 1559 16.78 200.00 38 39 41 Total Variable costs 4.65 $ BSR44 44 45 47 49 50 $ 92 55 56 55 36 Based on 12 dogs per day Assume 22 light-hour work days for Rennefattendant BOARDING Item Variable costs Item Kennel tendant 5.52 Depreciation on Kennels Souls 0.30 Rent Towels 087 loan utilities and insurance Depreciation on heating system Cleaning Products Odoban Cleaning Products: Simple Green Draw Total Variable costs 5 7:35 Total Fixed Costs Fixed Costs 50.00 386 90 250.00 357.14 19:40 2595 29.95 300.00 58 50 5 1,378.92 Variable Fixed mm Minton Type here to search o fi 15 16 GB 170 7 2 3 5. 7 ACC 202 Final Project Workboole O Search General File Home Insert Page Layout Formulas Data Review View Help Calibri 10 AA - 2 Wrap Text LE Paste 8 1 0 - 1 0 - - Merge a Center Clipboard 5 Font Alignment AutoSave OM B2- $ - % -28 Number S24 f 15 17 18 ITEMS TO COMPLETE FOR THIS MILESTONE (Green Tabs): 20 GENERAL 22 Use data from Milestone 1 in your analysis 24 CONTRIBUTION MARGIN ANALYSIS 26 Select a price for each service (grooming day care, boarding 27 Determine the variable cost from the Variable_Faed tab for each service 28 Calculate the contribution margin for each Service based on your sales price and the variable cost for that service 29 30 TREAK EVEN ANALYSIS 32 Determine the fixed cost from the variable Fixed tab for each service Fred variable cost designation is provided Calculate the break even units round up for each service 35 Calculate the break-even units fround up) for susted target profit levels for each service 36 37 18 39 40 41 42 Instructions Milestone 2 Type here to search o CE 17: 12 13 14 is 16 e 17 G ACC 202 Final Project Workbook-1 - File Home insert Page Layout Formulas Data Review View Help Search Calibri 10 - AA BIU AA. General Paste $$ Wrap Text Merge a Center $ - % -93 C For Clipboard 5 Font Alignment Number AutoSave ON 2. 4 4 MILESTONE 2 - Contribution Margin per Unit & Contribution Margin Ratio 5 7 9 10 11 12 13 14 15 10 17 18 19 20 21 22 23 74 25 26 28 INSTRUCTIONS: Select a price from the scenarios below and compute the contribution for each service based on your selected price Variable cont per unit comes from your Variable fled tab completed in Milestone SCENARIO (choose one from each category: Dog Day Care 1 With pricing at 518 per dog per day, you can expect to have 22 dees per day 2. With pricing at $20 per dog per day, you can expect to have 15 does perify 3 With pricing at $25 per dog per day, you can expect to have 10 dogs per day Overnight Boarding 1 with pricing at 525 per dos per day, you can expect to have 12 dogs per day 2 with pricing at 528 per dos per day, you can expect to have 10 dogs per day 3. With pricing at 530 per dot per day, you can expect to have does per day Bank Groom 1 with pricing at $25 per per day, you can expect to have 5 des per day 2 With pricing at 530 per deg per day. You can expect to have 4 dogs per day 3 With pricing at 35 perdor per day, you can expect to have dogs per day DAY CARE BOARDINO GROOMINO Sales Price Unit 5 Contribution Margin Analysis 3 Type here to search E E 12 13 0 15 2 3 5 6 7 8. ACC 202 Final Project Workbock1Ercel File Home Insert Page Layout Formulas Data Review View Help Search Calibri 10 Paste AA == - 33 Wrap Test 0-A-SE Merge Center 8 IV- General $ - %) A Condition Formatting 15 Chipboard AutoSave Font 2= Alignment Number 19 X AB 8 9 10 11 12 13 14 D Select a price from the scenarios below and compute the contribution for each service based on your selected price Variable cost per unit comes from your Variable Pad tab completed in Milestone 1 SCENARIO (choose one from each otegory's Dey Day Care 1 with pricing at $1 per dog per day. You can expect to have 22 dogs per day 2. With pricing at $20 per dog per day, you can expect to have 15 dos per day 3. With pricing at 525 perder per day, you can expect to have 10 dopper day Overnight Boarding 1 with pricing at $25 per dog per day, you can spect to have 12 dps per day 2. With pricing at $28 per dos per day you can expect to have 10 dogs per day 3. With pricing at $30 per dog per day, you can expect to have 7 des per day Bask Groom 1. With pricing at 525 per des per day, you can expect to have 5 dogs per day 2. With pricing at 50 per dober day, you can expect to have des per day 3. With pricing at $35 ger dog per day, you can get to have dos per day 15 17 18 20 21 23 24 25 26 DAY CARE BOARDINO GROOMING 28 Sales Price per unit Variable cost per unit Contribution Man 3 Contribution Margin Analysis Type here to search O SI 12 13 15 3 5 7 ab W E R ACC 202 Final Project Workbook 1. Excel Fle Home Insert Page Layout Formulas Data Review Help O Search Calibri AA - 2 Wap Text General Paste 10 -AA- 4 Merge Center $ -% % -2 Conditional Formu Formatting - Table Clipboard Font Alignment Numbet AutoSave OF 2. N21 X AD 4 MILESTONE 2 - Break-Even Analysis 5 6 7 INSTRUCTIONS Show all steps and calculations to determine the break even Determine the break even for the target profit levels as outlined in the instructions 9 10 11 12 19 Break-even Pined Cons/Contribution Margin 19 DAY CARE BOARDINO GROOMING Sales Price 17 18 19 20 Fixed costs Contribution Margin 5 5 Beek even Units round up 22 23 24 26 22 28 Tor Profit 417.00 5 53.00 5 1.000.00 Break Even Analysis Hey HE Type here to search RE HIP 2 15 lo WO 2 3 7 W E R. T ACC 202 Final Project Workbook-1 - Excel File Home Insert Page Layout Formulas Data Review View O Search X Calibri -10 > 3 Wrap Text General Paste BIWA. A = Merge a Center S. % -28 Condicional Format Cell Formatting Table Styles Clipboard Font Alignment Number AutoSave Op 2 lilli N21 > A# 11 12 13 14 Break even Fixed costs Contribution Margin DAY CARE DOARDING GROOMING 17 15 Sales Price Fred Costs $ Contribution Margin Break-even unit round up 20 21 22 25 24 26 22 28 20 50 Tarpt Profit S 417.00 5R300 $ 1,000.00 Break even Units round up 33 Target Profit 66700 5 909.00 1.500.00 35 30 Break-even units round up Cotton Varber MadeBreak Even Analyti Instru 15 Type here to search o i de A &