Posted: February 26th, 2023

# Budgeting and Forecasting

If you look at the bottom of the excel document, you will see tabs. I need assistance with Milestones 3 Metrics and Questions. Information can be found within the other tabs.

<h<t<t<t

d

>d

>d

>

2

>

S c e

n

a

rio & Requirements SCEN

A

RIO & REQUIREMENTS (Scroll to see all

3

Milestones) SCENARIO: You invented “

D

ura-Clear windows” that never need washin

g

! Nothing sticks to them – not pollution, pollen, dirt, dust,

b

ird droppings,

f

NOTE

1

: You must show all your work. Either your computations are in the cell behind your result, or you must place them out to the right on the

## Forecasted Financials

tab. If you choose to show your work out to the right, show all steps and label your work clearly so it can be understood.

NOTE 2: Do not round computations until you have found your final answer. Then, round your result to the nearest dollar. No pennies! MI

L

ESTONE 1: Sales Forecast (

4

0 points) DUE: WEEK 3 It’s currently January 1,

20

23

, and you’ll be going on Shark Tank in 2 weeks. Using the information below, create your

5

-year Sales Forecast for the years

2023

202

7

. Information:

1

Actual annual sales in the past: 2020 20

21 20

22 Actual SALES \$

1,

200,000 \$

1,500,000 \$

1,

8

75,000

2

Because you feel your most recent sales are the best predictor of future sales, you weight your sales as follows: W1 = 0.2 and W2 = 0.8 Required:

1
(20 pts) Conduct a 2-year weighted annual sales forecast for the years 2023-

2027

.

P

lace your results on the Sales line of the

## Milestone 1 Sales Forecast

tab in the highlighted area.

2
(20 pts) Answer the questions on the

## Milestone 1 Questions

tab.

MILESTONE 2: Creation of Forecasted Financials (

10

0 points) DUE: WEEK 5 It’s STILL January 1, 2023, and you’ll be going on Shark Tank in 2 weeks. You are unhappy with your previous Sales Forecast because your current sales to residences do not really predict your upcoming sales to commercial builders. You believe your incoming orders will be much higher than before with each order resulting in a much higher revenue stream. You decide to change your method of forecasting to a mix of Percent-of-Sales and Pro Forma forecasting.

Required:

1
(2

5 pts

) Using the information on the

## Assumptions

tab, create a 5-year forecasted Income Statement on the Forecasted Financials tab.

2

(30 pts) Using the information on the Assumptions tab, create a 5-year forecasted Balance Sheet on the Forecasted Financials tab.

3
(20 pts) On the

## Milestone 2 Metrics

tab, compute various metrics based upon your forecasted financials.

4

## Milestone 2 Questions

tab.

MILESTONE 3: Analysis of Forecasted Financials (

6

0 points) DUE: WEEK 7 You’ve created forecasted financials assuming the sharks will provide the funding you’ll request for expansion. Now you need to determine the profitability of your proposal.

Required:

1
(40 pts) On the

## Milestone 3 Metrics

tab, use your forecasted financials from Milestone 2 to compute all of the following for each forecasted year:

Fixed Costs and Variable Costs Contribution Margin ratio Breakeven

in sales dollars DOL

and

DFL Growth rate of earnings Operating

Cash

Flows and

Free

Cash Flow Year (and partial year) of Payback for your shark investor IRR and NPV for your shark investor

2

## Milestone 3 Questions

tab.

Milestone 1 Sales Forecast

2020

2023

2027

1,200,000
1,500,000

before

Taxes
Net Income
 MILESTONE 1 SALES FORECAST Fill in the yellow highlighted cells with your forecasted figures. SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, out to the right, or both — clearly labeling your work. All your work must be shown on this sheet, not on a separate tab. DURA-CLEAR WINDOWS, LLC Proforma Income Statement 2021 2022 2024 2025 2026 Sales (all on credit) 1,875,000 Cost of Goods Sold Gross Profit Selling and Administrative Expense Operating profit (EBIT) Interest expense Net Income Taxes Shares Earnings per Share

Milestone 1 Questions

1

2

 MILESTONE 1 QUESTIONS (10 points) How reliable is your Sales Forecast? Explain your answer. (10 points) What are some ways you could potentially improve the accuracy of your forecast?

Assumptions

1

% Required Rate of Return on their investment (needed for Milestone 3).

2

3

4

5

,000 per year in 2020-2022, increasing to \$200,000 per year in 2023-2027. 2. Depreciation expense is included in S&A in the amount of 10% of Plant & Equipment each year.

6

.

7

into the 39.6% tax bracket. Because of this, use 36% as your effective tax rate. (NOTE: If the taxes shown for 2020-2022 seem high, it’s because you had income from another job that threw your LLC income into a slightly higher tax bracket. However, you’ll quit that job IF the sharks fund you!)

8

9

10

S: Plan to keep

at 60% of Cash levels.

15

20

21

22

Forecasted Financials

Fill in the yellow highlighted cells with your forecasted figures.

SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, out to the right, or both — clearly labeling your work.

All your work must be shown on this sheet, not on a separate tab.

DURA-CLEAR WINDOWS, LLC

Proforma Income Statement

2020
2021
2022
2023
2024
2025
2026
2027

Sales (all on credit)

