A new client of yours, Katie Perez wants to open an ice cream shop near the downtown shoppingcenter. She will beselling Frozen POPS, Frozen Ices, Frozen POPS Packs, Soda and BottledWater. Her grand opening will be March $1,2024.$Katie needs help figuring out how her business will perform the first year in business. She hashired you as an independent consultant, prepare a cash proforma budget for her businessventure. This is a spreadsheet that helps forecast income and expenses over a period of time$).$It can be used to plan and manage the business if done correctly.Assignment:Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year ofoperations.Use the file attached Excel Template $-$Perezpops.xls as your starting point. Download and usethis file as the basis for your assignment. Do not make any changes to this pre$-$definedtemplate items $($this means start with the items that already included and that areexpected in the spreadsheet$).$ You may add your information to the existing sheets. Youmay add extra worksheets as needed as well update the template.Do not use a template from a previous semester this is academic dishonesty and will besubject to disciplinary action. Do not remove the Grading Criteria Worksheet. Place the finished cash pro forma on a worksheet labeled Cash ProForma". Calculations and functions must be used to determine values as necessary $($i$.$e$.$payment, taxes, cogs, sales, etc. Place all your case assumptions data on a separate worksheet. Label theworksheet "Assumptions" $($note: each piece of data must appear in its own cell onthe Assumption sheet$).$ Place your start$-$up costs on a third worksheet labeled "Startup Costs" Create additional worksheets for your What if Scenario recommendation. Events Appropriate Charts $($graphs$)$: You will be creating two separate charts so create andlabel two additional worksheets for the charts $($each chart will be in its ownworksheet$)\mathrm{.}1$ Chart One Monthly Product Revenue this will show the monthly revenue foreach of your five products for the entire year. Charts should be on their ownworksheet. Chart Two Total Product Net Income $-$ You want to track the total product netincome for the year to determine any trends or projections in product sales. Chartsshould be on their own worksheet. Make sure both charts are formatted correctly $($i$.$e$.$ appropriate title, legend whereappropriate, data series properly labeled$)$ and they are appropriate for businessuse.Information needed to complete assignment:Products: Specialty POPS, Frozen Ice, Soda and Bottle water.Product Selling Prices:POPSFruit Explosion $$4\mathrm{.}85$Vanilla Vanilla $$3\mathrm{.}85$Chocolate Mousse $$4\mathrm{.}35$Mango Tangerine Surprise $$4\mathrm{.}35$Strawberry Cr$$me $$3\mathrm{.}75$Pina Colada $$4\mathrm{.}85$Strawberry Kiwi $$4\mathrm{.}35$Orange Cr$$me $$3\mathrm{.}75$Chocolate sea salt $$4\mathrm{.}35$Cookies & Cr$$me $$3\mathrm{.}25$Pop Packs$12$pk $$28\mathrm{.}006$pk $$12\mathrm{.}00$Frozen IceMedium $$3\mathrm{.}50$Large $$4\mathrm{.}25$Soda $-$$$1\mathrm{.}95$ a bottleBottled Water $-$ $$1\mathrm{.}25$ a bottleCost of Goods Sold:POPSFruit Explosion $$0\mathrm{.}90$VanillaVanilla $$0\mathrm{.}40$Chocolate Mousse $$0\mathrm{.}95$Mango Tangerine $$0\mathrm{.}70$Strawberry Cr$$me $$0\mathrm{.}70$Pina Colada $$0\mathrm{.}90$Strawberry Kiwi $$0\mathrm{.}80$Orange Cr$$me $$0\mathrm{.}501$Chocolate sea salt $$0\mathrm{.}95$Cookies & Cr$$me $$0\mathrm{.}70$Pop Packs$12$pk $$7\mathrm{.}206$pk $ $4\mathrm{.}20$Frozen IceMedium $ $0\mathrm{.}95$Large $$1\mathrm{.}10$Sodas cost about $$\mathrm{.}90$ per $16$ oz$.$ bottleWater cost $$\mathrm{.}08$ per $16$ oz$.$ bottleDemand Rates:Operating Hours:Weekdays $($Monday Thursday$).$ Open $8$ hours.Weekends Friday, Saturday Open $12$ hours.Employees: One hourly employee needed weekdays. Two hourly employees needed weekends. Hourly employees are paid $$13\mathrm{.}50$ per hour. Your client will be the manager and draw a salary of $$42,000$ peryear. The assistant manager will receive a salary of $$28,500$ per year.$\%$ of customers purchasing Fruit Explosion $15\%\%$ of customers purchasing Vanilla Vanilla $35\%\%$ of customers purchasing Chocolate Mousse $30\%\%$ of customers purchasing Mango Tangerine Surprise $15\%\%$ of customers purchasing Strawberry Cr$$me $30\%\%$ of customers purchasing Pina Colada $15\%\%$ of customers purchasing Strawberry Kiwi $10\%\%$ of customers purchasing Orange Cr$$me $15\%\%$ of customers purchasing Chocolate sea salt $45\%\%$ of customers purchasing Cookies & Cr$$me $35\%\%$ of customers purchasing Frozen Ice Medium $12\%\%$ of customers purchasing Frozen Ice Large $12\%\%$ of customers purchasing Pop $6$pk $12\%\%$ of customers purchasing Pop $12$pk $10\%\%$ of customers purchasing sodas $10\%\%$ of customer purchasing water Operating Hours:Weekdays $($Monday $-$ Thursday$).$ Open $8$ hours.Weekends Friday, Saturday Open $12$ hours.Employees:$12\%12\%10\%10\%10\%$Your client will be the manager and draw a salary of $$42,000$ per year.The assistant manager will receive a salary of $$28,500$ per year.Product demand on average:: Fiday$-$Saturday the experts pecustomer age of $30$Friday $-$ Saturday the owner expects an average of $30$ customers per hour.Kitchen equipment: $$12,250$Cash register and sales equipment: $$2,750$Initial inventory: $$5,000$Pre$-$opening marketing: $$1,000$Store setup $($chairs$,$ tables, decor etc.$)$: $$3,500$Security deposit: $$4,500$First Insurance Payment: $$1,500$ and then Insurance will be $$1000$ a month.The building rent is $3600\mathrm{.}00$ per month.Phone$/$Internet will cost about $$1000$ per month.Electricity should cost about $$1200$ per month.Advertising and promotion will be $$500$ a month.Your client has $$15,000$ and plans to borrow $$46,000$ from the bank with a five$-$year loan at $7\mathrm{.}1\%$ interest. You are to calculate the monthly loan payment using the appropriate financialAssume a tax rate of $20\%$ if Income Before Taxes $($IBT$)$ is equal to or is greater than $$125,000.$Assume a tax rate of $15\%$ if Income Before Taxes $($IBT$)$ is less than $$124,999.$You are to calculate the monthly tax payment using the appropriate boolean$/$logical function.Assume that sales will grow at an average of $2\mathrm{.}46\%$ per month.Assume that each month contains $4\mathrm{.}2$ weeks. What If ScenarioKatie would really like to have a food truck to expand the business because there are lots of local outdoor events where she could sell her products.There is a used food truck available, but it will require somemodifications to work for her business. Additionally, there will be extra expenses for operating it$.$The following are the costs for this that have to be accounted for $-$ some are ongoing monthly, and some are one$-$time. She would like to pay for the used food truck over time like a car loan, but it will be at a different interest rate and term than her existing loan. New Trucks cost between $$50$ and $$100$K so she was really excited when she found an ad locally for the following used truck:Georgia Food Truck For Sale: $$32,000.-5\mathrm{.}924$ valve Cummins diesel engine, Automatic transmission $-2000$Freightliner MT$45$ with $363,132$ miles, needs new tires.The good news? She was able to negotiate the price to $$28,900$ but will still have to buy tires. The truck can be bought on a loan over $6$ years at $7\mathrm{.}15\%$ interest.To get it fully operational she will need the following: Tires $$3600,$ Paint or Wrap to brand with Perez POPS $-$ $$2500,$ Upgraded commercial freezer $$3900,$ additional licensing for the food truck $$155,$ Truck Insurance $$750$ monthly separate from the business insurance.To operate this part of the business, she will need to hire $2$ additional workers that have commercial driver's licenses. She will have to pay these $2$ people $$16$ per hour.The plan is to attend events and sell Perez POPS. Events vary month to month. But, each event will involve paying the workers for an $8$ hour day which includes the setup time and working the event.At events, Perez POPS will be selling an estimated $1100$ pops per event. Below is the schedule of events over the year and the approximate number of pops sold at each event. MonthNumber of EventsJanuary$0$February$03$March$4$April$7$May$7$June$15$July$15$August$12$September$8$October$8$November$2$December $2$ Approximate number of pops sold at each event:Fruit Explosion$175$Vanilla Vanilla$125$Chocolate Mousse$125$Mango Tangerine Surprise$50$Strawberry Cr$$me$100$Pina Colada$50$Strawberry Kiwi$50$Orange Cr$$me$50$Chocolate sea salt $200$Cookies and cream $175$In a new spreadsheet using the existing Proforma$-$ add on these additional needs to get the food truck business started. Should Katie invest in this opportunity now? Why or why not. Add a text box to the spreadsheet for your recommendation$/$ answers to these questions Recommendations:Show your client how these recommendations would affect the bottom line by recreating the pro forma for each scenario and applying the data analysis to determine profitability. You do not have to start from scratch but note these are completely independent pro formas. They mustPlan on showing your analysis and discussing the proforma changes that occur under each new scenario and how it affects profitability.Use a formatted text box $($not a comment$)$ to explain your recommendations under each new pro forma. This will be approximately a $2-3$ paragraph endeavor. and how it affects profitability. Use a formatted text box $($not a comment$)$ to explain your recommendations under each new pro forma. This will be approximately a $2-3$ paragraph endeavor.