In: Accounting
Activity #1 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.
1) Create a new Excel spreadsheet.
2) Create a new worksheet in the Excel spreadsheet – In this worksheet, create a model that allows the following variables to be 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
Create formulas to calculate the total sales (revenue) and total COGS.
3) Create another worksheet (2) in the same spreadsheet – In this worksheet, create an income statement to model profitability, using the following::
a. 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.
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.
Sheet 1
ABC cycle Inc | |||||
Particulars | Road | Mountain | Tandem | Electric | Hybrid |
Sales quantity | 10000 | 5000 | 2000 | 15000 | 25000 |
selling price per cycle( $) | 60 | 100 | 80 | 200 | 250 |
Cost of goods sold per unit($) | 40 | 50 | 60 | 160 | 180 |
Sheet 2
Income statement | ||||||
Particulars | Road | Mountain | Tamdem | Electric | Hybrid | Total |
Sales(in dollar) | 600000 | 500000 | 160000 | 3000000 | 6250000 | 10510000 |
Less:cost of goods sold(in dollar) | 400000 | 250000 | 120000 | 2400000 | 4500000 | 7670000 |
Gross profit(in dollar) | 200000 | 250000 | 40000 | 600000 | 1750000 | 2840000 |
Particulars | Amount in dollar | Percentage on sales |
Sales | 10510000 | 100% |
Cost of goods sold | 7670000 | 73% |
Gross profit | 2840000 | 27% |
Less:selling and administration | 25000 | |
Research and development | 40000 | |
Miscellanous overhead | 5000 | |
Net profit(gross profit-indirect expenses) | 2770000 | 26% |