Produce an Excel spreadsheet that allows your company to model profitability. Assume your company produces bicycles and
Question:
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.
Fundamentals of Investing
ISBN: 978-0133075359
12th edition
Authors: Scott B. Smart, Lawrence J. Gitman, Michael D. Joehnk