Question

In: Accounting

Sara sells two types of cakes from home. The total fixed cost for every month is...

Sara sells two types of cakes from home. The total fixed cost for every month is budgeted at 200 BD. The labor cost per unit is equal to 2 BD. Sara sells 150 cake every month. The selling prices is 15 BD. The ingredient cost is 4 BD per cake.
Sara wants to know what price she must charge to generate enough revenue to cover her costs. With Break-Even Analysis, Sara can compare different pricing options and calculate how many units sold will lead to profitability. She needs to calculate the contribution margin which equal to selling price minus the variable costs. Contribution margin shows the revenue earned per unit, after deducting variable costs and needs to be enough to cover the company's fixed costs. Sara needs to calculate the following:

1) Break-Even Price, to determine the price needs to be set to generate enough revenue to cover her costs. Break-Even Price equal to 1 / ((1 - Total Variable Costs Percent per Unit) * (Total Fixed Costs per Unit)). Where Variable Costs Percent per Unit = Total Variable Costs / (Total Variable + Total Fixed Costs). Then determine how changes in unit sold and cost per unit affect Break-Even Price, unit sold between 100 and 200 in 10 increments and Cost per unit between 3.5 and 6.5 in 0.5 increments.

2) Break-Even Units Sold, to determine the number of units that need to be sold to achieve the break-even point. To calculate the Break-Even Units Sold, we divide the total fixed costs by the contribution margin for each unit sold. Then determine how changes in unit sold and cost per unit affect Break-Even Unit, price between 7 and 17 in 1 increments and Cost per unit between 3.5 and 6.5 in 0.5 increments

I need it to be solved using modeling , sensitivity analysis and financial excel functions with excel or written.

Expert Solution

CALCULATION OF CONTRIBUTION MARGIN

CONTRIBUTION=SALES-VARIABLE COST

SALES=SELLING PRICE*NO OF CAKES PRODUCED PER MONTH

15BD*200=3000 BD

VARIABLE COST=INGREDIANT COST+LABOUR COST

4 BD+2 BD =6 BD

6 BD*200=1200

CONTRIBUTION = 3000-1200 = 1800

CONTRIBUTION PER UNIT=1800/200=9 BD

CONTRIBUTION-FIXED COST=PROFIT

PROFIT = 1800-200=1600

1)CALCULATION OF BREAK EVEN PRICE

BEP IN UNITS =FIXED COST/CONTRIBUTION PER UNIT

FIXED COST=200 BD

200/9=22.22 units

Break even point is the point.at which the total cost equal total revenue.there is no profit no loss.If sales exceeds the BEP,the business makes a profit.

CALCULATION OF HOW CHANGES IN UNIT SOLD AND COST PER UNIT AFFECT BEP

UNIT SOLD TOTAL SALES COST PER UNIT(TOTAl) CONTRIBUTION CONTRIBUTION(unit) BEP

100 15*100=1500 3.5*100= 350 1500-350=1150 1150/100=11.5 200/11.5=17.4

110 15*110=1650 3.5*110=385 1650-385=1265 1265/110=11.5 200/11.5=17.4

120 15*120=1800 4*120=480 1800-480=1320 1320/120=11 200/11=18

130 15*130=1950 4*130=520 1950-520=1430 1430/130=11 200/11=18

140 15*140=2100 4.5*140=630 2100-630=1470 1470/140=10.5 200/10.5=19

150 15*150=2250 4.5*150=675 2250-675 =1575 1575/150=10.5 200/10.5=19

160 15*160=2400 5*160=800 2400-800=1600 1600/160=10 200/10 = 20

170 15*170=2550 5*170=850 2550-850=1700 1700/170=10 200/10=20

180 15*180=2700 5.5*180=990 2700-990=1710 1710/180=9.5 200/9.5=21

190 15*190=2850 5.5*190=1045 2850-1045=1805 1805/190=9.5 200/9.5=21

200 15*200=3000 6*200=1200 3000-1200=1800 1800/200=9 200/9=22.22

200 15*200=3000 6.5*200=1300 3000-1300=1700 1700/200=8.5 200/8.5=23.52

UNIT PRICE TOTAL SALES COST PER UNIT(TOTAl) CONTRIBUTION CONTRIBUTION(unit) BEP

7 7*200=1400 3.5*200=700 1400-700=700 700/200=3.5 200/3.5=57

8 8*200=1600 4*200=800 1600-800=800 800/200=4 200/4=50

9 9*200=1800 4.5*200=900 1800-900=900 900/200=4.5 200/4.5=44

10 10*200=2000 5*200= 1000 2000-1000=1000 1000/200=5 200/5=40

11 11*200=2200 5.5*200=1100 2200-1100=1100 1100/200=5.5 200/5.5=36

12 12*200=2400 6 * 200=1200 2400-1200=1200 1200/200=6 200/6=33

13 13*200=2600 6.5*200=1300 2600-1300=1300 1300/200=6.5 200/6.5=30

14 14*200=2800 6*200=1200 2800-1200=1600 1600/200=8 200/8=25

15 15*200=3000 6.5*200=1300 3000-1700=1300 1300/200=6.5 200/6.5=30

