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 (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: Revenue $ COGS $ Gross Profit $ S&GA $ R&D Misc Overhead $ Total Opex $ Net Profit $ un 1,000 800 200 100 25 10 135 65 100.0% 80.0% 20.0% 10.0% 2.5% 1.0% 13.5% 6.5% 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%. 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 sell 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 Opex cost / Contribution Margin 1) Create a new Excel worksheet (3) in the same Excel file utilized in activity #1. In this worksheet, create a model that allows you to enter the sales price per unit and COGS per unit. Obtain the total Opex cost from worksheet (4) (defined below in step 2) via a cell reference. Using these values, create formulas that calculate contribution margin and break-even point using the supplied formulas above. Calculate the resulting sales revenue and COGS based upon this break-even point. 2) Create a new Excel worksheet (4) - In this worksheet, create an income statement, using the same format specified in Activity #1. Utilize the following: a. The sales (revenue) and COGS should be obtained from worksheet (3) using cell references. b. Selling and General Admin (SG&A) expenses of $40,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 $14 per board. The cost for making each board (COGS) is $9. 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 sales price of skateboards rises to $16 per board. However, the cost of the wheels has increased, and the cost for making each board (COGS) is now $10. 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 To complete the exercise and submit for grading: Please review the grading rubric for the assignment. Perform Activity #1 and #2 Upload the Microsoft Word document containing the four deliverables AND the Excel Spreadsheet to drop box: "Assignment 1: Financial Modeling with Excel