Please read the instructions carefully. A new client of yours, Katie Perez wants to open an ice cream shop near the downtown shopping center. She will selling Frozen POPS, Frozen Ices, Frozen POPS Packs, Soda and Bottled Water. Her grand opening will be June 1, 2023. Katie needs help figuring out how her business will perform the first year in business. She has hired you as an independent consultant, prepare a cash proforma budget for her business venture. 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 of operations. Use the file attached Excel Template - "Perezpops.xIs" as your starting point. Download and use this file as the basis for your assignment. Do not make any changes to this pre-defined template items (this means start with the items that already included and that are expected in the spreadsheet). You may add your information to the existing sheets. You may 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 be subject to disciplinary action. - Do not remove the Grading Criteria Worksheet. - Place the finished cash pro forma on a worksheet labeled "Cash ProForma". - Place all your case assumptions data on a separate worksheet. Label the worksheet "Assumptions" (note: each piece of data must appear in its own cell on the 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 and label two additional worksheets for the charts (each chart will be in its own worksheet). - Create addisional worksheess for your Whar ir Scenario recommendiston. Events - Approprite Charts (gruphs) You wil be creating wo separate charts so create and label two assitional worksheets for the chars (each chart will be in its own worksheet). - Char One -" Monthly Product Revenue" - this wil show the monthiy reverwe for each of your five products for the entire year - Char Two - Total Product Net income" - You want to track the lotal product net income for the year to dewermine any trends or projections in product iates. - Make sure both charts are formatied comectly fie. appropriate tite, legend where appropriase, data series property labelide and they are appropriate for business use. Information needed to complete assignment: Products: Specialty POPS, Frocen lee, Soda and Botte water. Product Selling Prices: pops Fruit Explosion 54.75 Vanilla Verilla $3.75 Chocoline Mousse $4.25 Mango Tangerne Surprise $4.25 Strawbery Creme $3.75 Pina Colasa $4,75 Strabery Kiwi \$4.25 Orange Creme $3.75 Chocolane sea set $4.25 Cookies 8 Creme $3.25 Pop Pocks 12pk348.00 Gpk 32400 Frazen lee Frazen ice Laroe S4.2s POPS Fruit Explosion $4.75 Vanilla Varila 53.75 Chocolate Mousse $4.25 Mango Tangerhe Surprise \$4.25 Struwbery Creme $3.75 Pina Colada S4.75 Strawbery Kowi $4.25 Orange Creme 53.75 Chocolate sea NhS4.25 Cookies \& Creme $3.25 Pop Packs 12pt345.00 epk 54.00 Froden ice Froden ice Lave \$4.25 Soda $1.95 a botte Bolled Water - $1.25 a bollte Cost of Coods sold: POPS Frunt Explosion $0.90 Vanillavarila $0.40 Chocolate Mousse $0.95 Mango Tangerine $0.70 Strawbery Crume 50.50 Pina Colada 80.90 Strawbery Kowi $0.70 Orange Creme $0.50 Chacolate seat swi 50.95 Cookies $ Creme $0.70 Pop Packs Freasen ice Mostum 50.196 Larpe $1.10 Sodas cod about 590 per 16 ov. bolll Water cost $.06 per 16 oc. botle Domand Rates: W of customers purchasing Frut Explosion W of customers purchasing Vanilivanilla W of customers purchasing Chooolate Mousse. 30% W of cusbomes purchasing Mango Tangerine Surprise 15% \% of aushemen purchasing strabbery Creme N of cusbmen purchativg Pina Colade W of customen purchasing Wtrawberry Kow \% of cuslomers purchasing Orange Creme W of customers purchasing Chocolate sea sat W of cuslomers purchasing Cookies \& Crime 35% Wh of customers purctasing Cookies \& Crime W of customers purchasing Frozen loe Medum 20s W of customers purchasing Frozen loe Large 15% W of customes purchasing Poo Gpk 25s \% of customers purchasing Poo 12pk 15\% W of cusbemes purchasing sodas 105 * of ouskmer purchasing water 103 Operating Hours: Weekdays (Monday - Thursdiy). Open s hours Weekends Fridxy, Saturday Open 12 houn. Employees: - One houly employee needed weekdian. * Two hourfy employees neesed woekand. - Houry employees are paid \$12.00 per hour - Your clent wil be the managor and draw a salary of $40.000 per yeur. - The assistant manager wilt roceive a salary of 528.500 per year. Product demand on mverage: - Woekdars the owner expects 20 customen per hour. - Friday - Salurdsy the owner expecta ah anverage of 32 cuttomen per hour. Start-up eosts Kinchen equipment $12,290 Cash reqister and salis equpmert $2,550 The bulding rent is 3600.00 per month. Phoneintemed wit cos about $1000 per morth. Electricily should cos about $1300 per month. Advertining and promotion will be $500 a month. Your dlient has $20,000 and plart io borrow $45,000 thom the bark wh a five year bon at 6.1$ interest. You are to caloulate the monthly loon payment ising the appegprote tinarelil functon. Asaume a tax rate of 2Z\% if inoome Belore Taxes (IET) is equal to or in gruter than 535.504 Asaume a tax rate of 15% if income Belore Thoes (18T) is lest than $35,490. You ase io caloulabe the montily tax payment using the appropriabe iogical furction. Assume that sales wil grow at an average of 2.40 s per month. Assume thet each manth contwins 4.2 weeks. Recommendations: Show your cilent how these recommendafions would aflect the botom line by rectuatng the gro forma for ebch scenaris and applying the data aralyais to dewemine proftielly. You do not have to start from seratch, but note, these are campielely independent pro format. They must updise acoordingly from the data warksheet. Plan on showing your analhis and discuasing the proflome tharges that oceur under exch new scenario and how in affects profiabilit. Use a formaned tent box (not a comment) to expiain your recommendutions under euch new pro forma. This will be approsimetely a 2-3 paragraph endeuvor. What if Scenario Kate would mally bie to heve a food tuck to expand the business becave teen ane lab of bocal odboor The following are the costs for tis that have to be acosurted for - some are congoing monthly and bome are onetime. She would the to pay lor the used tood truok over fime like a car loan, but it wil be at a diferent intorest tale and berm than her exaling loen. New Trods eoet setween $50 and 510cK so ahe was roaly exciled when she found an ad localy lor be following used tuck Georgis Food Truck For Sale. 532.090. 5.924 value Cummint desel angine. Autamasc tranamitsion-2000 Ensatilee MTAS with 354,132 miles needt new Aires. The good news? She was able to negoliale the price te tas 400 but wal its have bo buy tres. The truck cah be bought on a loan over 6 years at 6.75% interest. To get in fully operafonal she wit need the following. Fires suob. Paint or Wha to brand with Perez POPs. $2600, Upgraded commerdial freezer $3700, addbonal loenting for fhe food truck 5125 , Truck insurance. 5850 montry separate from the buanest incurance To ocerate this pad of the businesi, she will need to hice 2 addbonal workers that have commercial driver's licenses. She will hove ts pisy these 2 people $15 per hour The plan is to athend everta and sel Perez POPS. Eienta wary month b morih. But each event will involve pwying the workers for an 8 hour day which inctudet the setup lime and working the tevent. A evants, Perez POPS will be selling an entimated 1900 pees per evtnt. Below is the achedule of eventa oroc the year. In a new spreadsheet, using the exising Prolomma - add on these adstonal needs to get the food truch businest sharted. Stould Kobe imeet in this ceportunity now? Why or why net Add a Inatbes to the speradsheel for your recommendatonlanswers to these queltions. Aleminder: Aeminder: Subme the completed Project in an Exoel the to the bla aatignent Dropbox. Name the fle Yourtasthamefrocl ylsx - Remember (Google Sheets. Nunbert, Linta. PDFs are all unacceptable - must be an Exsen fine Please read the instructions carefully. A new client of yours, Katie Perez wants to open an ice cream shop near the downtown shopping center. She will selling Frozen POPS, Frozen Ices, Frozen POPS Packs, Soda and Bottled Water. Her grand opening will be June 1, 2023. Katie needs help figuring out how her business will perform the first year in business. She has hired you as an independent consultant, prepare a cash proforma budget for her business venture. 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 of operations. Use the file attached Excel Template - "Perezpops.xIs" as your starting point. Download and use this file as the basis for your assignment. Do not make any changes to this pre-defined template items (this means start with the items that already included and that are expected in the spreadsheet). You may add your information to the existing sheets. You may 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 be subject to disciplinary action. - Do not remove the Grading Criteria Worksheet. - Place the finished cash pro forma on a worksheet labeled "Cash ProForma". - Place all your case assumptions data on a separate worksheet. Label the worksheet "Assumptions" (note: each piece of data must appear in its own cell on the 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 and label two additional worksheets for the charts (each chart will be in its own worksheet). - Create addisional worksheess for your Whar ir Scenario recommendiston. Events - Approprite Charts (gruphs) You wil be creating wo separate charts so create and label two assitional worksheets for the chars (each chart will be in its own worksheet). - Char One -" Monthly Product Revenue" - this wil show the monthiy reverwe for each of your five products for the entire year - Char Two - Total Product Net income" - You want to track the lotal product net income for the year to dewermine any trends or projections in product iates. - Make sure both charts are formatied comectly fie. appropriate tite, legend where appropriase, data series property labelide and they are appropriate for business use. Information needed to complete assignment: Products: Specialty POPS, Frocen lee, Soda and Botte water. Product Selling Prices: pops Fruit Explosion 54.75 Vanilla Verilla $3.75 Chocoline Mousse $4.25 Mango Tangerne Surprise $4.25 Strawbery Creme $3.75 Pina Colasa $4,75 Strabery Kiwi \$4.25 Orange Creme $3.75 Chocolane sea set $4.25 Cookies 8 Creme $3.25 Pop Pocks 12pk348.00 Gpk 32400 Frazen lee Frazen ice Laroe S4.2s POPS Fruit Explosion $4.75 Vanilla Varila 53.75 Chocolate Mousse $4.25 Mango Tangerhe Surprise \$4.25 Struwbery Creme $3.75 Pina Colada S4.75 Strawbery Kowi $4.25 Orange Creme 53.75 Chocolate sea NhS4.25 Cookies \& Creme $3.25 Pop Packs 12pt345.00 epk 54.00 Froden ice Froden ice Lave \$4.25 Soda $1.95 a botte Bolled Water - $1.25 a bollte Cost of Coods sold: POPS Frunt Explosion $0.90 Vanillavarila $0.40 Chocolate Mousse $0.95 Mango Tangerine $0.70 Strawbery Crume 50.50 Pina Colada 80.90 Strawbery Kowi $0.70 Orange Creme $0.50 Chacolate seat swi 50.95 Cookies $ Creme $0.70 Pop Packs Freasen ice Mostum 50.196 Larpe $1.10 Sodas cod about 590 per 16 ov. bolll Water cost $.06 per 16 oc. botle Domand Rates: W of customers purchasing Frut Explosion W of customers purchasing Vanilivanilla W of customers purchasing Chooolate Mousse. 30% W of cusbomes purchasing Mango Tangerine Surprise 15% \% of aushemen purchasing strabbery Creme N of cusbmen purchativg Pina Colade W of customen purchasing Wtrawberry Kow \% of cuslomers purchasing Orange Creme W of customers purchasing Chocolate sea sat W of cuslomers purchasing Cookies \& Crime 35% Wh of customers purctasing Cookies \& Crime W of customers purchasing Frozen loe Medum 20s W of customers purchasing Frozen loe Large 15% W of customes purchasing Poo Gpk 25s \% of customers purchasing Poo 12pk 15\% W of cusbemes purchasing sodas 105 * of ouskmer purchasing water 103 Operating Hours: Weekdays (Monday - Thursdiy). Open s hours Weekends Fridxy, Saturday Open 12 houn. Employees: - One houly employee needed weekdian. * Two hourfy employees neesed woekand. - Houry employees are paid \$12.00 per hour - Your clent wil be the managor and draw a salary of $40.000 per yeur. - The assistant manager wilt roceive a salary of 528.500 per year. Product demand on mverage: - Woekdars the owner expects 20 customen per hour. - Friday - Salurdsy the owner expecta ah anverage of 32 cuttomen per hour. Start-up eosts Kinchen equipment $12,290 Cash reqister and salis equpmert $2,550 The bulding rent is 3600.00 per month. Phoneintemed wit cos about $1000 per morth. Electricily should cos about $1300 per month. Advertining and promotion will be $500 a month. Your dlient has $20,000 and plart io borrow $45,000 thom the bark wh a five year bon at 6.1$ interest. You are to caloulate the monthly loon payment ising the appegprote tinarelil functon. Asaume a tax rate of 2Z\% if inoome Belore Taxes (IET) is equal to or in gruter than 535.504 Asaume a tax rate of 15% if income Belore Thoes (18T) is lest than $35,490. You ase io caloulabe the montily tax payment using the appropriabe iogical furction. Assume that sales wil grow at an average of 2.40 s per month. Assume thet each manth contwins 4.2 weeks. Recommendations: Show your cilent how these recommendafions would aflect the botom line by rectuatng the gro forma for ebch scenaris and applying the data aralyais to dewemine proftielly. You do not have to start from seratch, but note, these are campielely independent pro format. They must updise acoordingly from the data warksheet. Plan on showing your analhis and discuasing the proflome tharges that oceur under exch new scenario and how in affects profiabilit. Use a formaned tent box (not a comment) to expiain your recommendutions under euch new pro forma. This will be approsimetely a 2-3 paragraph endeuvor. What if Scenario Kate would mally bie to heve a food tuck to expand the business becave teen ane lab of bocal odboor The following are the costs for tis that have to be acosurted for - some are congoing monthly and bome are onetime. She would the to pay lor the used tood truok over fime like a car loan, but it wil be at a diferent intorest tale and berm than her exaling loen. New Trods eoet setween $50 and 510cK so ahe was roaly exciled when she found an ad localy lor be following used tuck Georgis Food Truck For Sale. 532.090. 5.924 value Cummint desel angine. Autamasc tranamitsion-2000 Ensatilee MTAS with 354,132 miles needt new Aires. The good news? She was able to negoliale the price te tas 400 but wal its have bo buy tres. The truck cah be bought on a loan over 6 years at 6.75% interest. To get in fully operafonal she wit need the following. Fires suob. Paint or Wha to brand with Perez POPs. $2600, Upgraded commerdial freezer $3700, addbonal loenting for fhe food truck 5125 , Truck insurance. 5850 montry separate from the buanest incurance To ocerate this pad of the businesi, she will need to hice 2 addbonal workers that have commercial driver's licenses. She will hove ts pisy these 2 people $15 per hour The plan is to athend everta and sel Perez POPS. Eienta wary month b morih. But each event will involve pwying the workers for an 8 hour day which inctudet the setup lime and working the tevent. A evants, Perez POPS will be selling an entimated 1900 pees per evtnt. Below is the achedule of eventa oroc the year. In a new spreadsheet, using the exising Prolomma - add on these adstonal needs to get the food truch businest sharted. Stould Kobe imeet in this ceportunity now? Why or why net Add a Inatbes to the speradsheel for your recommendatonlanswers to these queltions. Aleminder: Aeminder: Subme the completed Project in an Exoel the to the bla aatignent Dropbox. Name the fle Yourtasthamefrocl ylsx - Remember (Google Sheets. Nunbert, Linta. PDFs are all unacceptable - must be an Exsen fine