I need help filling in this chart. Please help!

Salty Seafood Diner Please read the instructions carefully and provide what is being requested. A new client of yours, Mary Powell, wants to open a small diner selling calamari, oysters, fried shrimp, catfish sandwiches, salads, French fries, and sodas. He is planning to open the diner on the March 1, 2020. You, as an independent consultant, have been hired to develop a cash pro forma budget for his business venture (note: this is a spreadsheet that helps forecast income and expenses over a period of time). It can be used to plan for 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 - "Excel Assignment Diner Template" as your starting point. This is the file that must be downloaded and used as the basis for your assignment. Do not make any changes to this pre-designed template. This means changing the format. You will need to add your information and any other information needed for the assignment. Do not use a template from a previous semester - this is considered academic dishonesty and will be subject to disciplinary action. 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 two additional worksheets for your recommendations. Label one worksheet Beer Recommendation", label the other "Entertainment Recommendation". 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). Chart One-"Monthly Product Revenue" this will show the monthly revenue for each of your five products for the entire year. 1 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. Make sure both charts are formatted correctly (.e. appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Calamari, Oysters, Fried shrimp, Catfish sandwiches, Salads, French fries, and sodas. Product Selling Prices: Oysters on half shell will sell for $8.25 each (dozen) Fried Shrimp will sell for $10.25 (dozen) Calamari will sell for $4.95 an order Catfish Sandwich will sell for $5.95 Salads will sell for $4.50 each Fries sell for $1.25 per order Sodas sell for $1.25 a bottle Cost of Goods Sold: The Catfish sandwich ingredients (fried catfish, cheese, sauce, bun etc.) cost $3.15 per sandwich. Calamari cost per order $1.90. The fresh Shrimp cost $7.19 per order from the supplier. Oysters (sauce, lemon, oysters) cost $5.19 per order. Salad ingredients (lettuce, tomatoes, cheese, etc.) cost $1.23 per salad serving Frozen fries and oil average to $.67 per order Sodas cost about $.7 per 16 oz. bottle The building rent is $2775 per month. Phone will cost about $220 per month. Electricity should cost about $775 a month. Insurance will be $850 a month. Advertising and promotion will be $900 a month. Operating Hours: The diner will be open seven days a week. The diner will serve lunch and dinner and will be open from 11am-7pm on weekdays (Monday - Friday). It will need two hourly employees and an assistant manager (or manager) during these hours that the diner is open. On Saturdays and Sundays the store will be open 11am-11pm and will need three hourly employees and an assistant manager (or manger). Your client will be the manager and draw a salary of $48,500 per year (includes benefits). He will also work in the store during the busiest times, and fill in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $37,500 per year (includes benefits). The hourly workers will be paid $7.25 an hour. Monday through Fridays the owner expects an average of 17 customers an hour. Saturdays and Sundays the owner expects an average of 45 customers an hour. Demand Rate: On average, 1/4 of all customer will buy shrimp, 1/4 of the customers will buy oysters, 1/4 will buy Calamari, and 1/2 customers will buy Catfish, 3/4 of them will buy a salad, all of them buy french fries, and every customer will purchase a soda. Start-up costs for the diner includes: Kitchen equipment: $16,250 Cash register and sales equipment: $1,250 Initial inventory: $5,500 Pre-opening marketing: $3,500 Diner fixtures (chairs, tables etc.): $4,500 Oil painting of your client's momma to hang on the wall: $350 Licenses: $1,025 Security deposit: $6,500 First Insurance Payment: $850 Your client has $10,000 and plans to borrow the rest from the bank with a five-year loan at 5.1% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 24% if Income Before Taxes (IBT) is equal to or is greater than $23,500. Assume a tax rate of 15% if IBT is less than $23,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 2.55% per month. Assume that each month contains 4.2 weeks. Scenario One: "What if" Analysis Your client is unsure if he should sell beer at the diner. He thinks he can sell a beer to every other customer and it seems to be lucrative because the beer sells for $4.75 each and costs him only $1.10 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the beer customers (one soft drink less for every beer sold). It will cost him $5,250 to purchase the beer license from the county and his monthly insurance costs would rise by another $375 per month. What is your recommendation: Should your client offer beer to his customers? Excel Assignment Salty DinerTemplatespr20 Autos.com Home Insert Out Besu. Draw Page Layout Formulas Data Arial .12 . AA BOU A E fax Review == View > s swrap Test Merpecente Number $ % Namal Neural d Nor Calolation Core p latony. E- Delete Share MESUMAYO. 4 Sort & Find des XC Tiller Comments u Sensitivity 82 Condona Fornel Check Cell Insert Format Pom x 124 F Formula Builder a Search Maxwell's Seafood Diner Most Recently Used SUM AVERAGE 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 HYPERLINK COUNT MAX 5 Revenue: 5 Oysters 7 Fried Shrimp 8 Calamari 9 Catfish Sandwich 10 Salads 11 Fries 12 Sodas 13 Monthly Revenue SUMIR PMT STDEV 14 ARS FX SUM Adds all the numbers in a range of cols 15 Expenses: 16 COGS 17 Rent 18 Phone 19 Electricity 20 Insurance 21 Advertising 22 Hourly Wages 23 Salaries 24 Loan Payment 25 Total Expenses Syntax SIM number, pumba Numbert: number number... 1 to 265 rubro. Logica de Ignored in o rder typedas Numar number numbr blon. Lugo Igrond in cells included typed tn 25 . 27 Income Before Tax 28 Tax 29 Net Income 30 Cash Flow Mare held on this functie Grading criters Cash Prolorms Shell Assumptions Startup Cost Calculations Beer Recommendation Entertainment Recommendation Shanta + - + 138% Maxwell's Seafood Diner Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Revenue: Oysters Fried Shrimp Calamari Catfish Sandwich Salads Fries Sodas Monthly Revenue Expenses: COGS Rent Phone Electricity Insurance Advertising Hourly Wages Salaries Loan Payment Total Expenses Income Before Tax Tax Net Income Cash Flow Salty Seafood Diner Please read the instructions carefully and provide what is being requested. A new client of yours, Mary Powell, wants to open a small diner selling calamari, oysters, fried shrimp, catfish sandwiches, salads, French fries, and sodas. He is planning to open the diner on the March 1, 2020. You, as an independent consultant, have been hired to develop a cash pro forma budget for his business venture (note: this is a spreadsheet that helps forecast income and expenses over a period of time). It can be used to plan for 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 - "Excel Assignment Diner Template" as your starting point. This is the file that must be downloaded and used as the basis for your assignment. Do not make any changes to this pre-designed template. This means changing the format. You will need to add your information and any other information needed for the assignment. Do not use a template from a previous semester - this is considered academic dishonesty and will be subject to disciplinary action. 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 two additional worksheets for your recommendations. Label one worksheet Beer Recommendation", label the other "Entertainment Recommendation". 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). Chart One-"Monthly Product Revenue" this will show the monthly revenue for each of your five products for the entire year. 1 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. Make sure both charts are formatted correctly (.e. appropriate title, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assignment: Calamari, Oysters, Fried shrimp, Catfish sandwiches, Salads, French fries, and sodas. Product Selling Prices: Oysters on half shell will sell for $8.25 each (dozen) Fried Shrimp will sell for $10.25 (dozen) Calamari will sell for $4.95 an order Catfish Sandwich will sell for $5.95 Salads will sell for $4.50 each Fries sell for $1.25 per order Sodas sell for $1.25 a bottle Cost of Goods Sold: The Catfish sandwich ingredients (fried catfish, cheese, sauce, bun etc.) cost $3.15 per sandwich. Calamari cost per order $1.90. The fresh Shrimp cost $7.19 per order from the supplier. Oysters (sauce, lemon, oysters) cost $5.19 per order. Salad ingredients (lettuce, tomatoes, cheese, etc.) cost $1.23 per salad serving Frozen fries and oil average to $.67 per order Sodas cost about $.7 per 16 oz. bottle The building rent is $2775 per month. Phone will cost about $220 per month. Electricity should cost about $775 a month. Insurance will be $850 a month. Advertising and promotion will be $900 a month. Operating Hours: The diner will be open seven days a week. The diner will serve lunch and dinner and will be open from 11am-7pm on weekdays (Monday - Friday). It will need two hourly employees and an assistant manager (or manager) during these hours that the diner is open. On Saturdays and Sundays the store will be open 11am-11pm and will need three hourly employees and an assistant manager (or manger). Your client will be the manager and draw a salary of $48,500 per year (includes benefits). He will also work in the store during the busiest times, and fill in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $37,500 per year (includes benefits). The hourly workers will be paid $7.25 an hour. Monday through Fridays the owner expects an average of 17 customers an hour. Saturdays and Sundays the owner expects an average of 45 customers an hour. Demand Rate: On average, 1/4 of all customer will buy shrimp, 1/4 of the customers will buy oysters, 1/4 will buy Calamari, and 1/2 customers will buy Catfish, 3/4 of them will buy a salad, all of them buy french fries, and every customer will purchase a soda. Start-up costs for the diner includes: Kitchen equipment: $16,250 Cash register and sales equipment: $1,250 Initial inventory: $5,500 Pre-opening marketing: $3,500 Diner fixtures (chairs, tables etc.): $4,500 Oil painting of your client's momma to hang on the wall: $350 Licenses: $1,025 Security deposit: $6,500 First Insurance Payment: $850 Your client has $10,000 and plans to borrow the rest from the bank with a five-year loan at 5.1% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 24% if Income Before Taxes (IBT) is equal to or is greater than $23,500. Assume a tax rate of 15% if IBT is less than $23,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 2.55% per month. Assume that each month contains 4.2 weeks. Scenario One: "What if" Analysis Your client is unsure if he should sell beer at the diner. He thinks he can sell a beer to every other customer and it seems to be lucrative because the beer sells for $4.75 each and costs him only $1.10 to purchase. Unfortunately your client is afraid that he would cannibalize his soft drink sales with the beer customers (one soft drink less for every beer sold). It will cost him $5,250 to purchase the beer license from the county and his monthly insurance costs would rise by another $375 per month. What is your recommendation: Should your client offer beer to his customers? Excel Assignment Salty DinerTemplatespr20 Autos.com Home Insert Out Besu. Draw Page Layout Formulas Data Arial .12 . AA BOU A E fax Review == View > s swrap Test Merpecente Number $ % Namal Neural d Nor Calolation Core p latony. E- Delete Share MESUMAYO. 4 Sort & Find des XC Tiller Comments u Sensitivity 82 Condona Fornel Check Cell Insert Format Pom x 124 F Formula Builder a Search Maxwell's Seafood Diner Most Recently Used SUM AVERAGE 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 HYPERLINK COUNT MAX 5 Revenue: 5 Oysters 7 Fried Shrimp 8 Calamari 9 Catfish Sandwich 10 Salads 11 Fries 12 Sodas 13 Monthly Revenue SUMIR PMT STDEV 14 ARS FX SUM Adds all the numbers in a range of cols 15 Expenses: 16 COGS 17 Rent 18 Phone 19 Electricity 20 Insurance 21 Advertising 22 Hourly Wages 23 Salaries 24 Loan Payment 25 Total Expenses Syntax SIM number, pumba Numbert: number number... 1 to 265 rubro. Logica de Ignored in o rder typedas Numar number numbr blon. Lugo Igrond in cells included typed tn 25 . 27 Income Before Tax 28 Tax 29 Net Income 30 Cash Flow Mare held on this functie Grading criters Cash Prolorms Shell Assumptions Startup Cost Calculations Beer Recommendation Entertainment Recommendation Shanta + - + 138% Maxwell's Seafood Diner Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Revenue: Oysters Fried Shrimp Calamari Catfish Sandwich Salads Fries Sodas Monthly Revenue Expenses: COGS Rent Phone Electricity Insurance Advertising Hourly Wages Salaries Loan Payment Total Expenses Income Before Tax Tax Net Income Cash Flow