section E----start from E3
ULE 1 Section E C e the break even proti setore tax (it we hadn't rounded Break Even Units this should be 0) The following are to be completed in the Profit Volume Forecast Worksheet In this sheet we want to be able to forecast what our profits will look like with different sales volumes In C4 create a calculation to add the Increment (the amount our forecast is increasing by) in B3 to the volume in B4. (Note we want the ability to change the values in B3 and 3 so do NOT just add 25, use a relative reference or a range name). Drag the formula across to G4 In row 5 calculate the total sales revenue for each of the different Sales Volumes Complete the variable costs table for the different sales volumes (88613) In row 16 calculate the Contribution Margin for each sales volume. In row 17 calculate Contribution Margin less Fixed Costs for each sales volume. 00000000000 9 In row 20 calculate the state tax incurred for each sales volume, if the profit was zero or below, the calculation should return 0. (Do NOT just type 0 as the volumes can change.) In row 21 calculate the federal tax incurred for each sales volume, if the profit was zero or below the calculation should return O. (DO NOT just type o as the volumes can change.) In row 24 calculate the profit after factoring in the income tax. in row 26 work out the profit for each sales volume as a percentage of sales. Format as a percentage with 2 decimal places do not apply rounding formulas). Complete the Break Even Analysis Line Chart Data in 329 to 632 for each of the Unit volumes. (Note Total Costs includes Variable costs, Fixed Costs and Income Tax) Using the data in B29 to G23, create a 2D Line Chart that plots the Total Foxed Costs, Total Costs & Total Sales. Add the Volume Units as HorizontalAxis Labels and add the Chart Title: Break-Even Analysis (mark given only if this exact title is used). Use chart tools (not drag and dropl to position the legend at the top of the chart. N.B. Only include one that ensure it is on the Forecast Sheet and that it is a 2D Line Chart with no line markers (not a Scatter chart with smooth lines). 1 Done Question Section F1 instruction The following are to be completed in the inventory Worksheet wa stock item falls below the minimum stock level in M e ct to change and is not on backorder in column N then we need to reorderit create a calculation that will retum the item needs reordering and leave the bank it does not The quantity to reorder is determined by the price of the item as shown in the table in LG M10, e anything that costs between 55 and $49.99 we reorder 25 (these values can change in 14 calculate the reorder quantity, return reorder not required TOTAL MARKS - Profit Volume Forecast N 25 3 Unit Volume Increments 4 Sales Volume in Units 5 Total Sales Value 25 150 $ 140,000.00 $ 280,000.00 $ 420,000.00 $ 560,000.00 $ 125 700,000.00 S 840,000.00 7 Variable Costs 8 Direct Labor 9 Direct Material 10 Factory Overhead 11 Other Vary-with-Unit Costs 12 Sales Commissions 13 Other Vary-with-Revenue Costs 14 Total Variable Costs 16 Contribution Margin 17 Contribution Margin - Fixed Costs 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax 24 $ Profit 26 % Profit of Sales Value 28 Break-Even Analysis Line Chart Data 29 Volumes in Units 30 Fixed Costs 31 Total Costs 32 Total Sales 33 Information Foxed Costs HR Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory BC D E F G H I Fixed Costs and Pricing for the Year w X-800 ZF300 5 Fixed Cost Items 6 Rent 7 Utilities 8 Admin Costs 9 Insurance 10 11 Unit Sales Price 12 Total Units Sold 58,000.00 14,000.00 25,400.00 12,300.00 $ $ $ $ $ $ 58,000.00 13,900.00 26,300.00 12,500.00 $ 5,600.00 350 $ 5,700.00 370 Information Fixed Costs HR Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory fr 57300 Labour Costs for the year X Super Days without Pay Product Line X-800 X-800 x 800 27300 ZF300 ZF300 $ 1 O Employee Number S12716 S12717 S12720 S12722 0 S12723 1 S12724 2 S12725 3 S12726 4 S12727 5 S12728 6 512729 7 512730 8 512733 Annual 86,600.00 74,800.00 70,100.00 66,000.00 64,800.00 57,300.00 77,200.00 63,300.00 89,800.00 76,300.00 55,900.00 72,200.00 62,100.00 Bonus Rate Bonus Amount 6.0% S ale 5.196.00 9.0% $ 6,732.00 6.0% S 4,206.00 6.0% $ 3,960.00 4.5% $ 2,916.00 9.00 $ 5.157.00 6.0NS 4,632.00 1.5% S 949.50 7.5 $ 6,735.00 3.0% S 2,289.00 3.0% S 1,677.00 3.0% 2,166.00 6.0 S 3,726.00 $ $ S $ $ S $ S S S $ $ $ Daily Rate 237.26 S 204.93 $ 192.05 S 180.82 S 177.53 $ 156.99 $ 211.51 $ 173.42 S 246.03S 209.04 153.15 S 197.81 $ 170.14 $ Super 8.227.00 7,106.00 6,659.50 6 ,270.00 6,156.00 5,443.50 7,334.00 6,013,50 8,531.00 7,248.50 5,310.50 6,850.00 5.899.50 S $ S $ S $ S S $ $ $ $ $ Total Pac 99. 88,6 80.9 76,2 73,6 67,9 89,16 70,20 105,06 81,74 62,88 81.22 71,72 10 X-800 ZF300 ZF300 X-800 X-800 X-ROO 0 0 Total S Highest Average S 916,400.00 89,800.00 20.492 31 PENNIN Information Fred Costs HR Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory me Review View Help sert .: B Page Layout X C Formulas Data -SUMI4-189 D A Bill of Materials Wey Part CodeQuantity - Category GR1751 Accessories and tuning CO2157 1 Contact Point Total Weight for Groupset Accessories and tuning Contact Points 196.0 256.6 1566 200 21163 774 Unit $71.10 $123 $17.71 $165.00 12 $136.74 $12198 Total $146. 20 $25 26 $17.71 $165.00 SO $136.74 M70 12017 Wheels 3 Product 4 300 5 X-100 6 300 7 X 800 4 x 800 9 300 10 X 100 11 X-300 12 300 13 300 16 X 300 15 X-800 16 F300 17 100 10 100 19 300 2010 21 300 Product Decoration CNC brakes 2004 Brakes Nashbar Alped Huet Pedas Rocks Me Saddle JBL Fork Generic Foam Tape Tape Untega Cranks XR-270/tex Wheel N a Carbon Generic Foam Tape Tape IBL Plus Nash RD Titanium Skewers 8455 2 Contact Points 1 Group 2 Wheels 1 Contact Points 2 Contact Points 1 Frameet 1 Group 2 Wheels CO1460 2009 WH1100 CO1000 C0149 FR20 1720 WW 1914 GRI FR1215 GR 1172 $0.12 $10.40 $10.00 $03 45 0.00 0.00 5450 $165.00 545.50 $165.00 100 1 Group 1 Frameset 1 Group 2 Contact Points RD IRL Fork UFO Customer ISL Top Cap SOS SO Screw SS. 51 $10.6 $1.0 23 X 60 24 X 300 FROHO 1215 WH237 COM04 W21250 WH1573 CO109 FR1934 R1323 2 Contact Points 2 Who Veloplusim tape Custom Play CNC VISU Whe forte Light Tubes Nashbar Carbon Hrad Nashbar Carbon Frame 103.4 2416 26 X RO 27 1300 $144.00 $4.35 $64.08 50.00 S60 SR.70 46 1 Contact Points 1290 $164.80 29 7300 30 X 800 31 X-300 32 300 33 0300 FR123 1836 Nawar Carbon Frame 1298.0 Ultegra Cassette 2120 CNC V16 Super Slim Wheels BOM CVP Anal Profil Volume Forecast $364 $46,55 $15.00 inventory SM.BD $46.55 70.00 Information Faced costs HR Labour costs . f =H6*E13 Cost Volume Profit Analysis Product Name Unit Sales Price Total units sold Total Flored Costs X-800 5,600.00 350.00 109,700.00 Variable Costs per Unit Vary with Unit Costs Direct Labor Direct Material Factory Overhead Other Vary with Unit Costs Total Vary with Unit Costs Break Even Analysis Contribution Margin Per Unit Break Even Volume in Units Break Even Sales Value 1.947.13 5634 315,500.89 1,540.27 1.548 60 120.00 80.00 RR 87 77.18 Break Even Variable costs Director Direct Material Factory Overhead Other Vary with Unit Costs Sales Commissions Other Vary with Revenue Costs Total Variable Costs Vary with Revenue Costs Sales Commissions Other Vary with Revenue Costs Total Vary with Revenue Costs 5.0 87,247.25 6,760.73 4,507.16 15.775.04 4,732.51 205,00,89 6.5% Income Tax State Tax Federal Tax 109,700.00 Contribution Margin Profit Before Tax information Fired Costs HR Labour Costs BOM CVP Analysis Profil Volume Forecast Inventory VW Hep Table Design Inventory Category Prica/Unit Weight) Supplier Nashbar Bikes Plus Reorder ty $16500 Part Code FR1323 FR1235 FR2360 FR1934 FR10BO FR1081 WH1100 W112373 Product Description Nash Carbon Frame JR Fork JAL Plug Honchet JSL Top Cap & Screw Mr Ride Seat Clamp XR-270/Box Wheel Veloplags Rompe Price Bucked $13.44 Cycle Warehouse Cycle Warehouse Cycle Warehouse LBS Cycle Warehouse Performance Cycle W house Cycle W house $18.451 54.35 $17.71 Contact Point Contact Points 04 Cycle Warehouse Nastbar Cycle Warehouse Cycie Ware Contact Point 3 WH2056 Forte LCR Tyres W111573 Forte Light Tubes 5 C02357 RockBros CrMo Saddie 6 C01649 KCNC Scandium Seatpost 7 C02103 Spacers 8 C00202 Uno 7050 Stem 9 CO100 Naslibar Carbon 0 C0149 Generic Foam Tape Tape 1 C02904 Customer Plus CR9 Ugra Lavors 23 R1322 Ugra RD 24 OR1172 Ultegra FD 25 GR1836 Utegra Cassette 26 CR1215 Dura Ace Chan 271751 CNC en 2009 Brake 28 OR200 gra Cranes 29 C hagra 30 GR Ung Cat 31 Adio F MS AC1945 Aloy CR Bos 33 AC2733 Tanum tomos nomo Groups Backcountry Go Navbar Groups Na 318 Croust Groups Groupe Arco Acce VW d uning Cyck Warehe HR Labour costs BOM CVP Analysis ULE 1 Section E C e the break even proti setore tax (it we hadn't rounded Break Even Units this should be 0) The following are to be completed in the Profit Volume Forecast Worksheet In this sheet we want to be able to forecast what our profits will look like with different sales volumes In C4 create a calculation to add the Increment (the amount our forecast is increasing by) in B3 to the volume in B4. (Note we want the ability to change the values in B3 and 3 so do NOT just add 25, use a relative reference or a range name). Drag the formula across to G4 In row 5 calculate the total sales revenue for each of the different Sales Volumes Complete the variable costs table for the different sales volumes (88613) In row 16 calculate the Contribution Margin for each sales volume. In row 17 calculate Contribution Margin less Fixed Costs for each sales volume. 00000000000 9 In row 20 calculate the state tax incurred for each sales volume, if the profit was zero or below, the calculation should return 0. (Do NOT just type 0 as the volumes can change.) In row 21 calculate the federal tax incurred for each sales volume, if the profit was zero or below the calculation should return O. (DO NOT just type o as the volumes can change.) In row 24 calculate the profit after factoring in the income tax. in row 26 work out the profit for each sales volume as a percentage of sales. Format as a percentage with 2 decimal places do not apply rounding formulas). Complete the Break Even Analysis Line Chart Data in 329 to 632 for each of the Unit volumes. (Note Total Costs includes Variable costs, Fixed Costs and Income Tax) Using the data in B29 to G23, create a 2D Line Chart that plots the Total Foxed Costs, Total Costs & Total Sales. Add the Volume Units as HorizontalAxis Labels and add the Chart Title: Break-Even Analysis (mark given only if this exact title is used). Use chart tools (not drag and dropl to position the legend at the top of the chart. N.B. Only include one that ensure it is on the Forecast Sheet and that it is a 2D Line Chart with no line markers (not a Scatter chart with smooth lines). 1 Done Question Section F1 instruction The following are to be completed in the inventory Worksheet wa stock item falls below the minimum stock level in M e ct to change and is not on backorder in column N then we need to reorderit create a calculation that will retum the item needs reordering and leave the bank it does not The quantity to reorder is determined by the price of the item as shown in the table in LG M10, e anything that costs between 55 and $49.99 we reorder 25 (these values can change in 14 calculate the reorder quantity, return reorder not required TOTAL MARKS - Profit Volume Forecast N 25 3 Unit Volume Increments 4 Sales Volume in Units 5 Total Sales Value 25 150 $ 140,000.00 $ 280,000.00 $ 420,000.00 $ 560,000.00 $ 125 700,000.00 S 840,000.00 7 Variable Costs 8 Direct Labor 9 Direct Material 10 Factory Overhead 11 Other Vary-with-Unit Costs 12 Sales Commissions 13 Other Vary-with-Revenue Costs 14 Total Variable Costs 16 Contribution Margin 17 Contribution Margin - Fixed Costs 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax 24 $ Profit 26 % Profit of Sales Value 28 Break-Even Analysis Line Chart Data 29 Volumes in Units 30 Fixed Costs 31 Total Costs 32 Total Sales 33 Information Foxed Costs HR Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory BC D E F G H I Fixed Costs and Pricing for the Year w X-800 ZF300 5 Fixed Cost Items 6 Rent 7 Utilities 8 Admin Costs 9 Insurance 10 11 Unit Sales Price 12 Total Units Sold 58,000.00 14,000.00 25,400.00 12,300.00 $ $ $ $ $ $ 58,000.00 13,900.00 26,300.00 12,500.00 $ 5,600.00 350 $ 5,700.00 370 Information Fixed Costs HR Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory fr 57300 Labour Costs for the year X Super Days without Pay Product Line X-800 X-800 x 800 27300 ZF300 ZF300 $ 1 O Employee Number S12716 S12717 S12720 S12722 0 S12723 1 S12724 2 S12725 3 S12726 4 S12727 5 S12728 6 512729 7 512730 8 512733 Annual 86,600.00 74,800.00 70,100.00 66,000.00 64,800.00 57,300.00 77,200.00 63,300.00 89,800.00 76,300.00 55,900.00 72,200.00 62,100.00 Bonus Rate Bonus Amount 6.0% S ale 5.196.00 9.0% $ 6,732.00 6.0% S 4,206.00 6.0% $ 3,960.00 4.5% $ 2,916.00 9.00 $ 5.157.00 6.0NS 4,632.00 1.5% S 949.50 7.5 $ 6,735.00 3.0% S 2,289.00 3.0% S 1,677.00 3.0% 2,166.00 6.0 S 3,726.00 $ $ S $ $ S $ S S S $ $ $ Daily Rate 237.26 S 204.93 $ 192.05 S 180.82 S 177.53 $ 156.99 $ 211.51 $ 173.42 S 246.03S 209.04 153.15 S 197.81 $ 170.14 $ Super 8.227.00 7,106.00 6,659.50 6 ,270.00 6,156.00 5,443.50 7,334.00 6,013,50 8,531.00 7,248.50 5,310.50 6,850.00 5.899.50 S $ S $ S $ S S $ $ $ $ $ Total Pac 99. 88,6 80.9 76,2 73,6 67,9 89,16 70,20 105,06 81,74 62,88 81.22 71,72 10 X-800 ZF300 ZF300 X-800 X-800 X-ROO 0 0 Total S Highest Average S 916,400.00 89,800.00 20.492 31 PENNIN Information Fred Costs HR Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory me Review View Help sert .: B Page Layout X C Formulas Data -SUMI4-189 D A Bill of Materials Wey Part CodeQuantity - Category GR1751 Accessories and tuning CO2157 1 Contact Point Total Weight for Groupset Accessories and tuning Contact Points 196.0 256.6 1566 200 21163 774 Unit $71.10 $123 $17.71 $165.00 12 $136.74 $12198 Total $146. 20 $25 26 $17.71 $165.00 SO $136.74 M70 12017 Wheels 3 Product 4 300 5 X-100 6 300 7 X 800 4 x 800 9 300 10 X 100 11 X-300 12 300 13 300 16 X 300 15 X-800 16 F300 17 100 10 100 19 300 2010 21 300 Product Decoration CNC brakes 2004 Brakes Nashbar Alped Huet Pedas Rocks Me Saddle JBL Fork Generic Foam Tape Tape Untega Cranks XR-270/tex Wheel N a Carbon Generic Foam Tape Tape IBL Plus Nash RD Titanium Skewers 8455 2 Contact Points 1 Group 2 Wheels 1 Contact Points 2 Contact Points 1 Frameet 1 Group 2 Wheels CO1460 2009 WH1100 CO1000 C0149 FR20 1720 WW 1914 GRI FR1215 GR 1172 $0.12 $10.40 $10.00 $03 45 0.00 0.00 5450 $165.00 545.50 $165.00 100 1 Group 1 Frameset 1 Group 2 Contact Points RD IRL Fork UFO Customer ISL Top Cap SOS SO Screw SS. 51 $10.6 $1.0 23 X 60 24 X 300 FROHO 1215 WH237 COM04 W21250 WH1573 CO109 FR1934 R1323 2 Contact Points 2 Who Veloplusim tape Custom Play CNC VISU Whe forte Light Tubes Nashbar Carbon Hrad Nashbar Carbon Frame 103.4 2416 26 X RO 27 1300 $144.00 $4.35 $64.08 50.00 S60 SR.70 46 1 Contact Points 1290 $164.80 29 7300 30 X 800 31 X-300 32 300 33 0300 FR123 1836 Nawar Carbon Frame 1298.0 Ultegra Cassette 2120 CNC V16 Super Slim Wheels BOM CVP Anal Profil Volume Forecast $364 $46,55 $15.00 inventory SM.BD $46.55 70.00 Information Faced costs HR Labour costs . f =H6*E13 Cost Volume Profit Analysis Product Name Unit Sales Price Total units sold Total Flored Costs X-800 5,600.00 350.00 109,700.00 Variable Costs per Unit Vary with Unit Costs Direct Labor Direct Material Factory Overhead Other Vary with Unit Costs Total Vary with Unit Costs Break Even Analysis Contribution Margin Per Unit Break Even Volume in Units Break Even Sales Value 1.947.13 5634 315,500.89 1,540.27 1.548 60 120.00 80.00 RR 87 77.18 Break Even Variable costs Director Direct Material Factory Overhead Other Vary with Unit Costs Sales Commissions Other Vary with Revenue Costs Total Variable Costs Vary with Revenue Costs Sales Commissions Other Vary with Revenue Costs Total Vary with Revenue Costs 5.0 87,247.25 6,760.73 4,507.16 15.775.04 4,732.51 205,00,89 6.5% Income Tax State Tax Federal Tax 109,700.00 Contribution Margin Profit Before Tax information Fired Costs HR Labour Costs BOM CVP Analysis Profil Volume Forecast Inventory VW Hep Table Design Inventory Category Prica/Unit Weight) Supplier Nashbar Bikes Plus Reorder ty $16500 Part Code FR1323 FR1235 FR2360 FR1934 FR10BO FR1081 WH1100 W112373 Product Description Nash Carbon Frame JR Fork JAL Plug Honchet JSL Top Cap & Screw Mr Ride Seat Clamp XR-270/Box Wheel Veloplags Rompe Price Bucked $13.44 Cycle Warehouse Cycle Warehouse Cycle Warehouse LBS Cycle Warehouse Performance Cycle W house Cycle W house $18.451 54.35 $17.71 Contact Point Contact Points 04 Cycle Warehouse Nastbar Cycle Warehouse Cycie Ware Contact Point 3 WH2056 Forte LCR Tyres W111573 Forte Light Tubes 5 C02357 RockBros CrMo Saddie 6 C01649 KCNC Scandium Seatpost 7 C02103 Spacers 8 C00202 Uno 7050 Stem 9 CO100 Naslibar Carbon 0 C0149 Generic Foam Tape Tape 1 C02904 Customer Plus CR9 Ugra Lavors 23 R1322 Ugra RD 24 OR1172 Ultegra FD 25 GR1836 Utegra Cassette 26 CR1215 Dura Ace Chan 271751 CNC en 2009 Brake 28 OR200 gra Cranes 29 C hagra 30 GR Ung Cat 31 Adio F MS AC1945 Aloy CR Bos 33 AC2733 Tanum tomos nomo Groups Backcountry Go Navbar Groups Na 318 Croust Groups Groupe Arco Acce VW d uning Cyck Warehe HR Labour costs BOM CVP Analysis
