Course Project Milestone $2$

Scenario:

You invented $$Dura$-$Clear windows$$ that never need washing! Nothing sticks to them $$ not pollution, pollen, dirt, dust, bird droppings, fingerprints. You$$ve invested all your savings, your parents$$ savings, and some of your friends$$ savings 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 nationwide have been requesting your windows. Unfortunately, you$$re already operating at full capacity. It's time to 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 profitable. You also need to determine how much funding to ask for in exchange for how much ownership you'll give up$.$

Requirements:

It$$s STILL January $1,2023,$ and you$$ll go on Shark Tank in $2$ weeks. You are unhappy with your previous Sales Forecast $($Milestone $1)$ because using your current level of sales to residents was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and each order will increase profit. You change your forecasting method to ratios, Percentage$-$of$-$sales, and pro forma forecasting.

Required:

$1.$ Using the information on the Assumptions tab, create a $5-$year forecasted Income Statement on the Forecasted Financials tab.

$2.$ Using the information on the Assumptions tab, create a $5-$year forecast Balance Sheet on the Forecasted Financials tab.

$3.$ On the Milestone $2$ Metrics tab, compute various metrics based on your forecasted financials.

$4.$ Answer questions about your results on the Milestone $2$ Questions tab.

ASSUMPTIONS

NOTE: You will use a combination of Proforma, Ratios, and Percent$-$of$-$Sales methods to create your forecasted financials.

$1.$ The Sharks gave you the $$1,000,000$ in funding you requested in exchange for $25\%$ ownership of your company$$s profits.

$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.$ SELLING & ADMINISTRATIVE EXPENSE: Use a $3-$year average Percent$-$of$-$Sales to forecast S&A expenses. $($HINT: Find what percent S&A expense is for each of $2020,2021,$ and $2022,$ and average the three results together. Use the resulting average S&A Percent$-$of$-$Sales to forecast S&A into the future.$)$

$4.$ RENT EXPENSE: Rent expense is a fixed cost in the amount of $$15,000$ per year in $2020-2022,$ increasing to $$200,000$ per year in $2023.$

$5.$ DEPRECIATION EXPENSE: Depreciation expense is a fixed cost of $10\%$ of Plant and 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 $2022,$ higher tax rates were passed for the $2023$ tax year, pushing income over $$400,000$ into the $39\mathrm{.}6\%$ tax bracket. Because of this, use $36\%$ as your effective tax rate. $($NOTE: If the taxes 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.$ SHARES: Issued $26,000$ $$1-$par shares to the sharks for a $25\%$ ownership stake.

$9.$ CASH: Increases to $$50,000$ in $2023$ and stays at that level.

$10.$ MARKETABLE SECURITIES: Plan to keep Marketable Securities at $60\%$ of Cash levels.

$11.$ ACCOUNTS RECEIVABLE: Use a $3-$year average Receivables Turnover ratio to forecast. $($HINT: Find the formula for Receivables Turnover $($RTO$)$ in your Week $2$ Chapter readings, and solve for RTO for each of $2020,2021,$ and $2022.$ Average the $3$ results together. Plug your Average RTO into the RTO formula for each future year and your other known number from your financial statements to find your forecasted Accounts Receivable amounts. This is demonstrated in your Week $2$ Lesson!$)$

$12.$ INVENTORY: Compute a $3-$year inventory average as a percentage of sales and then use that figure to forecast inventory levels through $2027.$

$13.$ PLANT & EQUIPMENT: