Need excel answers for the question attached using formula ( explaining clearly in excel )

LO 3-1 Cost-Volume-Profit Analysis LO 3-1 Use cost-volume-profit (CVP) analysis to analyze decisions. What is CVP? CVP analysis explores the relationship between revenue, cost, and volume and their effect on profits. 3-4 LO 3-1 3-5 Profit Equation The Income Statement Total revenues - Total costs = Operating profit The Income Statement written horizontally Operating profit = Total revenues - Total costs Profit = TR - TC LO 3-1 Profit Equation Total revenue (TR) Average selling price per unit (P) Units of output produced and sold (X) TR = PX Total cost (TC) [Variable cost per unit (V) Units of output (X)] + Fixed costs (F) TC = VX + F 3-6 LO 3-1 Profit Equation 3-7 Profit = Total revenue - Total costs = TR - TC TC = VX + F Therefore, Profit = PX - (VX + F) Profit = (Price - Variable costs) Units of output - Fixed costs = X(P - V) - F LO 3-1 Contribution Margin This is the difference between price and variable cost. It is what is leftover to cover fixed costs and then add to operating profit. Contribution margin = Price per unit - Variable cost per unit P-V 3-8 LO 3-1 CVP Example Contribution margin = $2,880 12,000 = $0.24 3-9 LO 3-1 Break-Even Volume in Units This is the volume level at which profits equal zero. Profit 0 = X(P - V) - F If profit = 0, then X = F (P - V) Fixed costs Break-even volume (in units) = Unit contribution margin = $1,500 $0.24 = 6,250 prints 3-10 LO 3-1 Break-Even Volume in Sales Dollars Contribution margin percentage (contribution margin ratio) is the contribution margin as a percentage of sales revenue. Contribution Margin Percentage $0.24 $0.60 = 0.40 (or 40%) Break-even in Sales Dollars $1,500 0.40 = $3,750 3-11 LO 3-1 Target Volume Assume that management wants to have a profit of $1,800. How many prints must be sold? What is the target dollar sales? Target Volume in Units ($1,500 + $1,800) $0.24 = 13,750 Target Volume in Sales Dollars ($1,500 + $1,800) 0.40 = $8,250 3-12 LO 3-1 CVP Summary: Break-Even Break-even volume = (units) Fixed costs Unit contribution margin Break-even volume Fixed costs = (sales dollars) Contribution margin ratio 3-13 LO 3-1 CVP Summary: Target Volume Target volume = (units) Fixed costs + Target profit Unit contribution margin Target volume = (sales dollars) Fixed costs + Target profit Contribution margin ratio 3-14 3-25. Basic Decision Analysis Using CVP (LO 3-1 ) Anu's Amusement Center has collected the following data for operations for the year: Total revenues Total fixed costs Total variable costs Total tickets sold $2,400,000 $ 656,250 $1,350,000 75,000 Required a. What is the average selling price for a ticket? b. What is the average variable cost per ticket? c. What is the average contribution margin per ticket? d. What is the break-even point? e. Anu has decided that unless the operation can earn at least $131,250 in operating profits, she will close it down. What number of tickets must be sold for Anu's Amusements to make a $131,250 operating profit for the year on ticket sales? 3-28. Basic Decision Analysis Using CVP (LO 3-1 ) Derby Phones is considering the introduction of a new model of headphones with the following price and cost characteristics: Sales price $ 270 per unit Variable costs Fixed costs 120 per unit 300,000 per month Required a. What number must Derby sell per month to break even? b. What number must Derby sell to make an operating profit of $180,000 for the month? 3-39. CVP with Income Taxes Hunter & Sons sells a single model of meat smoker for use in the home. The smokers have the following price and cost characteristics: Sales price Variable costs Fixed costs $ 550 per smoker 330 per smoker 143,000 per month Hunter & Sons is subject to an income tax rate of 40 percent. Required a. How many smokers must Hunter & Sons sell every month to break even? b. How many smokers must Hunter & Sons sell to earn a monthly operating profit of $39,600 after taxes? 3-40. CVP with Income Taxes (LO 3-4) Hammerhead Charters runs fishing trips out of the local port. Hammerhead charges $50 per trip for a half-day trip. Variable costs for Hammerhead total $20 per trip and the fixed costs are $6,000 per month. Hammerhead is subject to an income tax rate of 25 percent. Required a. How many trips must Hammerhead sell to break even? b. How many trips must Hammerhead sell to earn a monthly operating profit of $9,000 after taxes? 3-51. Extensions of the CVP ModelTaxes (LO 3-4) Odd Wallow Drinks is considering adding a new line of fruit juices to its merchandise products. This line of juices has the following prices and costs: Selling price per case (24 bottles) of juice Variable cost per case (24 bottles) of juice $ $ Fixed costs per year associated with this product Income tax rate $12,168,000 40% Required 75 36 a. Compute Odd Wallow Drinks' break-even point in units per year. b. How many cases must Odd Wallow Drinks sell to earn $1,872,000 per year after taxes on the juice? 3-52. Extensions of the CVP ModelTaxes (LO 3-4) Frightproof Commuter Airlines is considering adding a new flight to its current schedule from Metro to Hicksville. This route has the following prices and costs: Selling price per passenger per flight Variable cost per passenger per flight Fixed cost per flight Income tax rate $ 240 $ 60 $8,640 25% Required a) b) c) Compute Frightproof's break-even point in number of passengers per flight. How many passengers per flight must Frightproof have to earn $3,510 per flight after taxes? Each aircraft has the capacity for 70 passengers per flight. In view of this capacity limitation, can Frightproof carry enough passengers to break even? Can the company carry enough passengers to earn $3,510 per flight after taxes? 3-53. Extensions of the CVP ModelTaxes (LO 3-4) Central Company manufactures a sensor used in various electronic devices. The product's price and cost characteristics are: Selling price per unit Variable cost per unit Fixed cost per year Required $ 13 4 540,000 The company must sell 10,000 units annually in order to earn $187,200 in profits after taxes. What is Central Company's tax rate? 3-28. Basic Decision Analysis Using CVP (LO 3-1) Derby Phones is considering the introduction of a new model of headphones with the following price and cost characteristics: Sales price $ 270 per unit Variable costs 120 per unit Fixed costs 300,000 per month Required a. What number must Derby sell per month to break even? b. What number must Derby sell to make an operating profit of $180,000 for the month? A) Sale Price - VC 270-120=150 B) Profit O = X(P-V)-F 180,000=X9150)-300,000 180,000+300,000=X(150) 480,000=150X X= 480,000 = 3200 Units 150