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
>
>
n
rio & Requirements
RIO & REQUIREMENTS
Milestones)
ura-Clear windows” that never need washin
! Nothing sticks to them – not pollution, pollen, dirt, dust,
ird droppings,
ingerprints, 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.
NOTE
: 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.
ESTONE 1: Sales Forecast (
0 points)
23
, and you’ll be going on Shark Tank in 2 weeks. Using the information below, create your
-year Sales Forecast for the years
–
.
1
75,000
2
1
(20 pts) Conduct a 2-year weighted annual sales forecast for the years 2023-
.
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.
0 points)
Required:
1
(2
) Using the information on the
Assumptions
tab, create a 5-year forecasted Income Statement on the Forecasted Financials tab.
2
3
(20 pts) On the
Milestone 2 Metrics
tab, compute various metrics based upon your forecasted financials.
4
(25 pts) Answer questions about your results on the
Milestone 2 Questions
tab.
0 points)
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:
in sales dollars
and
Flows and
2
(20 pts) Answer questions about your results on the
Milestone 3 Questions
tab.
Milestone 1 Sales Forecast
| 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
| 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
| ASSUMPTIONS | ||
| NOTE: You will use a combination of the Proforma and the Percent-of-Sales methods to create your forecasted financials. | ||
| The Sharks gave you the $1,000,000 in funding you asked for in exchange for 25% ownership of your company’s profits. You issued 26,000 $1-par shares to the Sharks and updated your additional paid-in capital by the excess received. The sharks have a | 9 | |
| All the following are complete by March 31, 2023: 1) your shark funding has been received, 2) new capital investments have been purchased and set up, and 3) additional labor has been hired and trained. | ||
| SALES: a. All Sales are credit sales. b. In 2023, your expanded operations enable Production and Sales to double your 2022 sales (due to selling more windows to the commercial builders and increasing the sale price per window). c. Quantity of windows sold is expected to increase 25% every year from 2024 on. d. Due to inflation and demand, at the start of 2024 you increase your selling price per window by 9%. |
||
| COGS: Compute your 2022 COGS as a Percent of Sales ratio, and then subtract 10% because you’ll be able to get volume discounts for your materials now that you’ve expanded. Use this adjusted ratio to forecast COGS across all years. | ||
| SELLING & ADMINISTRATIVE EXPENSE: S&A Expense is a “mixed” expense, so fixed expenses must be removed before forecasting S&A, and then added back once that’s done. Use a 3-year average Percent of Sales to forecast variable S&A expenses. (HINT: Recall that since S&A is an expense, and therefore negative, to remove fixed expenses, you must ADD them!) All S&A expenses are variable expenses EXCEPT for the following 2 fixed costs: 1. Rent expense of $ | 15 | |
| INTEREST EXPENSE: This is a Fixed cost, and is 10% of | Long-term Liabilities | |
| TAXES: Because you live in a business-friendly State (Wyoming), you don’t have to pay state taxes on your LLC’s income. You do, however, still have to pay Federal taxes. Also, in 2022, higher tax rates were passed for the 2023 tax year, pushing income over $ | 400,000 | |
| SHARES: Issued 72,000 $1-par shares to the sharks for a 48% ownership stake. | ||
| CASH: Increases to $50,000 in 2023 and stays at that level. | ||
| MARKETABLE SECURI | TIE | Marketable Securities |
| 11 | ACCOUNTS RECEIVABLE: Use a 3-year average Receivables Turnover ratio to forecast. | |
| 12 | INVENTORY: Compute a 3-year average of inventory as a percent of sales, and then use that figure to forecast inventory levels through 2027. | |
| 13 | PLANT & EQUIPMENT: New capital expenditure of $750,000 dollars in 2023, paid for with equity funding from the sharks, rather than new debt. All capital expenditures are assumed to occur on January 1st of the year of purchase, and no equipment is sold or salvaged during the forecasted period. | |
| 14 | ACCUMULATED DEPRECIATION: Each year, 10% of the total amount of Plant and Equipment is added to the depreciation amount. | |
| ACCOUNTS PAYABLE: Use the 3-year average Current Ratio to forecast. | ||
| 16 | ACCRUED EXPENSES: Use the 3-year average Percent of Sales method to forecast. | |
| 17 | LONG-TERM LIABILITIES: Pay down $100,000 of old debt every year starting in 2023. | |
| 18 | COMMON STOCK ($1 Par): Increase by the dollar value of shares issued to sharks (26,000 shares at $1 par). | |
| 19 | CAPITAL PAID IN EXCESS OF PAR: Compute new figure using shark investment, less par value of common stock. | |
| RETAINED EARNINGS: Fill in the amount needed to balance the Balance Sheet. | ||
| DIVIDENDS: You do not pay dividends now and do not plan to while in a growth stage. | ||
| FIXED & VARIABLE COSTS: The only fixed costs are rent expense, depreciation expense, and interest expense. All other costs are variable. |
Forecasted Financials
| 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
| 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
| MILESTONE 2 QUESTIONS | |
| (5 pts) Is it reasonable to assume that inventory levels will remain the same percent of sales in your forecast | |
| as when your business was smaller? Explain. What actions have you taken that should decrease the percent | |
| of inventory in your forecast? | |
| (5 pts) You asked the sharks for $1M. Is it enough? | |
| If it is not enough, or if you simply would like access to additional funds, what existing source(s) of funds could you use instead? | |
| (HINT: Look at your financials.) | |
| Ratio analysis | (5 pts) What trends do you see looking at your profitability ratios? What is causing this trend? |
| Are the sharks likely to challenge these figures? | |
| (5 pts) What trends do you see looking at your debt utilization ratios? What is causing this trend? | |
| (5 pts) How do you explain the difference between your liquidity ratios and the industry standard ratios? |
Milestone 3 Metrics
| 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
| 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.