elevant cash flows for a marketing campaign / Need Excel Formulas, please Marcus Tube, a manufacturer of high-quality aluminum tubing, has maintained stable sales and profits over the past 10 years. Although the market for aluminum tubing has been expanding by 4% per year, Marcus has been unsuccessful in sharing this growth. To increase its sales, the firm is considering an aggressive marketing campaign that centers on regularly running ads in all relevant trade journals and exhibiting products at all major regional and national trade shows. The campaign is expected to require an annual tax-deductible expenditure of $159,000 over the next 5 years. Sales revenue, as shown in the income statement for 2020 totaled $19,700,000. If the proposed marketing campaign is not initiated, sales are expected to remain at this level in each of the next 5 years, 2021 through 2025. With the marketing campaign, sales are expected to rise to the levels shown in the table for each of the next 5 years; cost of goods sold is expected to remain at 74% of sales; general and administrative expense (exclusive of any marketing campaign outlays) is expected to remain at 11% of sales; and annual depreciation expense is expected to remain at $510,000. Assuming a 21% tax rate, find the net cash flows over the next 5 years associated with the proposed marketing campaign. How do I create excel spreadshet (with formulas) for these questions? Question content area bottom Part 1 The annual operating cash flow without the marketing campaign will be $2,441,5502,441,550. (Round to the nearest dollar.) Part 2 Calculate the net profits after taxes and operating cash flows with the marketing campaign below: (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2021 Sales $ 20,200 Cost of good sold (74%) $ 14,948 Gross profit $ 5,252 Less: Operating expenses General and administrative expense (11%) $ 2,222 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 2,891 Net profit before taxes $ 2,361 Less: Taxes (21%) $ 496 Net profit after taxes $ 1,865 Operating cash flow $ 2,375 Incremental cash flow $ (67) Part 3 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2022 Sales $ 20,700 Cost of good sold (74%) $ 15,318 Gross profit $ 5,382 Less: Operating expenses General and administrative expense (11%) $ 2,277 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 2,946 Net profit before taxes $ 2,436 Less: Taxes (21%) $ 512 Net profit after taxes $ 1,924 Operating cash flow $ 2,434 Incremental cash flow $ (8) Part 4 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2023 Sales $ 21,200 Cost of good sold (74%) $ 15,688 Gross profit $ 5,512 Less: Operating expenses General and administrative expense (11%) $ 2,332 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 3,001 Net profit before taxes $ 2,511 Less: Taxes (21%) $ 527 Net profit after taxes $ 1,984 Operating cash flow $ 2,494 Incremental cash flow $ 52 Part 5 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2024 Sales $ 22,200 Cost of good sold (74%) $ 16,428 Gross profit $ 5,772 Less: Operating expenses General and administrative expense (11%) $ 2,442 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 3,111 Net profit before taxes $ 2,661 Less: Taxes (21%) $ 559 Net profit after taxes $ 2,102 Operating cash flow $ 2,612 Incremental cash flow $ 170 Part 6 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2025 Sales $ 23,200 Cost of good sold (74%) $ 17,168 Gross profit $ 6,032 Less: Operating expenses General and administrative expense (11%) $ 2,552 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 3,221 Net profit before taxes $ 2,811 Less: Taxes (21%) $ 590 Net profit after taxes $ 2,221 Operating cash flow $ 2,731 Incremental cash flow $ 289

elevant cash flows for a marketing campaign / Need Excel Formulas, please Marcus Tube, a manufacturer of high-quality aluminum tubing, has maintained stable sales and profits over the past 10 years. Although the market for aluminum tubing has been expanding by 4% per year, Marcus has been unsuccessful in sharing this growth. To increase its sales, the firm is considering an aggressive marketing campaign that centers on regularly running ads in all relevant trade journals and exhibiting products at all major regional and national trade shows. The campaign is expected to require an annual tax-deductible expenditure of $159,000 over the next 5 years. Sales revenue, as shown in the income statement for 2020 totaled $19,700,000. If the proposed marketing campaign is not initiated, sales are expected to remain at this level in each of the next 5 years, 2021 through 2025. With the marketing campaign, sales are expected to rise to the levels shown in the table for each of the next 5 years; cost of goods sold is expected to remain at 74% of sales; general and administrative expense (exclusive of any marketing campaign outlays) is expected to remain at 11% of sales; and annual depreciation expense is expected to remain at $510,000. Assuming a 21% tax rate, find the net cash flows over the next 5 years associated with the proposed marketing campaign. How do I create excel spreadshet (with formulas) for these questions? Question content area bottom Part 1 The annual operating cash flow without the marketing campaign will be $2,441,5502,441,550. (Round to the nearest dollar.) Part 2 Calculate the net profits after taxes and operating cash flows with the marketing campaign below: (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2021 Sales $ 20,200 Cost of good sold (74%) $ 14,948 Gross profit $ 5,252 Less: Operating expenses General and administrative expense (11%) $ 2,222 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 2,891 Net profit before taxes $ 2,361 Less: Taxes (21%) $ 496 Net profit after taxes $ 1,865 Operating cash flow $ 2,375 Incremental cash flow $ (67) Part 3 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2022 Sales $ 20,700 Cost of good sold (74%) $ 15,318 Gross profit $ 5,382 Less: Operating expenses General and administrative expense (11%) $ 2,277 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 2,946 Net profit before taxes $ 2,436 Less: Taxes (21%) $ 512 Net profit after taxes $ 1,924 Operating cash flow $ 2,434 Incremental cash flow $ (8) Part 4 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2023 Sales $ 21,200 Cost of good sold (74%) $ 15,688 Gross profit $ 5,512 Less: Operating expenses General and administrative expense (11%) $ 2,332 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 3,001 Net profit before taxes $ 2,511 Less: Taxes (21%) $ 527 Net profit after taxes $ 1,984 Operating cash flow $ 2,494 Incremental cash flow $ 52 Part 5 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2024 Sales $ 22,200 Cost of good sold (74%) $ 16,428 Gross profit $ 5,772 Less: Operating expenses General and administrative expense (11%) $ 2,442 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 3,111 Net profit before taxes $ 2,661 Less: Taxes (21%) $ 559 Net profit after taxes $ 2,102 Operating cash flow $ 2,612 Incremental cash flow $ 170 Part 6 (Round to the nearest $1,000.) Marcus Tube Calculation of Relevant Cash Flow ($000) 2025 Sales $ 23,200 Cost of good sold (74%) $ 17,168 Gross profit $ 6,032 Less: Operating expenses General and administrative expense (11%) $ 2,552 Marketing campaign $ 159 Depreciation $ 510 Total operating expenses $ 3,221 Net profit before taxes $ 2,811 Less: Taxes (21%) $ 590 Net profit after taxes $ 2,221 Operating cash flow $ 2,731 Incremental cash flow $ 289

FINANCIAL ACCOUNTING

10th Edition

ISBN:9781259964947

Author:Libby

Publisher:Libby

Chapter1: Financial Statements And Business Decisions

Section: Chapter Questions

Problem 1Q

See similar textbooks

Question content area bottom

Part 1

The annual operating cash flow without the marketing campaign will be

$2,441,5502,441,550.

(Round to the nearest dollar.)

Part 2

Calculate the net profits after taxes and operating cash flows with the marketing campaign below: (Round to the nearest $1,000.)

Marcus Tube
Calculation of Relevant Cash Flow ($000)
		2021
Sales	$	20,200
Cost of good sold (74%)	$	14,948
Gross profit	$	5,252
Less: Operating expenses
General and administrative expense (11%)	$	2,222
Marketing campaign	$	159
Depreciation	$	510
Total operating expenses	$	2,891
Net profit before taxes	$	2,361
Less: Taxes (21%)	$	496
Net profit after taxes	$	1,865
Operating cash flow	$	2,375
Incremental cash flow	$	(67)

Part 3

(Round to the nearest $1,000.)

Marcus Tube
Calculation of Relevant Cash Flow ($000)
		2022
Sales	$	20,700
Cost of good sold (74%)	$	15,318
Gross profit	$	5,382
Less: Operating expenses
General and administrative expense (11%)	$	2,277
Marketing campaign	$	159
Depreciation	$	510
Total operating expenses	$	2,946
Net profit before taxes	$	2,436
Less: Taxes (21%)	$	512
Net profit after taxes	$	1,924
Operating cash flow	$	2,434
Incremental cash flow	$	(8)

Part 4

(Round to the nearest $1,000.)

Marcus Tube
Calculation of Relevant Cash Flow ($000)
		2023
Sales	$	21,200
Cost of good sold (74%)	$	15,688
Gross profit	$	5,512
Less: Operating expenses
General and administrative expense (11%)	$	2,332
Marketing campaign	$	159
Depreciation	$	510
Total operating expenses	$	3,001
Net profit before taxes	$	2,511
Less: Taxes (21%)	$	527
Net profit after taxes	$	1,984
Operating cash flow	$	2,494
Incremental cash flow	$	52

Part 5

(Round to the nearest $1,000.)

Marcus Tube
Calculation of Relevant Cash Flow ($000)
		2024
Sales	$	22,200
Cost of good sold (74%)	$	16,428
Gross profit	$	5,772
Less: Operating expenses
General and administrative expense (11%)	$	2,442
Marketing campaign	$	159
Depreciation	$	510
Total operating expenses	$	3,111
Net profit before taxes	$	2,661
Less: Taxes (21%)	$	559
Net profit after taxes	$	2,102
Operating cash flow	$	2,612
Incremental cash flow	$	170

Part 6

(Round to the nearest $1,000.)

Marcus Tube
Calculation of Relevant Cash Flow ($000)
		2025
Sales	$	23,200
Cost of good sold (74%)	$	17,168
Gross profit	$	6,032
Less: Operating expenses
General and administrative expense (11%)	$	2,552
Marketing campaign	$	159
Depreciation	$	510
Total operating expenses	$	3,221
Net profit before taxes	$	2,811
Less: Taxes (21%)	$	590
Net profit after taxes	$	2,221
Operating cash flow	$	2,731
Incremental cash flow	$	289

### Marcus Tube Income Statement for the Year Ended December 31, 2020

**Sales Revenue:**
- $19,700,000

**Less: Cost of Goods Sold (74%):**
- $14,578,000

**Gross Profits:**
- $5,122,000

**Less: Operating Expenses**
- General and Administrative Expense (11%): $2,167,000
- Depreciation Expense: $510,000

**Total Operating Expense:**
- $2,677,000

**Earnings Before Interest and Taxes:**
- $2,445,000

**Less: Taxes (21%):**
- $513,450

**Net Operating Profit After Taxes:**
- $1,931,550

### Notes:

- The statement breaks down the financial performance by listing sales revenue and subtracting various expenses to calculate the net operating profit.
- Cost of goods sold is presented as a percentage of sales revenue, highlighting the proportion of costs relative to sales.
- Operating expenses are further detailed into administrative expenses and depreciation to show their impact on total expenses.
- The income statement provides a clear overview of profitability before and after tax deductions.

Click on the icon here to copy the contents of the data table below into a spreadsheet.

**Marcus Tube Sales Forecast**

| Year | Sales Revenue |
|------|---------------|
| 2021 | $20,200,000 |
| 2022 | $20,700,000 |
| 2023 | $21,200,000 |
| 2024 | $22,200,000 |
| 2025 | $23,200,000 |

This table presents the sales forecast for Marcus Tube over a five-year period, from 2021 to 2025. It shows a gradual increase in sales revenue each year.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 2 images

SEE SOLUTION Check out a sample Q&A here