Any help with these excell formulas would be great Your Name Member Name Member

Question

Any help with these excell formulas would be great

Your Name Member Name Member Name Member Name Requirement #6-What is the Margin of Safety, if expected sales is $2,000,000? NIKE has been selling the new sneakers for six (6) months. The Shoe Dept. Manager wants to know what the total cost would be for 60,000 sneakers using the following information: Margin of Safety Requirement #7-What is the Margin of Safety Ratic Margin of Safe ty Ratio NIKE is deciding whether to produce a new sneaker designed for long-distance runners. In order to make a final decision, NIKE needs you to create a CVP income statement using the following information: Units Month January February March Cost 35,000 $1,650,000 38,000 $1,500,000 72,000 $2,760,000 65,000 $2,550,000 83,000 $3,090,000 79,000 $3,200,000 Show as a percentage, round to 0 decimals) ril Sales Revenue Cost of Goods Sold Gross Profit Selling and Admin Expenses Net Operating Income 34,350,000 May 19,038,000 15,312,000 10,563,000 4,749,000 June Requirement #4-Use the High-Low Method to Calculate the following: Variable/Unit COGS Fixed Cost Percentage S& A Fixed Cost Percentage 55% 60% Round to 2 decimals) Fixed Cost Requirement #1-Complete the CVP Income Statement. Total Mixed Cost for 60,000 sneakers - Sales Revenue Variable Cost Contribution Margin Fixed Cost Net Operating Income $34,350,000.00 $12,792,300.00 2,557,700.00 $ 16,808,700.00 $4,749,000.00 Requirement #5-The selling price is $70 per pair of sneakers. Using the information from Req. #4 above and the $70 sales price: First, calculate how many units must the Shoe Dept. must sell to break-even. Then calculate how much revenue (in sales dollars) is needed to earn a profit of $1,500,000. Requirement #2. Calculate the Contribution Margin Ratio. (Show as a percentage, round to 0 decimals) Contribution Margin Ratio Break-even units- 63% Sales dollars required to earn $1,500,000 profit- Requirement #3-Calculate the Degree of Operation Leverage Round to 2 decimals) Degree of Operating Leverage Round your final answer to 2 decimals) 4.54 (If you use the CM ratio approach, please round your ratio to 2 decimal places.)

Explanation / Answer

Requirement 4 :

Variable cost per unit = Change in cost/change in units

= (3090000-1650000)/(83000-35000)

variable cost per unit = 30 per unit

Fixed cost = Total cost-variable cost

= 3090000-(83000*30)

Fixed cost = 600000

Total mixed cost on 60000 sneakers = (60000*30+600000) = 2400000

Requirment 5 :

Break even units = Fixed cost/contribution margin per unit

= 600000/(70-30)

Break even units = 15000 units

Sales dollars for 1500000 profit = (Fixed cost+desired profit)/Contribution margin per unit*Sale price per unit

= (600000+1500000)/40*70

Sales dollars for 1500000 profit = 3675000

Requirement 6:

Margin of safety = Expected sales-break even sales

= 2000000-(15000*70)

Margin of safety = 950000

Margin of safety ratio = Margin of safety sales*100/actual sales

= 950000*100/2000000

Margin of safety ratio = 47.5%

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.