PLEASE SHOW THE EXCEL FORMULAS. Thank you so much.

You invented "Dura-Clear windows' that never need washing! Nothing sticks to them - nt pollution, pollen, dirt, dust, bird droppings, fingerprints, nothing. You've invested all your own savings, your parents' savings, and some of your friends' savings as well into the R&D, production, and start-up of your business.

It's been three years since you started selling your windows primarily to single-family homeowners, but now apartment building contractors across the nation have been requesting your windows. Unfortunately, you're already operating at full capacity. It's time to make the switch from single-family homes to commercial buildings, but to do so requires a large infusion of funding for expansion. You need more of everything: space, equipment, employees, etc.

Ever the optimist, you applied to the TV show Shark Tank--where entrepreneurs compete for funding from angel investors ("sharks")--to see if a shark will invest in your company and serve as a mentor as well. To your surprise, you were accepted to the show! You've already prepared most of your marketing and sales pitches, and now it's time to put together your forecasted financial statements, You intend to show the sharks that backing your business would be a very profitable investment. You also need to determine how much funding to ask for in exchange for how much ownership you'll give up.

The financial statements below show Actual data (in white) and Forecasted data (highlighted in blue).

Use the data below to complete your Milestone 3 Metrics and answer the Milestone 3 Questions.

PLEASE NOTE: The figures provided below are NOT the solution to Milestone 2!

DURA-CLEAR WINDOWS, LLC

Proforma Income Statement

2020

2021

2,022

2,023

2,024

2,025

Sales (all on credit)

Cost of Goods Sold

Gross Profit

Selling and Administrative Expense

Operating profit (EBIT)

Interest expense

Net Income before Taxes

Taxes (36%)

Net Income

Shares

Earnings per Share

1,200,000

(800,000)

400,000

(304,900)

95,100

(35,000)

60,100

(36,900)

23,200

60,000

$0.39

1,500,000

(1,040,000)

460,000

(350,500)

109,500

(45,000)

64,500

(49,200)

15,300

60,000

$0.26

-

1,875,000

(1,105,000)

770,000

443 700)

326,300

(85.000)

241,300

(55,600)

185,700

78,000

$2.38

4,000,000

(1,957,333)

2,042,667

(1,100,120)

942,547

(70,000)

872,547

(314,117)

558,430

104,000

$5.37

5,000,000

(2,446,667)

2,553,333

(1,271,650)

1,281,683

(60,000)

1,221,683

(439,806)

781,877

104,000

$7.52

6,000,000

(2,936,000)

3,064,000

(1,443,180)

1,620,820

(50,000)

1,570,820

(565,495)

1,005,325

104,000

$9.67

-

2,026

7,000,000

(3,425,333)

3,574,667

(1,614,710)

1,959,957

(40,000)

1,919,957

(691,184)

1,228,772

104,000

$11.82

2,027

8,000,000

(3,914,667)

4,085,333

(1,786,240)

2,299,093

(30 000)

2,269,093

(816,874)

1,452,220

104,000

$13.96

DURA-CLEAR WINDOWS, LLC

Proforma Balance Sheet

ASSETS

Cash

Marketable Securities

Accounts Receivable

Inventory

Total Current Assets:

Plant and equipment

Less: accumulated depreciation

Net Plant and equipment

Total Assets

2020

30,000

20,000

170,000

230,000

450,000

650,000

(65,000)

585,000

1,035,000

LIABILITIES & STOCKHOLDER'S EQUITY

Accounts Payable

200,000

Accrued Expenses

20,400

Total Current Liabilities

220,400

Long-term Liabilities

325,000

Total Liabilities

545,400

Common Stock (51 par)

60,000

Capital paid in excess of par

190,000

Retained Earnings

239,600

Total Stockholder's Equity

489,600

Total Liabilities & Stockholder's Equi|1,035,000|

2021

2022

40,000

25,000

259.000

261,000

585,000

765,000

(141,500)

623,500

1.208,500

30,000

30,000

360,000

290,000

710.000

1,390,000

(280,500)

1,109,500

1,819,500

310,000

505,000

30,000

35,000

340,000

540,000

363,600

