Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Copy resulting income statement in worksheet (2) into a new Microsoft Word document, and label the income statement as Deliverable 1. Deliverable 2: Now return

Copy resulting income statement in worksheet (2) into a new Microsoft Word document, and label the income statement as "Deliverable 1". Deliverable 2: Now return to worksheet (1) and reduce the price per unit of all bicycles by 25%. Copy the resulting income statement in worksheet (2) into the same Microsoft Word document, and label the income statement as "Deliverable 2".

Activity #2 Goal: Produce an Excel spreadsheet that allows your company to model the sales quantity required to break-even, given a specified price per unit and COGS per unit. The break-even 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.

Break-Even Point = Total overhead cost / Contribution Margin

1) do it in a new Excel worksheet (3) in the same Excel file utilized in activity #1. In this worksheet, do 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 break-even point using the supplied formulas above.

2) do it in a new Excel worksheet (4) - In this worksheet, do it in income statement, using the same format specified in Activity #1 step 3. Utilize the following:

a. The total sales (revenue) and total COGS should be calculated using the price per unit, the COGS per unit, and break-even point (sales quantity) from worksheet (3).

b. Selling and General Admin (SG&A) expenses of $20,000.

c. Research & Development (R&D) expenses of $10,000.

d. Miscellaneous Overhead expenses of $10,000.

e. 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.

Copy the resulting income statement in worksheet (4) into the same Microsoft Word document used in Activity #1. Label the income statement as "Deliverable 3".

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. Change values in worksheet (3) accordingly.

Copy the resulting income statement in worksheet (4) into the same Microsoft Word document. Label the income statement as "Deliverable 4".

Remember, if the net profit does not equal zero in the resulting income statements, the break-even point has been calculated incorrectly, or the spreadsheet formulas are incorrect.

image text in transcribedimage text in transcribed
Deliverable 1: Enter the following values for sales quantity, price per unit, and COGS per unit for each bicycle, as shown below, into your model in worksheet). Sales Qty Price per unit COGS per unit Road 1000 $1000 5700 Mountain 1500 $1250 $800 Tandem 50 $2000 5900 Electric 750 $2500 $1400 Hybrid 400 $600 $350 Utilize the following format for the income statement: 96 of sales Revenue 5 60?.500 100.0% COGS S 43?,500 ?2.0% Gross Prot $170,000 28.0% 5&GA 5 25,000 4.1% REID 5 40,000 6.6% Misc Overhead 5 10,000 1.6% Total Open 5 75,000 12.3% Nit Profit 5 95,000 15.6%

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamental Financial Accounting Concepts

Authors: Thomas P. Edmonds, Frances M. Mcnair, Philip R. Olds, Mark Edmonds, Christopher Edmonds

10th Edition

126015940X, 978-1260159400

More Books

Students also viewed these Accounting questions

Question

Explain why the market supply curve of a resource slopes upward.

Answered: 1 week ago