Model profitability.

Assume your company produces bicycles and manufactures these different models:
road, mountain, tandem, electric and hybrid.
1) Create a new Excel spreadsheet.
2) Create a new worksheet (1) in the Excel spreadsheet – In this worksheet, create a
model that allows the following variables to be specified and changed for each of
the 5 bicycle types:
1) The sales quantity
2) The sales price per unit
3) The Cost of Goods Sold (COGS) per unit
3) In worksheet (1), in addition to being able to physically enter a sales price for each
product individually, as described in step 2, also create the ability to increase or
decrease the prices of all products by a single, specified percentage (example
10% price increase for all products). Use a formula for this calculation, so that
the percentage amount can be changed.
4) In worksheet (1), create formulas to calculate the sales (revenue) and COGS for
each type of bicycle. Also create formulas to calculate the total sales (revenue)
and total COGS for all 5 bicycles combined. For example, total sales (revenue)
would represent the sales revenue of each of the 5 bicycles added together.
5) Create another worksheet (2) in the same spreadsheet – In this worksheet, create
an income statement to model profitability, using the following:
a. Sales (revenue) and COGS from worksheet (1). Use cell references to
obtain the total sales (revenue) and total COGS from worksheet (1).
b. Selling and General Admin (SG&A) expenses of $25,000.
c. Research & Development (R&D) expenses of $40,000.
d. Miscellaneous Overhead expenses of $5000.
e. Use formulas to calculate Gross Profit, Total Opex and Net Profit and all of
the % of sales values.
Utilize the following format for the income statement:
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
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%.

Sample Solution