703,900

703,600

1,243,900

60,000

78,000

190,000

262,000

254,900

504,900

235,600

575,600

1.208,500

1,819,500

2023

2024

60,000

40,000

600,000

693,778

1,393,778

2,140,000

(494,500)

1,645,500

3.039,278

60,000

40,000

800,000

867,222

1,767,222

2,140,000

(708,500)

1,431,500

3,198,722

748,560

15,000

823,560

603.900

1,427,460

104.000

1,236,000

271,818

1,611,818

3.039.278

949.222

95,000

1,044,222

503,900

1,548,122

104,000

1,236,000

310,600

1,650.600

3.198,722

2025

60,000

40,000

1,000,000

1,040,667

2,140,667

2,140,000

1922,500

1,217,500

3,358,167

1,149,884

115,000

1,264,884

403,900

1,668,784

104,000

1,236,000

349,383

1,689,383

3,358,167

2026

60,000

40,000

1,100,000

1,214,111

2,414,111

2,140,000

(1,136,500)

1,003,500

3,417,611

1,296,458

130,000

1,426,458

303,900

1,730,358

104,000

1,236,000

347,254

1,687,254

3,417.611

2027

60,000

40,000

1,200,000

1,387,556

2,687,556

2,140,000

(1,350,500)

789,500

3,477,056

1,443,031

145.000

1,588,031

203,900

1,791,931

104.000

1,236,000

345,124

1,685,124

3,477,056

Based upon the figures below and the financial forecast for the years 2023 - 2027, compute la through 1d below, placing your final results in the yellow highlighted area.

HINT: All formulas needed to solve 1a - 1d come from your Week 4 Lesson and Week 4 Chapter readings.

GIVEN:

Quantity of windows sold (Q)

Selling price per window (P)

Variable Costs per window (VC)

Total Fixed Costs (FC)

Total Variable Costs (TVC)

SOLVE FOR:

2023

2024

2025

2026

2027

Contribution Margin Ratio Breakeven in sales dollars

DOL

DFL

3 pts

3 pts

4 pts

4 pts

2

(14 pts) Based upon your financial forecast for the years 2023 - 2027, compute 2a through 2d below FROM THE VIEWPOINT OF THE SHARK,

2023	2024|	2025	2026	2027
4,000	5,000	6,000	7,000	8,000
$1,000	$1,000	$1,000	$1,000	$1,000
$500	$600	$583	$571	$625
$500,000	$500,000	$500,000	$500,000	$500,000
$2,000,000	$3,000,000	$3,500,000	$4,000,000	$5,000,000

2023

2024

2025

2026

2027

14pts) Based upon your financial forecast for the years 2023 - 2027, compute 2a through 2d below FROM THE VIEWPOINT OF THE SHARK, placing your final results in the yellow highlighted area.

(HINT: See Week 6 Lesson for 2b - 2d. No hint for 2a!)

GIVEN:

NOTE 1: The shark's investment of $1,000,000 gave him 25% ownership of your company and its profits.

NOTE 2: The shark requires a 9% Rate of Return on his investment.

2023

2024

2025

2026

2027

NOTE 3:

Cash Flows from

Operations (Total for company)

($1,000,000)

$372,212

$843,095

$1,066,542

$1,330,902

$1,554,349

2023

2024

2025

2026

2027

b

d

SOLVE FOR:

Cash Flows from

Operations (shark only)

Payback (in years) of the shark's $1M investment (xxx years)

IRR of shark's investment

NPV of shark's investment

3 pts

3 pts

4 pts

4 pts

pts) What is the growth rate of Net Income for each of the forecasted years?

(HINT: See Week 2 Lesson, horizontal analysis)

2023

2024

2025

2026

2027

(4 pts) Compute the following for the forecasted years. (Total company)

(HINT: See Week 1 Chapter readings for FCF formula)

GIVEN:

NOTE 1: Dividends are zero every year.

NOTE 2: $750,000 of the $1M invested by the shark was spent on Capital Expenditures in 2023. No other capital expeditures occurred.

SOLVE FOR:

Free Cash Flow

Free Cash Flow per share

2023

2024

2025

2026

2027