Question

Presidio, Inc., produces one model of mountain bike. Partial information for the company follows: CostData Number of Bikes Produced and so 625 800 1,050 Total costs Variable costs $125,000 $? $ Fixed costs per year ? Total costs Cost per unit Variable cost per unit Fixed cost per unit Total cost per unit $543.75 Required: 1. Complete Presidio's cost data table. 2. Calculate Presidio's contribution margin ratio and its total contribution margin at each sales level indicated in the cost data table assuming the company sells each bike for $650. 3. Calculate net operating income at each of the sales levels assuming a sales price of $650,

please include excel formulas

Answer 1

625 800) 1,050 Bikes produced and sold Total costs Variable costs Fixed costs per year Total costs Cost per unit Variable cost per unit Fixed cost per unit Total cost per unit $125,000 $275,000 $400,000 $160,000 $275,000 $435,000 $210,000 $275,000 $485,000 $200.00 $440.00 $640.000 $200.00 $343.75 $543.75 $200.00 $261.90 $461.90 per unit Working notes: Variable cost per unit Total variable cost Total units $125,000 625 $200 Variable cost per unit remains same Calculation of fixed cost Total cost per unit Output Total cost Less: Variable cost Fixed cost $543.75 A 800 B $435,000 AXB $160,000 (800 units X $200) $275,000 Fixed cost remains same at all levels of output. Cost per unit will vary depending on production Total Contribution Margin Contribution Margin Ratio 625 $281,250 69.23% 800 $360,000 69.23% 1050 $472,500 69.23% Working notes: Sales units Selling price Variable cost 625 $650 $200 800 $650 $200 1050 $650 $200 Total Sales Variable Cost Total Contribution Margin Contribution margin ratio (Contribution margin/Sales) $406,250 $125,000 $281,250 69.23% $520,000 $160,000 $360,000 69.23% $682,500 (Sales units X selling price) $210,000 (Sales units X variable cost) $472,500 Sales - Variable cost 69.23% 3 Net 1050 $682,500 rating Income 625 800 $406,250 $520,000 $125,000 $160,000 $281,250 $360,000 $275,000 $275,000 $6,250 $85,000 Sales Variable Cost Contribution Fixed Cost Net Operating Income $210,000 $472,500 $275,000 $197,500 Answer

D E F G H 7 1 8 625 800 1050 9 10 11 Bikes produced and sold Total costs Variable costs Fixed costs per year Total costs Cost per unit Variable cost per unit Fixed cost per unit Total cost per unit 125000 =H8*H20 =18*H20 =G30 =G11 =H11 =SUM(G10:11) =SUM(H10:H11) -SUM(110:111) 12 13 14 15 =G10/G$8 =H10/H$8 =110/1$8 =G11/G$8 =H11/H$8 =111/1$8 =SUM(G14:615) -SUM(H14:H15) -SUM(114:115) 16 17 18 19 per unit Working notes: Variable cost per unit Total variable cost Total units 20 =G10 21 =G8 =G20/G21 22 23 Variable cost per unit rem 24 25 26 A 27 B Calculation of fixed cost Total cost per unit Output Total cost Less: Variable cost Fixed cost 543.75 800 =G26*G27 =H10 28 AXB (800 units X $200) 29 30 1=G28-G29

D E F G Н. 33 2 =H8 =18 34 35 =G8 Total Contribution Margin =G45 Contribution Margin Ratio =G35/643 =H45 =H35/H43 =145 =135/143 36 37 38 39 EG34 =H34 =134 Working notes: Sales units Selling price Variable cost =H40 40 41 650 =G14 =G40 =H14 =114 42 43 44 Total Sales =G$39*G40 Variable Cost =G$39*G41 Total Contribution Margin =G43-444 Contribution margin ratio=G45/G43 (Contribution margin /Sale =H$39*H40 =H$39*H41 =H43-H44 =H45/H43 =I$39 140 =1$39*141 =143-144 =145/143 (Sales units X selling (Sales units X variab Sales - Variable cost 45 46 47 48 49 50 3 51 52 53 54 Net Operating Income =G34 =H34 =G43 =H43 =G44 =H44 =G52-G53 =H52-H53 =G11 =H11 =G54-655 =H54-H55 =134 =143 =144 Sales Variable Cost Contribution Fixed Cost Net Operating Income 55 56 57 =152-153 =111 =154-155 Answer