16 16*200=2800 5.5*200=1100 2800-1100=1700 1700/200=8.5 200/8.5=23

17 17*200=3400 5*200=1000 3400-1000=2400 2400/200=12 200/12=16

ekkarill92 answered 1 year ago

The total fixed cost is $60 per month. (a) If the price pervisit is $60,...

The total fixed cost is $60 per month. (a) If the price per visit is $60, at what level of visits will the maximum profit position be? (b) What are the profits at this level?(c) What is the quantity supplied? (d) If the total fixed cost increases to $80 and the price per visit is $60, what is the quantity supplied (assuming maximizing profits)?quantity per visits supplied:1,2,3,4,5,6,total variable cost: 20,50,90,140,210,290

Quantity Total Cost Total Fixed Cost Total Variable Cost Average Fixed Cost Average Total Cost Average...

Quantity Total Cost Total Fixed Cost Total Variable Cost Average Fixed Cost Average Total Cost Average Variable Cost Marginal Cost 0 30 1 75 2 150 3 255 4 380 5 525 6 680 7 840 8 1010 9 1200 Given the quantity and total cost, calculate for total fixed cost, total variable cost, average fixed cost, average total cost, average variable cost, and marginal cost. Excel formulas would be nice but not required.

Labor Q Total Fixed Cost Total Variable Cost Total Cost Marginal Cost Average Fixed Cost Average...

Labor Q Total Fixed Cost Total Variable Cost Total Cost Marginal Cost Average Fixed Cost Average Variable Cost Average Total Cost 0 0 25 0 1 4 25 25 2 10 25 50 3 13 25 75 4 15 25 100 5 16 25 125 (a) Complete the blank columns. (b) Assume the price of this product equals $10. What’s the profit-maximizing output (q)? Note: managers maximize profits by setting MR=MC and under perfectly competitive markets, MR=Price. Thus, maximize profit...

From the following information on costs of production, calculate Total Fixed Cost, Total Variable Cost, Average...

From the following information on costs of production, calculate Total Fixed Cost, Total Variable Cost, Average Variable Cost, and Marginal Cost. I also need to graph the total cost curves as well as the average and marginal cost curves. TC = TFC + TVC so for Q=1 TC=30, assume TFC=20 so TVC=10 (20+10=30); continue with logic about FC, it is independent of output, so it would be an incremental 20 with each additional level of output, TVC for each Q...

Draw a graph showing the Total Fixed Cost, Total Variable Cost, and Total Cost curves.

INSTRUCTIONS FOR TABLE 1 and Two Graphs-21 points1) Calculate the Total Cost (TC) for each level of output. (3 points)2) Calculate the Average Fixed Cost (AFC) for each level of output. (3 points)3) Calculate the Average Variable Cost (AVC) for each level of output. (3 points)4) Calculate the Average Total Cost (ATC) for each level of output. (3 points)5) Calculate the Marginal Cost (MC) for each level of output. (3 points)Using the data from Table 1 draw two graphs:Draw...

Given the following total cost schedule of a firm, derive the total fixed cost and total...

Given the following total cost schedule of a firm, derive the total fixed cost and total variable cost schedules of the firm, and from them derive the average fixed cost, average variable cost, average total cost, and marginal cost schedules of the firm. Q1. Answer question above using the table below: Quantity TC($) TFC TVC AFC AVC ATC MC 0 1 2 3 4 5 Q 0 1 2 3 4 5 TC $30 50 60 81 118 180

Sara Nixon is looking for a fixed-income investment. She is considering two bond issues: a. A...

Sara Nixon is looking for a fixed-income investment. She is considering two bond issues: a. A Treasury with a yield of 5% b. An in-state municipal bond with a yield of 4% Sara is in the 33% federal tax bracket and the 8% state tax bracket. Calculate a) Treasury taxable equivalent yield and b) Muni taxable equivalent yield. Which bond would provide Sara with a higher tax-adjusted yield?

A firm incurred a total fixed cost of $400,000 and total variable cost of $600,000 to...

A firm incurred a total fixed cost of $400,000 and total variable cost of $600,000 to produce 50,000 units of output. What are the average fixed cost (AFC), average variable cost (AVC), and average total cost (ATC)?

The table below shows part of the cost structure (total fixed cost, total variable cost, and...

The table below shows part of the cost structure (total fixed cost, total variable cost, and total cost) for a typical producer of olive oil -- a perfectly competitive industry. Copy the table into Excel and use it to calculate average total cost and marginal cost for all quantities from 1 to 10. Use Excel (following the hints in QSet 2, #9) to produce a diagram of the firm’s average total cost and marginal cost curves. If the price of...

Red Rider makes three types of electric scooters. The company’s total fixed cost is $1,296,000,000. Selling...

Red Rider makes three types of electric scooters. The company’s total fixed cost is $1,296,000,000. Selling prices, variable cost, and sales percentages for each type of scooter follow: Selling Price Variable Cost Percent of Total Unit Sales Mod. $2,200. $1,900. 30 Rad. $3,700. $3,000. 50 X-treme $6,000. $5,000. 20 a. What is Red Rider’s break-even point in units and sales dollars? Units. Dollars Mod Rad X-treme Total b. If the company has an after-tax income goal of $1 billion and...