Produce an Excel spreadsheet that allows your company to model the sales quantity required to breakeven, given a specified price per unit and COGS per unit The breakeven point is the sales quantity you must sale in order to have enough revenue to cover all costs and show zero net profit i e you neither lose nor make money Assume your company produces skateboards Contribution Margin Sales Price per unit COGS per unit BreakEven Point Total overhead cost Contribution Margin create a model that allows you to enter the sales price per unit, COGS per unit and total overhead cost (Opex) Using these values, create formulas that calculate contribution margin and breakeven point using the supplied formulas above 2) Create a new Excel worksheet (4) In this worksheet, create an income statement Utilize the following The total sales(revenue)and total COGS should be calculated using the price per unit, the COGS per unit, and breakeven point (sales quantity) from worksheet (3) Selling and GeneralAdmin(SG A) expenses of$20,000 Research Development (R D) expenses of $10,000 Miscellaneous Overhead expenses of$10,000 Deliverable 4 Assume the skateboards continue to be sold at $12 per board However, the cost of the wheels has increased, and the cost for making each board (COGS) is now $8 Use formulas to calculate Gross Profit, Total Opex and Net Profit and all of the of sales values Deliverable 3 Assume skateboards are sold for $12 per board The cost for making each board (COGS) is $7 Enter these values in worksheet (3), as well as total overhead cost from item 2 above Deliverable 4 Assume the skateboards continue to be sold at $12 per board However, the cost of the wheels has increased, and the cost for making each board (COGS) is now $8 AutoSave OFF AGE Book 1 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X X LE Calibri (Body) v 12 Al A Insert v Wrap Text Percentage 22 O 4 DX Delete Paste B 1 A Ideas S Merge Center Y Sensitivity Conditional Format Cell Formatting as Table Styles Farmatu Sort Filter Find Select Y fx B8 B2 A B D E F 1 1 L M N N F a Formula Builder percentage 5,090,000 4,150 5,085,850 25,000 40,000 5,000 70,000 5,020,000 C of sales 100 D 18 0 08 0 49 0 80 0 10 1 39 99 1 2 Revenue $ 3 COGS 4 Gross Profit $ 5 SEGA $ 6 RAD S $ 7 Misc Overhead $ 8 Tatal Opex $ 9 Net Profit $ 10 11 12 13 14 15 16 17 18 19 20 Insert Function 22 23 fa PERCENTILE This function is available for compathlity with Excel 2007 and earlier Returns the kth percentile di values in a range 25 26 27 28 29 30 31 32 33 34 35 36 37 Syntax PERCENTILE arraykl Array is the array or range of data that delines relative standing ki is the percentile value that is baturan D through 1, inclusive 38 39 40 41 More help on this function Sheet1 Sheet2 Shocta Ready 100

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 30, 2024

Produce an Excel spreadsheet that allows your company to model the sales quantity required to breakeven, given a specified price per unit and COGS per

image text in transcribed Produce an Excel spreadsheet that allows your company to model the sales quantity required to breakeven, given a specified price per unit and COGS per unit. The breakeven point is the sales quantity you must sale in order to have enough revenue to cover all costs and show zero net profit. i.e. you neither lose nor make money. Assume your company produces skateboards.

Contribution Margin = Sales Price per unit COGS per unit. BreakEven Point = Total overhead cost / Contribution Margin

create a model that allows you to enter the sales price per unit, COGS per unit and total overhead cost (Opex). Using these values, create formulas that calculate contribution margin and breakeven point using the supplied formulas above.
2) Create a new Excel worksheet (4) In this worksheet, create an income statement. Utilize the following:
1. The total sales(revenue)and total COGS should be calculated using the price per unit, the COGS per unit, and breakeven point (sales quantity) from worksheet (3).
2. Selling and GeneralAdmin(SG&A) expenses of$20,000.
3. Research & Development (R&D) expenses of $10,000.
4. Miscellaneous Overhead expenses of$10,000.
5. Deliverable 4: Assume the skateboards continue to be sold at $12 per board. However, the cost of the wheels has increased, and the cost for making each board (COGS) is now $8.
6. Use formulas to calculate Gross Profit, Total Opex and Net Profit and
  
  all of the % of sales values.

Deliverable 3: Assume skateboards are sold for $12 per board. The cost for making each board (COGS) is $7. Enter these values in worksheet (3), as well as total overhead cost from item #2 above.

Deliverable 4: Assume the skateboards continue to be sold at $12 per board. However, the cost of the wheels has increased, and the cost for making each board (COGS) is now $8.

AutoSave OFF AGE Book 1 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X X LE Calibri (Body) v 12 ~ Al A Insert v Wrap Text Percentage 22 -O 4 DX Delete Paste B 1 A = = = Ideas S-% Merge & Center Y Sensitivity Conditional Format Cell Formatting as Table Styles Farmatu Sort & Filter Find & Select Y fx =B8/B2 A B D E F 1 1 L M N N F a Formula Builder percentage 5,090,000 4,150 5,085,850 25,000 40,000 5,000 70,000 5,020,000 C * of sales 100% D.18% 0.08% 0.49% % 0.80% 0.10% 1.39% 99% 1 2 Revenue $ 3 COGS 4 Gross Profit $ 5 SEGA $ 6 RAD S $ 7 Misc Overhead $ 8 Tatal Opex $ 9 Net Profit $ 10 11 12 13 14 15 16 17 18 19 20 Insert Function 22 23 fa PERCENTILE This function is available for compathlity with Excel 2007 and earlier. Returns the kth percentile di values in a range. 25 26 27 28 29 30 31 32 33 34 35 36 37 Syntax PERCENTILE arraykl Array: is the array or range of data that delines relative standing ki is the percentile value that is baturan D through 1, inclusive 38 39 40 41 More help on this function Sheet1 Sheet2 Shocta + Ready + 100% %