ZuoTi.Pro
Question

Query companys projected income statement shows the following results from selling 100,000 units of product. Net sales are $2,000,000 - total expenses are $1,760,000-net income is $240,000. The details of the expenses are as following TOTAL FIXED 420,000.00 340,000.00 $60,000.00 820,000.00 VARIABLE Cost of goods sold Selling expenses Administrative expenses Total Expenses 780,000.00 100,000.00 60,000.00 $940,000.00 1,200,000.00 $440,000.00 $120,000.00 $1,760,000.00 Compute the break-even point in dollars and units for each of the independent alternatives 1. Increase unit selling price by 20% with no change in variable or fixed costs 2. Increase the sales volume by 20% but keep the price per unit the same 3. Change the compensation of salespersons (selling expenses) to 10% commission of sales with no fixed cost THIS PROJECT AND ALL CALCULATIONS MUST BE DONE USING A SPREADSHEET (i.e. Excel)

Just Calculation 2 please and could I see the formulas in excel

business   accounting   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Calculation of Break Even Point: Total Per unit $ 2,000,000 Net Sales Less: Variable Cost Contribution Unit sold Contribution Margin per unit 20 9.40 10.60 940,000 $ 1,060,000 100,000 unit 10.60 per unit (1,060,000/100,000) PV Ratio (Contribution / Net Sales) Fixed Cost 53.00% (1,060,000/2,000,000) $ 820,000 Break Even Point in Units 77,358 unit (Fixed Cost/ Contribution margin per unit) (820,000/10.60) $1,547,170 (820,000 / 53.00% Break Even Point in dollars (Fixed Cost PV ratio1 Increase in selling price by 20% increase in selling price, increases the Net sales by 20% but variable cost in total and fixed cost remain the same Selling unit remain the samee New Selling Price : 20 * 120%-$ 24 per unit $ 2,400,000 940,000 Net Sales (100,000 * 24) Less: Variable Cost Contribution Unit sold Contribution Margin per unit $ 1,460,000 (2,400,000 940,000) 100,000unit 14.60 per unit (1,460,000/100,000) PV Ratio (Contribution / Net Sales) Fixed Cost 60.83% (1,460,000/2,400,000) $ 820,000 Break Even Point in Units 56,164 unit (Fixed Cost/ Contribution margin per unit) (820,000/14.60) $ 1,347,945 820,000 / 63.83%) Break Even Point in dollars (Fixed Cost PV ratio2 Increase the Sales Volume by 20% selling unit will increase by 20% variable cost per unit remain same but changes in total, fixed cost remain same selling unit-100,000* 120% -120,000 unit $ 2,400,000 Net Sales (120,000 *20) Less: Variable Cost (120,000 9.4) Contribution Unit sold Contribution Margin per unit 1,128,000 $ 1,272,000 (2,400,000 1,128,000) 120,000 unit 10.60 per unit (1,272,000/120,000) PV Ratio (Contribution / Net Sales) Fixed Cost 53.00% (1,272,000/2,400,000) $ 820,000 Break Even Point in Units 77,358 unit (Fixed Cost/ Contribution margin per unit) (820,000/10.60) $ 1,547,170 820,000 / 53.00% Break Even Point in dollars (Fixed Cost/ PV ratio3 Change the Compensation of sales person: Fixed Cost Variable Cost Cost of Goods Sold Selling Expenses Administrative expenses Total 420,000 780,000 200,000 (2,000,000 * 10%) 60,000 480,000 60,000 1,040,000 Net Sales Less: Variable Cost Contribution Unit sold Contribution Margin per unit $ 2,000,000 1,040,000 $960,000 100,000 unit 9.60 per unit (960,000/100,000) PV Ratio (Contribution /Net Sales) Fixed Cost 48.00% (960,000/2,000,000) $480,000 Break Even Point in Units 50,000 unit (Fixed Cost/ Contribution margin per unit) (480,000/9.60) $ 1,000,000 480,000 / 48.00%) Break Even Point in dollars (Fixed Cost/ PV ratio)

0 0
Add a comment
Know the answer?
Add Answer to:
Just Calculation 2 please and could I see the formulas in excel Query company's projected income...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • please use excel to answer and show formulas thank you!! Chapter 5: Applying Excel Data Unit...

    please use excel to answer and show formulas thank you!! Chapter 5: Applying Excel Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses 40,000 units $50 per unit $35 per unit $570,000 Enter a formula into each of the cells marked with a ? below Review Problem: CVP Relationships Compute the CM ratio and variable expense ratio Selling price per unit Variable expenses per unit Contribution margin per unit $50 per unit 35 per unit $15...

  • I JUST NEED HELP WITH QUESTION 4!!!!!!!!!!! Morton Company's contribution format income statement for last month...

    I JUST NEED HELP WITH QUESTION 4!!!!!!!!!!! Morton Company's contribution format income statement for last month is given below: Sales (40,000 units X $27 per unit) Variable expenses Contribution margin Fixed expenses Net operating income $ 1,080,000 756,000 324,000 259, 200 $ 64,800 The industry in which Morton Company operates is quite sensitive to cyclical movements in the economy. Thus, profits vary considerably from year to year according to general economic conditions. The company has a large amount of unused...

  • Miller Company's most recent contribution format income statement is shown below: Total 1 $378,000 252,000 126,000...

    Miller Company's most recent contribution format income statement is shown below: Total 1 $378,000 252,000 126,000 Per Unit $9.00 1116.00 Sales (42,000 units) Variable expenses Contribution margin Fixed expenses Net operating income 47,000 79,000 3. The selling price increases by $1.20 per unit, fixed expenses increase by $7,000, and the number of units sold decreases by 6%. Miller Company Contribution Income Statement Total Sales 0 $ 0.00 Variable expenses Contribution margin Fixed expenses Net operating income 01 4. The selling...

  • Chapter 5: Applying Excel: Exercise (Part 2 of 2) 2. Change all of the numbers in...

    Chapter 5: Applying Excel: Exercise (Part 2 of 2) 2. Change all of the numbers in the data area of your worksheet so that it loc BC 1 Chapter 5: Applying Excel 3 4 Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses 60,000 units $ 30 per unit $ 21 per unit $ 486,000 7 Required information (c) What is the degree of operating leverage? (Round your answer to 2 decimal places.) Answer is complete...

  • Miller Company's most recent contribution format income statement is shown below: Sales (32.000 units) Variable expenses...

    Miller Company's most recent contribution format income statement is shown below: Sales (32.000 units) Variable expenses Contribution margin Fixed expenses Net operating income Total $224,000 128,000 96,000 47,000 $ 49,000 Per Unit $7.00 4.00 $3.00 Required: Prepare a new contribution format income statement under each of the following conditions (consider each case independently): (Do not round intermediate calculations. Round your "Per unit" answers to 2 decimal places.) 1. The number of units sold increases by 12%. Per Unit Miller Company...

  • please answer all parts. Thanks! Miller Company's contribution format income statement for the most recent month...

    please answer all parts. Thanks! Miller Company's contribution format income statement for the most recent month is shown below: Score answer Sales (35,000 units) Variable expenses Contribution margin Fixed expenses Net operating income Total $ 324,000 216,000 108,000 47,000 $ 61,000 Per Unit $9.00 6.00 $3.00 Required: (Consider each case independently): 1. What is the revised net operating income if unit sales increase by 16%? 2. What is the revised net operating income if the selling price decreases by $1.40...

  • I See The Light, Inc Schedule of Projected Costs Variable Manufacturing Unit Cost 20x 1 Cost...

    I See The Light, Inc Schedule of Projected Costs Variable Manufacturing Unit Cost 20x 1 Cost Projected Percent Increase 20x2 Cost Rounded to 2 Decimal Places 161 Lamp Kit Labor Variable Overhead 2 5% $16.64 $2.10 $2.10 {4.01) {4.02} {4.03) 2 5% Projected Variable Manufacturing Cost Per Unit $20.84 {4.04} Total Variable Cost Per Unit 20x1 Cost Projected Percent Increase 20x2 Cost Rounded to 2 Decimal Places 3 4% 3.12 Variable Selling Variable Administrative Projected Variable Manufacturing Unit Cost 2...

  • Miller Company's contribution format income statement for the most recent month is shown below: Total $...

    Miller Company's contribution format income statement for the most recent month is shown below: Total $ 320,000 200,000 Sales (40,000 units) Variable expenses Contribution margin Fixed expenses Net operating income Per Unit $8.00 5.00 $3.00 120,000 49,000 $ 71,000 Required: (Consider each case independently): 1. What is the revised net operating income if unit sales increase by 15%? 2. What is the revised net operating income if the selling price decreases by $1.40 per unit and the number of units...

  • Consider Brighton’s results for Year 0 (see excel click here). The sales forecast for Year 1...

    Consider Brighton’s results for Year 0 (see excel click here). The sales forecast for Year 1 is given in excel. In addition, Brighton plans on paying dividends of $20,000 in Year 1, they have 40,000 shares of common stock outstanding and for simplification, assume their tax expense is 35% of EBT. For Year 1, forecast Brighton’s: Basic Earnings per common share (rounded to the nearest cent). Retained earnings (rounded to the nearest dollar). Brighton Company Year 0 Year 1 Cash...

  • Problem 2-20 Points: The CGC Computer Products most recent contribution margin income statement is shown on...

    Problem 2-20 Points: The CGC Computer Products most recent contribution margin income statement is shown on the worksheet. In each of the following scenarios, calculate the values indicated. (CALCULATE ALL CHANGES FROM THE BEGINNING SCENARO OF NUMBERS-hint: it may be easier to copy the base income statement and paste to all other scenarios) A. The breakeven point in dollars and units. B. The sales volume increases by 30% and the price decreases by $0.50 per unit. c. The selling price...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT