Question

In: Accounting

Activity #1 Goal: Produce an Excel spreadsheet that allows your company to model profitability. Assume your...

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.

Solutions

Expert Solution

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%

Related Solutions

Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm that is in the 35% tax bracket forecasts that it can retain $4 million of new earnings plans to raise new capital in the following proportions: 60% from 30-year bonds with a flotation cost of 4% of face value. Their current bonds are selling at a price of 91...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company is evaluating the purchase of a machine to improve product quality and output levels. The new machine would cost $1.6 million and would be depreciated for tax purposes using the straight-line method over an estimated six-year life to its expected salvage value of $100,000. The new machine would require...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have interest expense of $2,000,000 and 1,000,000 common shares outstanding, whereas the second would have interest expense of $3,800,000 but only 750,000 shares outstanding. The company is in the 35% tax bracket. Part 1: Construct the...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm had the following abbreviated income statement for 2020 and abbreviated balance sheets at the end of 2020 and 2021. 2020 Sales $600,000 Less Cost of goods sold 320,000 Gross Profit 280,000 Less Operating expenses 190,000 Less Depreciation 30,000 Operating Income (EBIT) 60,000 Less Interest expense 20,000 Earnings Before Taxes...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have $25 million of bonds at 8% interest and 1,000,000 common shares outstanding, whereas the second would have $47.5 million of bonds at 8% interest and only 750,000 shares outstanding. The company is in the 35%...
Create an Excel spreadsheet to organize your answers to the following problem. A company with EBIT...
Create an Excel spreadsheet to organize your answers to the following problem. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have $25 million of bonds at 8% interest and 1,000,000 common shares outstanding, whereas the second would have $47.5 million of bonds at 8% interest and only 750,000 shares outstanding. The company is in the 35% tax bracket. Part 1: Construct the bottom half of the income statement (including EPS) for each financing...
CVP and Break-Even Goal: Create an Excel spreadsheet to perform CVP analysis and show the relationship...
CVP and Break-Even Goal: Create an Excel spreadsheet to perform CVP analysis and show the relationship between price, costs, and break-even points in terms of units and dollars. Use the results to answer questions about your findings. Scenario: Phonetronix is a small manufacturer of telephone and communications devices. Recently, company management decided to investigate the profitability of cellular phone production. They have three different proposals to evaluate. Under all the proposals, the fixed costs for the new phone would be...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...
Please use Excel to solve the assignment and submit as an excel spreadsheet. Bethesda Mining Company...
Please use Excel to solve the assignment and submit as an excel spreadsheet. Bethesda Mining Company Based on a Mini Case presented in the textbook Ross, S.A., R.W. Westerfield and J. Jaffe, Corporate Finance, McGraw Hill/Irwin. Bethesda Mining is a midsized coal mining company with 20 mines located in Ohio, Pennsylvania, West Virginia and Kentucky. The company operates deep mines as well as strip mines. Most of the coal mined is sold under contract, with excess production sold on the...
1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project...
1. Understand how to use EXCEL Spreadsheet (a)  Develop proforma Income Statement Using Excel Spreadsheet (b)  Compute  Net Project Cashflows, NPV,  and IRR (c) Develop problem-solving and  critical thinking skills and make long-term investment decisions 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) $200,000 2) New equipment cost $(200,000) 9) Sales increase per year 5% 3) Equipment ship & install cost $(35,000) 10) Operating cost (60% of Sales) $(120,000) 4) Related start up cost $(5,000)     (as a percent...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT