please make an excel and show how you were able to do it.

For sheet 1 after the grading criteria, how do i find COGS, the total wages is wrong, and the rest of the blank cells.

For the startup costs, how do I find the owner's Equity, and the cash reserves is wrong and how do i find it?

Product demand on average: - Weekdays the owner expects 18 customers per hour. - Friday - Saturday the owner expects an average of 30 customers per hour. Start-up costs Kitchen equipment: $12,250 Cash register and sales equipment: $2,550 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.00 per month. Phone/Internet will cost about $1000 per month. Electricity should cost about $1300 per month. Advertising and promotion will be $500 a month. Your client has $18,000 and plans to borrow $45,000 from the bank with a five-year loan at 7.2% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 22% 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 booleanlogical function. Assume that sales will grow at an average of 2.43% per month. Assume that each month contains 4.2 weeks. - Chart One - "Monthly Product Revenue" - this will show the monthly revenue for each of your five products for the entire year. Charts should be on their own worksheet. - Chart Two - "Total Product Net Income" - You want to track the total product net income for the year to determine any trends or projections in product sales. Charts should be on their own worksheet. - Make sure both charts are formatted correctly (i.e, appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Specialty POPS, Frozen Ice, Soda and Bottle water. Product Selling Prices: POPS Fruit Explosion $4.85 Vanilla Vanilla $3.85 Chocolate Mousse $4.35 Mango Tangerine Surprise $4.35 Strawberry Crme $3.75 Pina Colada $4.85 Strawberry Kiwi $4.35 Orange Crme $3.75 Chocolate sea salt $4.35 Cookies \& Crme $3.25 Pop Packs 12pk$32.00 6pk$15.00 Frozen ice Medium \$3.75 Large $4.25 Soda $1.95 a bottle Bottled Water - $1.25 a bottle Cost of Goods Sold: POPS Fruit Explosion $0.90 VanillaVanilla $0.40 Chocolate Mousse $0.95 Mango Tangerine $0.70 Strawberry Crme $0.70 Pina Colada $0.90 Strawberry Kiwi $0.80 Orange Crme $0.50 What if Scenario Katie 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 avalable, but it will require some modifications 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 $100K so she was really excited when she found an ad locally for the following used truck: Georgia Food Truck For Sale: \$33,000, -5.924 vaive Cummins diesel engine, Automatio transmission - 2000 Freightliner MT45 with 363,132 miles, needs new tires. The good news? She was able to negotiate the price to $29,900 but will still have to buy tires. The truck can be bought on a loan over 6 years at 7.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 $145. Truck Insurance $650 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 $15 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 1000 pops per event. Below is the schedule of events over the year and the approximate number of pops sold at each event. Annrovimate numher of noos sold at each event: All information cells on cash proforma are referenced back to the assumption page - Formulas and functions, calculations are correct, all fields have values, twelve months are presented and an annual totals are given as well as a monthly totals. Hard coded numbers even if correct will result in 4 zero points. 5 Assumptions and startup worksheets setup and complete. Recommendation are based on cash proforma information and what if analysis. Recommendation is typed in text box - minimum 4 sentences and are based on cash proforma information and what if analysis - Alternative 6 scenarios must be shown as proformas with the changes. 7 The charts are present and reflect worksheet content. Taxes are calculated correctly using boolean logic and appropriate 8 calculation. 9 Loan payment is calculated using a function. 10 Score (out of 100) B D 11 12 13 14 15 16 1 Start Up Costs: 2 3 Kitchen equipment 4 Cash Register and Sales equipment 5 Initial inventory 6 Pre-opening marketing 7 Store Setup (chairs, tables, decor etc.) 8 Security deposit 9 Initial insurance payment 10 Total 11 12 Owner's Equity 13 Cash Reserves 14 Loan Amount $12,250.00 $2,550.00 $5,000.00 $1,000.00 $3,500.00 $4,500.00 $1,500.00 $30,300.00 $18,000.00 $45,000.00 Perez POPS 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 beselling Frozen POPS, Frozen lces, Frozen POPS Packs, Soda and Bottled Water. Her grand opening will be January 1,2024. 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.xls" 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". - 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 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). 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 textbox to the spreadsheet for your recommendationlanswers 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 must update accordingly from the data worksheets. Plan 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 23 paragraph endeavor. Reminder: Submit the completed Project in an Excel file to the D2L assignment Dropbox. Name the file YourlastnameExcel.xisx - Remember (Google Sheets, Numbers, Links, PDFs are all unacceptable - must be an Excel File). Revenue: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Total Frut Expiosion POPS Vanilla Vanila POPS Checclate Mousse POPS Mango Tangerine POPS Sirawbery Creme POPS Pina Colada POPS 11 Strmbery Kiwi POPS 12. Orange Creme POPS 13 Chocolate See Suit POPS. 14 Cochies 8 Crome POPS 15 12 Pack POPS 16 . Pack POPS 17 Medium Frozen ice 15 Large Frozen lce 19 Soda 20 Botted Water 21 Monthly Revenue 22 23 Expenses: \begin{tabular}{|c|c|c|} \hline & cogs & \\ \hline2524 & Rent & 3600 \\ \hline 20 & Phone & 1.000 \\ \hline 27 & Electricity & 1,300 \\ \hline 28 & insurance & 1,000 \\ \hline 20 & Advertsing & 500 \\ \hline 30 & Hourly Wages & 1250 \\ \hline 31 & Salaries & $68,500.00 \\ \hline 32 & Loan Payment & $89531 \\ \hline & Total Expens & \\ \hline \end{tabular} 39 Total Expenses 35 income Before Tax 36 TaX 37 Net income \begin{tabular}{cc} s & 3,600 \\ s & 1,000 \\ 5 & 1,300 \\ s & 1,000 \\ s & 500 \\ 5 & 12.50 \\ 568,500,00 \\ & $895.31 \end{tabular} 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 $12.50 per hour. - Your client will be the manager and draw a salary of $40,000 per year. - The assistant manager will receive a salary of $28,500 per year. Product demand on average: - Weekdays the owner expects 18 customers per hour. - Friday - Saturday the owner expects an average of 30 customers per hour. Start-up costs Kitchen equipment: $12,250 Cash register and sales equipment: $2,550 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.00 per month. Phone/Internet will cost about $1000 per month. Electricity should cost about $1300 per month. Advertising and promotion will be $500 a month. Your client has $18,000 and plans to borrow $45,000 from the bank with a five-year loan at 7.2% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 22% 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 booleanlogical function. Assume that sales will grow at an average of 2.43% per month. Assume that each month contains 4.2 weeks. - Chart One - "Monthly Product Revenue" - this will show the monthly revenue for each of your five products for the entire year. Charts should be on their own worksheet. - Chart Two - "Total Product Net Income" - You want to track the total product net income for the year to determine any trends or projections in product sales. Charts should be on their own worksheet. - Make sure both charts are formatted correctly (i.e, appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Products: Specialty POPS, Frozen Ice, Soda and Bottle water. Product Selling Prices: POPS Fruit Explosion $4.85 Vanilla Vanilla $3.85 Chocolate Mousse $4.35 Mango Tangerine Surprise $4.35 Strawberry Crme $3.75 Pina Colada $4.85 Strawberry Kiwi $4.35 Orange Crme $3.75 Chocolate sea salt $4.35 Cookies \& Crme $3.25 Pop Packs 12pk$32.00 6pk$15.00 Frozen ice Medium \$3.75 Large $4.25 Soda $1.95 a bottle Bottled Water - $1.25 a bottle Cost of Goods Sold: POPS Fruit Explosion $0.90 VanillaVanilla $0.40 Chocolate Mousse $0.95 Mango Tangerine $0.70 Strawberry Crme $0.70 Pina Colada $0.90 Strawberry Kiwi $0.80 Orange Crme $0.50 What if Scenario Katie 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 avalable, but it will require some modifications 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 $100K so she was really excited when she found an ad locally for the following used truck: Georgia Food Truck For Sale: \$33,000, -5.924 vaive Cummins diesel engine, Automatio transmission - 2000 Freightliner MT45 with 363,132 miles, needs new tires. The good news? She was able to negotiate the price to $29,900 but will still have to buy tires. The truck can be bought on a loan over 6 years at 7.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 $145. Truck Insurance $650 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 $15 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 1000 pops per event. Below is the schedule of events over the year and the approximate number of pops sold at each event. Annrovimate numher of noos sold at each event: All information cells on cash proforma are referenced back to the assumption page - Formulas and functions, calculations are correct, all fields have values, twelve months are presented and an annual totals are given as well as a monthly totals. Hard coded numbers even if correct will result in 4 zero points. 5 Assumptions and startup worksheets setup and complete. Recommendation are based on cash proforma information and what if analysis. Recommendation is typed in text box - minimum 4 sentences and are based on cash proforma information and what if analysis - Alternative 6 scenarios must be shown as proformas with the changes. 7 The charts are present and reflect worksheet content. Taxes are calculated correctly using boolean logic and appropriate 8 calculation. 9 Loan payment is calculated using a function. 10 Score (out of 100) B D 11 12 13 14 15 16 1 Start Up Costs: 2 3 Kitchen equipment 4 Cash Register and Sales equipment 5 Initial inventory 6 Pre-opening marketing 7 Store Setup (chairs, tables, decor etc.) 8 Security deposit 9 Initial insurance payment 10 Total 11 12 Owner's Equity 13 Cash Reserves 14 Loan Amount $12,250.00 $2,550.00 $5,000.00 $1,000.00 $3,500.00 $4,500.00 $1,500.00 $30,300.00 $18,000.00 $45,000.00 Perez POPS 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 beselling Frozen POPS, Frozen lces, Frozen POPS Packs, Soda and Bottled Water. Her grand opening will be January 1,2024. 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.xls" 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". - 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 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). 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 textbox to the spreadsheet for your recommendationlanswers 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 must update accordingly from the data worksheets. Plan 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 23 paragraph endeavor. Reminder: Submit the completed Project in an Excel file to the D2L assignment Dropbox. Name the file YourlastnameExcel.xisx - Remember (Google Sheets, Numbers, Links, PDFs are all unacceptable - must be an Excel File). Revenue: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Total Frut Expiosion POPS Vanilla Vanila POPS Checclate Mousse POPS Mango Tangerine POPS Sirawbery Creme POPS Pina Colada POPS 11 Strmbery Kiwi POPS 12. Orange Creme POPS 13 Chocolate See Suit POPS. 14 Cochies 8 Crome POPS 15 12 Pack POPS 16 . Pack POPS 17 Medium Frozen ice 15 Large Frozen lce 19 Soda 20 Botted Water 21 Monthly Revenue 22 23 Expenses: \begin{tabular}{|c|c|c|} \hline & cogs & \\ \hline2524 & Rent & 3600 \\ \hline 20 & Phone & 1.000 \\ \hline 27 & Electricity & 1,300 \\ \hline 28 & insurance & 1,000 \\ \hline 20 & Advertsing & 500 \\ \hline 30 & Hourly Wages & 1250 \\ \hline 31 & Salaries & $68,500.00 \\ \hline 32 & Loan Payment & $89531 \\ \hline & Total Expens & \\ \hline \end{tabular} 39 Total Expenses 35 income Before Tax 36 TaX 37 Net income \begin{tabular}{cc} s & 3,600 \\ s & 1,000 \\ 5 & 1,300 \\ s & 1,000 \\ s & 500 \\ 5 & 12.50 \\ 568,500,00 \\ & $895.31 \end{tabular} 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 $12.50 per hour. - Your client will be the manager and draw a salary of $40,000 per year. - The assistant manager will receive a salary of $28,500 per year