1,200,000
1,500,000
1,875,000

Cost of Goods Sold

)

Gross Profit

400,000

Operating profit (EBIT)

Interest expense

)

before Taxes

Taxes

Net Income

Shares

60,000
60,000

Earnings per Share

DURA-CLEAR WINDOWS, LLC

2020
2021
2022
2023
2024
2025
2026
2027

Cash

40,000
30,000

Marketable Securities

30,000

585,000

200,000

30,000
35,000

Long-term Liabilities

60,000
60,000
78,000

190,000

1,035,000
1,208,500
1,819,500

 MILESTONE 2 FORECASTED FINANCIALS Forecasted Income Statement (25 points) (800,000) (1,0 40,000 (1,105,000) 4 60,000 770,000 (304,900) (350,500) (443,700) 95,100 109,500 326,300 ( 35,000 (45,000) (85,000) Net Income 60,100 64,500 241,300 (36,900) (49,200) (55,600) 23,200 15,300 185,700 78,000 \$0.39 \$0.26 \$2.38 Forecasted Balance Sheet (30 points) Proforma Balance Sheet ASSETS 30,000 20,000 25,000 Accounts Receivable 170,000 259,000 360,000 Inventory 230,000 261,000 290,000 Total Current Assets: 450,000 585,000 710,000 Plant and equipment 650,000 765,000 1,390,000 Less: accumulated depreciation (65,000) (141,500) (280,500) Net Plant and equipment 623,500 1,109,500 Total Assets 1,035,000 1,208,500 1,819,500 LIABILITIES & STOCKHOLDER’S EQUITY Accounts Payable 310,000 505,000 Accrued Expenses 20,400 Total Current Liabilities 220,400 340,000 540,000 325,000 363,600 703,900 Total Liabilities 545,400 703,600 1,243,900 Common Stock (\$1 par) Capital paid in excess of par 190,000 262,000 Retained Earnings 239,600 254,900 235,600 Total Stockholder’s Equity 489,600 504,900 575,600 Total Liabilities & Stockholder’s Equity

Milestone 2 Metrics

All your work must be shown on this sheet, not on a separate tab.

1

2023
2024
2025
2026
2027

a

b

c

d

e

f

g
TIE

2

) for each year

2022
2023
2024
2025
2026

A

S

L

P

D

 MILESTONE 2 METRICS SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, or to the side or below — clearly labeling your work. (10 points) Based upon your financial forecast for the years 2023 – 2027, compute the following ratios, placing your final results in the yellow highlighted area: Industry Averages Profit Margin 12.20% ROA 8.75% ROE 22.42% Current ratio 2.33X Quick ratio 1.45X Debt-to-Total Assets 43.05% 10.28X (10 points) Compute Required New Funds ( RNF HINT: It may be helpful to fill out the table below identifying the necessary variables before attempting to compute RNF. change in S S2

Milestone 2 Questions

1
Inventory

2
RNF

3

Milestone 3 Metrics

SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, out to the right, or both — clearly labeling your work.

All your work must be shown on this sheet, not on a separate tab.

1

2023
2024
2025
2026
2027

a

b

2 pts

c

d

3 pts

e
DOL
3 pts

f
DFL
3 pts

2

2023
2024
2025
2026
2027

Net Income

a

3 pts

b

3 pts
(\$1,000,000)

c

3 pts

d

e

4 pts

3

2023
2024
2025
2026
2027

4

2023
2024
2025
2026
2027

3 pts
Free Cash Flow

 MILESTONE 3 METRICS (16 pts) Based upon your financial forecast for the years 2023 – 2027, compute the following, placing your final results in the yellow highlighted area. HINT: Taxes should NOT be included in your Fixed or Variable costs for this computation. Total Fixed Costs 2 pts Total Variable Costs Contribution Margin ratio (See Week 5 Lesson) 3 pts Breakeven in sales dollars (See Week 5 Lesson) (17 pts) Based upon your financial forecast for the years 2023 – 2027, compute a – e below FROM THE VIEWPOINT OF THE SHARK, placing your final results in the yellow highlighted area. (HINT: Remember the shark has only 48% ownership!) Change in Current Assets Change in Current Liabilities Depreciation Expense Cash Flows from Operations (Total for company) (\$1,000,000) Cash Flows from Operations (shark only) Payback (in years) of the shark’s \$1M investment (x.xx years) IRR of shark’s investment 4 pts NPV of shark’s investment (3 pts) What is the growth rate of earnings for each of the forecasted years? (4 pts) Compute the following for the forecasted years. (Total company) 1 pt Free Cash Flow per share

Milestone 3 Questions

1

5 pts

2
Breakeven

5 pts

3

5 pts

4
Cash Flow

5 pts

 MILESTONE 3 QUESTIONS DOL & DFL Is your DOL increasing or decreasing? What caused this, and what does it mean for your company? What about your DFL? Does your breakeven point (in terms of sales dollars) increase, decrease, or stay fairly level? Why do you think this is? Financial Analysis Based upon the Payback, IRR, and NPV results, is this a wise investment for the shark? Explain what your results mean. Finally, discuss whether you think your forecast is realistic, and explain why you think the way you do. Looking at your FCF results, do you think you should have gone to the Sharks for money? If so, why? If not, why not?

### Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

## Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
\$0.00