Financial Model Practice Assignment:

Please help me figure out how to understand this practice assignment:

Goal:

Produce an Excel spreadsheet that allows your company to model profitability. Assume your company produces bicycles and manufactures these different models: road, mountain, tandem, electric and hybrid.

Activity #1

1. Start a new Excel spreadsheet.
2. Start a new worksheet (1) in the Excel spreadsheet - In this worksheet, generate a model that allows the following variables to be changed for each of the 5 bicycle types:

• The sales quantity
• The sales price per unit
• The Cost of Goods Sold (COGS) per unit

Create formulas to calculate the total sales (revenue) and total COGS.

3) Make another worksheet (2) in the same spreadsheet - In this worksheet, produce an income statement to model profitability, using the following:

• The total sales (revenue) and total COGS from worksheet (1).Hint: Use formulas to obtain the total sales (revenue) and total COGS from the worksheet.
• Selling and General Admin (SG&A) expenses of $25,000.
• Research & Development (R&D) expenses of $40,000.
• Miscellaneous Overhead expenses of $5000.
• Use formulas to calculate Gross Profit, Total Opex and Net Profit and all of the % of sales values.

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(1).

Sales Qty Price per unit COGS per unit

Road $1000 $1000 $700

Mountain $1500 $1250 $800

Tandem $ 50 $2000 $900

Electric $750 $2500 $1400

Hybrid $400 $600 $350

Copyresulting 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 breakeven, given a specified price per unit and COGS per unit. The breakeven point is thesales quantityyou 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

1. Add a new Excel worksheet (3) in the same Excel file utilized in activity #1. In this worksheet, use 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. Add a new Excel worksheet (4) - In this worksheet, produce an income statement, using the same format specified in Activity #1 step 3. 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 General Admin (SG&A) expenses of $20,000.
• Research & Development (R&D) expenses of $10,000.
• Miscellaneous Overhead expenses of $10,000.
• 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".

please upload both activities, excel sheets and deliverables.