HELP PLEASE COMPLEX EXCEL QUESTION

MUST SHOW WORK FOR EACH CELL

HOW 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. All your work must be shown on this sheet, not on a separate tab. 1 15 points: 5 for showing work, 10 for occuracy 22215 points: 5 for showing work, 10 for occuracy 24 Compute Required New Funds (RNF) for each year CiuNT: See Formula 4:1 in your Week 3 Chopter readings) OPNONAL: It would be very helpful to fill out the table below identifying the necessary variables before attempting to compute RNF. 33 \begin{tabular}{|c|c|c|c|c|c|} \hline & 2023 & 2024 & 2025 & 2026 & 2027 \\ \hline A/S - Total Current Assets/Sales & & & & & \\ \hline AS = Current Sales - Last vear's Sales & & & & & \\ \hline L/S - Total Current Liabilities/Sales & & & & & \\ \hline - Profit Margin (sce 1a abovel) & & & & & \\ \hline 5) - Sales in the current yt & & & & & \\ \hline D - Dividend Payout Ratio - Dividends & & & & & \\ \hline \end{tabular} Compute Required New Funds (RNF) for each year OPNONAL: It would be very helpfol to fill out the table below identifying the necessary variables before attempting to compute REQUIREMENTS: 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 (Milestone 1) because using your current level of sales to residences was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and that each order will result in a higher profit. You decide to change your forecasting method to a combination of Ratios, Percent-ofSales, and Pro Forma forecasting. Required: 1 (20 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. 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 (Milestone 1) because using your current level of sales to residences was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and that each order will result in a higher profit. You decide to change your forecasting method to a combination of Ratios, Percent-ofSales, and Pro Forma forecasting. Required: 1 (20 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 (30 pts) On the Milestone 2 Metrics tab, compute various metrics based upon your forecasted financials. 4 (20 pts) Answer questions about your results on the Milestone 2 Questions tab. Forecasted Income Statement (20 points: 5 for showing work, 15 for accuracy) 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 8 9 SHARES: Issued 26,000\$1-par shares to the sharks for a 25% ownership stake. 10 CASH: Increases to $50,000 in 2023 and stays at that level. 11 MARKETABLE SECURITIES: Plan to keep Marketable Securities at 60% of Cash levels. 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, along with your other known number from your financial statements, to find your forecasted Accounts Receivoble amounts. This is demonstrated in your Week 2 Lessonl) 12 INVENTORY: Compute a 3-vear average of inventory as a Percent-of-Sales, and then use that figure to forecast inventory levels through 2027. 13 PLANT \& EQUIPMENT: There is a new capital expenditure of $750,000 dollars in 2023, paid for from the $1M in funding from the sharks, rather than with new debt. (All copital expenditures are assumed to occur on January 1 st of the year of purchase, and no equipment is sold or salvaged during the forecasted were accepted to the showl 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 applyl 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! 13 PLANT \& EQUIPMENT: There is a new capital expenditure of $750,000 dollars in 2023 , paid for from the $1M in funding from the sharks, rather than with 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 14 ACCUMULATED DEPRECIATION: Each year, 10% of the total amount of Plant and Equipment is added to the depreciation amount. 15 ACCOUNTS PAYABLE: Use the 3-year average Current Ratio to forecast. (HINT: The Current Ratio will help you forecast TOTAL Current Liabilities, not Accounts Payable. Find Total Current Liabilities and Accrued Expenses first, and then you can solve for Accounts Payable.) 16 ACCRUED EXPENSES: Use the 3-year average Percent-of-Sales method to forecast. 17 18 19 par). CAPITAL PAID IN EXCESS OF PAR: The shark's full investment of $1M must be reflected on the Balance Sheet. The stock received by the shark, worth $26,000 (at Par value of $1 per share), is already reflected under Common Stock. The rest of the shark's investment value is added to this account. 20 RETAINED EARNINGS: This amount is whatever it takes to make the Balance Sheet balancel (HINT: Remember Total Liabilities + Stockholder's Equity is equal to Total Assets. Once Total L + SE is determined, Total SE can be solved. Finally, once Total SE is solved, Retained Earnings can be solved.) 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 showl 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. 3 4 NOTE: You will use a combination of Proformo, Ratios, and the Percent-of-Sales methods to create your forecasted financials. \begin{tabular}{|c|c|} \hline 1 & TheSharksgaveyouthe$1,000,000infundingyourequestedinexchangefor25%ownershipofyourcompanysprofits. \\ \hline 2 & AllthefollowingarecompletebyMarch31,2023:1)yoursharkfundinghasbeenreceived,2)newcapitalinvestmentshavebeenpurchasedandsetup,and3)additionallaborhasbeenhiredand \\ \hline 3 & SELUNG&ADMINISTRATIVEEXPENSE:Usea3-yearaveragePercent-of-SalestoforecastS&Aexpenses.(HINT:FindwhatpercentS&Aexpenseisforeachof2020,2021,and2022,andaverogethe3resultstogether.UsetheresultingoverageS&APercent-of-5olestoforecastS&Aintothefuture.) \\ \hline 4 & RENTEXPENSE:Rentexpenseisafixedcostintheamountof$15,000peryearin20202022,increasingto$200,000peryearin2023. \\ \hline 5 & DEPRECIATIONEXPENSE:Depreciationexpenseisafixedcostintheamountof10%ofPlant&Equipmenteachyear. \\ \hline 6 & INTERESTEXPENSE:ThisisaFiredcost,andis10%ofLong-termLiabilities.TAXES:Because \\ \hline 7 & TAXES:Becauseyouliveinabusiness-friendlyState(Wyoming),youdonthavetopaystatetaxesonyourLCsincome.Youdo,however,stillhavetopayfederaltaxes.Also,in2022,highertaxrateswerepassedforthe2023taxyeaf,pushingincomeover$400,000intothe39.6%taxbracket.Becausenfthis \\ \hline \end{tabular} HOW 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. All your work must be shown on this sheet, not on a separate tab. 1 15 points: 5 for showing work, 10 for occuracy 22215 points: 5 for showing work, 10 for occuracy 24 Compute Required New Funds (RNF) for each year CiuNT: See Formula 4:1 in your Week 3 Chopter readings) OPNONAL: It would be very helpful to fill out the table below identifying the necessary variables before attempting to compute RNF. 33 \begin{tabular}{|c|c|c|c|c|c|} \hline & 2023 & 2024 & 2025 & 2026 & 2027 \\ \hline A/S - Total Current Assets/Sales & & & & & \\ \hline AS = Current Sales - Last vear's Sales & & & & & \\ \hline L/S - Total Current Liabilities/Sales & & & & & \\ \hline - Profit Margin (sce 1a abovel) & & & & & \\ \hline 5) - Sales in the current yt & & & & & \\ \hline D - Dividend Payout Ratio - Dividends & & & & & \\ \hline \end{tabular} Compute Required New Funds (RNF) for each year OPNONAL: It would be very helpfol to fill out the table below identifying the necessary variables before attempting to compute REQUIREMENTS: 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 (Milestone 1) because using your current level of sales to residences was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and that each order will result in a higher profit. You decide to change your forecasting method to a combination of Ratios, Percent-ofSales, and Pro Forma forecasting. Required: 1 (20 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. 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 (Milestone 1) because using your current level of sales to residences was not a good predictor of your upcoming sales to commercial builders. You believe your sales will increase significantly and that each order will result in a higher profit. You decide to change your forecasting method to a combination of Ratios, Percent-ofSales, and Pro Forma forecasting. Required: 1 (20 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 (30 pts) On the Milestone 2 Metrics tab, compute various metrics based upon your forecasted financials. 4 (20 pts) Answer questions about your results on the Milestone 2 Questions tab. Forecasted Income Statement (20 points: 5 for showing work, 15 for accuracy) 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 8 9 SHARES: Issued 26,000\$1-par shares to the sharks for a 25% ownership stake. 10 CASH: Increases to $50,000 in 2023 and stays at that level. 11 MARKETABLE SECURITIES: Plan to keep Marketable Securities at 60% of Cash levels. 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, along with your other known number from your financial statements, to find your forecasted Accounts Receivoble amounts. This is demonstrated in your Week 2 Lessonl) 12 INVENTORY: Compute a 3-vear average of inventory as a Percent-of-Sales, and then use that figure to forecast inventory levels through 2027. 13 PLANT \& EQUIPMENT: There is a new capital expenditure of $750,000 dollars in 2023, paid for from the $1M in funding from the sharks, rather than with new debt. (All copital expenditures are assumed to occur on January 1 st of the year of purchase, and no equipment is sold or salvaged during the forecasted were accepted to the showl 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 applyl 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! 13 PLANT \& EQUIPMENT: There is a new capital expenditure of $750,000 dollars in 2023 , paid for from the $1M in funding from the sharks, rather than with 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 14 ACCUMULATED DEPRECIATION: Each year, 10% of the total amount of Plant and Equipment is added to the depreciation amount. 15 ACCOUNTS PAYABLE: Use the 3-year average Current Ratio to forecast. (HINT: The Current Ratio will help you forecast TOTAL Current Liabilities, not Accounts Payable. Find Total Current Liabilities and Accrued Expenses first, and then you can solve for Accounts Payable.) 16 ACCRUED EXPENSES: Use the 3-year average Percent-of-Sales method to forecast. 17 18 19 par). CAPITAL PAID IN EXCESS OF PAR: The shark's full investment of $1M must be reflected on the Balance Sheet. The stock received by the shark, worth $26,000 (at Par value of $1 per share), is already reflected under Common Stock. The rest of the shark's investment value is added to this account. 20 RETAINED EARNINGS: This amount is whatever it takes to make the Balance Sheet balancel (HINT: Remember Total Liabilities + Stockholder's Equity is equal to Total Assets. Once Total L + SE is determined, Total SE can be solved. Finally, once Total SE is solved, Retained Earnings can be solved.) 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 showl 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. 3 4 NOTE: You will use a combination of Proformo, Ratios, and the Percent-of-Sales methods to create your forecasted financials. \begin{tabular}{|c|c|} \hline 1 & TheSharksgaveyouthe$1,000,000infundingyourequestedinexchangefor25%ownershipofyourcompanysprofits. \\ \hline 2 & AllthefollowingarecompletebyMarch31,2023:1)yoursharkfundinghasbeenreceived,2)newcapitalinvestmentshavebeenpurchasedandsetup,and3)additionallaborhasbeenhiredand \\ \hline 3 & SELUNG&ADMINISTRATIVEEXPENSE:Usea3-yearaveragePercent-of-SalestoforecastS&Aexpenses.(HINT:FindwhatpercentS&Aexpenseisforeachof2020,2021,and2022,andaverogethe3resultstogether.UsetheresultingoverageS&APercent-of-5olestoforecastS&Aintothefuture.) \\ \hline 4 & RENTEXPENSE:Rentexpenseisafixedcostintheamountof$15,000peryearin20202022,increasingto$200,000peryearin2023. \\ \hline 5 & DEPRECIATIONEXPENSE:Depreciationexpenseisafixedcostintheamountof10%ofPlant&Equipmenteachyear. \\ \hline 6 & INTERESTEXPENSE:ThisisaFiredcost,andis10%ofLong-termLiabilities.TAXES:Because \\ \hline 7 & TAXES:Becauseyouliveinabusiness-friendlyState(Wyoming),youdonthavetopaystatetaxesonyourLCsincome.Youdo,however,stillhavetopayfederaltaxes.Also,in2022,highertaxrateswerepassedforthe2023taxyeaf,pushingincomeover$400,000intothe39.6%taxbracket.Becausenfthis \\ \hline \end{tabular}