Budgeting and Forecasting|Course hero helper

Posted: February 13th, 2023

Scenario & Requirements

SCENARIO & REQUIREMENTS
(Scroll to see all 3 Milestones)
SCENARIO:
You invented “Dura-Clear windows” that never need washing! Nothing sticks to them – not 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 following 2 Notes apply to all 3 Milestones!
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!
MILESTONE 1: Sales Forecast (40 points)
DUE: WEEK 3
It’s currently January 1, 2023, 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-2027.
Information:
1 Actual annual sales in the past:
2020 2021 2022
Actual SALES $1,200,000 $1,500,000 $1,875,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. Place 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 (100 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 (25 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 (25 pts) Answer questions about your results on the Milestone 2 Questions tab.
MILESTONE 3: Analysis of Forecasted Financials (60 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 (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
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
Selling and Administrative Expense
Operating profit (EBIT)
Interest expense
Net Income before Taxes
Taxes
Net Income
Shares
Earnings per Share

Milestone 1 Questions

MILESTONE 1 QUESTIONS
1 (10 points) How reliable is your Sales Forecast? Explain your answer.
2 (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.
1 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% Required Rate of Return on their investment (needed for Milestone 3).
2 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.
3 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%.
4 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.
5 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,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 INTEREST EXPENSE: This is a Fixed cost, and is 10% of Long-term Liabilities.
7 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 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!)

SOLUTION

Budgeting and forecasting are two important financial management techniques that businesses use to plan and control their financial activities.

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Budgeting is the process of creating a plan for spending and saving money. This plan includes estimated revenue and expenses for a specific period of time, usually a year. The budget helps a company determine its financial goals, allocate resources, and monitor its performance. Budgeting also enables a company to identify areas where it may need to reduce expenses or increase revenue in order to reach its financial goals.

Forecasting, on the other hand, is the process of making predictions about future events, such as future sales, expenses, and cash flows. Forecasting allows a company to prepare for future financial challenges and opportunities, and to make informed decisions about investments and expenditures. Forecasting can be done using a variety of methods, including historical analysis, market research, and economic forecasting.

Together, budgeting and forecasting are important tools for financial planning and management. They help companies to plan for the future, make informed decisions, and achieve their financial goals.

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