Your task is to create the 2021 Budget in an Excel Spreadsheet. You only need to prepare...

Your task is to create the 2021 Budget in an Excel Spreadsheet. You only need to prepare an Income Statement

Assume:

The company will sell 16,000 bicycles at an average price of $2,500 each
Cost of Labor is 20% of Sales
Cost of Materials is 10% of Sales
Variable Overhead is 6% of Sales
Fixed Manufacturing Overhead is $4,000,000
Office Salaries are 5,000,000
Office Deprecation Expense is $500,000
Lease Expense of Corporate Office is $2,000,000
Advertising is $1,000,000
Income Taxes are 30% of all revenues minus all expenses before income taxes.

The Board of Directors will also want to know, with the assumptions above what would be the company’s:

Gross Profit Margin?
Operating Profit Margin?
Net Profit Margin?
Number of bicycles to sell to breakeven? Assume if the company is breakeven, income taxes will be zero.

Solutions

Expert Solution

Thank you.

For any clarification please comment

ekkarill92 answered 1 year ago

Next > < Previous

Related Solutions

Create a budget spreadsheet for a hypothetical organization in excel

Using Excel, Create a Household Budget Spread Sheet. Your spreadsheet should list monthly household budget and...

Using Excel, Create a Household Budget Spread Sheet. Your spreadsheet should list monthly household budget and various household expenses for 3 months. You will need to use a Totals Column or Row, and Formula to show if you are spending more money than you are bringing in or if you have a surplus! Also insert a Chart using any part of the data in your spreadsheet.

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%...

You need to create an excel spreadsheet that answers the following questions. Where stated, make sure...

You need to create an excel spreadsheet that answers the following questions. Where stated, make sure you solve the problem “by hand”, i.e. discounting each cash flow, and also using TVM formulas from Excel. All work should be presented in three sheets/tabs (not files). Your sheets should be labeled. Questions 1-4 are in the first tab, questions 5-6 are in the second tab, and question 7 is in a third tab. In all cases, presentation matters!!! Make these professional. Also,...

Calculations must be done in Excel – You must create your own spreadsheet do not copy...

Calculations must be done in Excel – You must create your own spreadsheet do not copy and paste someone else’s. Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three year contract. The new machine will cost $1.9 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $120,000. The contract will not continue beyond three years and...

Create an Excel spreadsheet or use the project template to show your computations for the first...

Create an Excel spreadsheet or use the project template to show your computations for the first 10 ratios listed for Apple Inc. and Alphabet Inc., in 2017. Supporting calculations must be shown either as a formula or as text typed into a different cell. Earnings per share of common stock (as given in the income statement) current ratio (current assets divided by current liabilities) gross profit rate (gross profit divided by net sales) profit margin (net income divided by net...

Subjects