I need help with the recommendation section of the document. Specifically the proforma section for the Scenarios. I also need help with the demand rate thats found in the document.
xX ExAssignmentDinerempla OAtend of yours wants to ope met mate O Fle homeichronos/99149Ba51e620d01647baobe01992d55617d6/My les/Downloads/2200ExcelDinersm19%20(2) pdf 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 comectly (e, appropriate tite, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assianment Calamari, Oysters, Fried shrimp, Caish sandwiches, Salads, French fries, and sodas. Product Selling Prices: Oysters on hait shell will sell for s8.25 each (donen) Fried Shrimp will sell for $10.25 (dozen) Calamari will sell for $4.95 an order Catfeh Sandwich will sell for $5.95- Salads will sell for $4.50 each Fries sell for $1.25 per order Sodas sell for $1.75 a botle Cost of Goods Sold The Cafish sandwich ingredients (tried catfish, cheese, sauce, bun etc) cost $3.15 per sandwich Calamani cost per order $1.90. The fresh Shrimp cost $7.19 per order from the supplier Oysers (sauce, lemon, oysters) cost $5.19 per order Salad ingredents dettuce, tomatoes, cheese, e) cos $1.23 per salad serving Frozen tries and oil average to $.67 per onder Sodas cost about $.7 per 16 or. bomer The building rent is $2775 per month Phone will cost about $220 per month ctricity should cost about $775 a month Insur Adversing and pm s900 a month 11:5 C & 2 3 4 5 7 8 C r t O f h k C 6 m X AssignmentDemp A nd of yours wants to ope ematio home/chronos9914951e20d01647b7batbe0199205561766/Myles/Downloads/2200ExcelDinersm19%20(2) pd Operating Hours The diner will be open six days a week The diner will serve lunch and dinner and will be open from 11am-9pm on weekdays (Monday-Friday) t will need two hourly employee and an assistant manager (or manager) during these hours that the diner is open On Saturdays the restaurant will be open 11am-11pm and will need three hourly employees and an assistant manager (or manger). On Sundays the restaurant is only open dinner 4pm-8pm. assistant manager will be needed during these hours One hourly employee and an Your clent will be the manager and draw a salaly of $48,500 per year (includes benefies). He wil also work in the saore during the busiest fimes, and il in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $37,500 per year inckudes benefes) The hourly workers will be paid $8.25 an hour Monday through Fridays the owner expects an average of 15 customers an hour Saturdays and Sundays the owner expects an average of 40 cusomers an hour The restaurant is located near the beach so there is alot of foot rafic mot days with Sunday being the slowest Demand Rate 1204 2 4 5 6 7 backspace 9 r t e y d f j k b V n X Exce Assgmentiner Tpla Atnd of yours wants to ope x ome/chronos/u9914918a51e20d01d47b7ba0be0199245561706/MyFiles/Downloads/2200ExcelDinersm19%20(2) pd Demand Rate On average, 1/4 of all customer will buy shrimp, 1/4 of the customers will buy oysters, 1/4 wi 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 fatures (chairs, tables etc.): $4,500 Oil painting of your clients momma to hang on the wall: $350 Licenses $1,025 Security deposit $6,900 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 appropriste financial function Assume a tax rate of 23% if Income Betore Taxes (BT) is equal to or is greater than $23,500. Assume a tax rate of 13% if IBT is less than $23.500 You are to calculane the monthly tax payment using the appropriate logical function Assume that sales will grow at an average of 2.25% per month. Assume that each month contains 4.2 weeks Scenario One: "What if Analysis Your client is unsure it 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 sels for 12:0 2 4 5 6 7 E y u S f h k O. O O odN + X Excel AssignmentDiner Templa A triend of yours wants to oper x me/chronos/u-9914918a51e620d01d47b7ba0be01992d5561706/MyFiles/Downloads/220DExcelDinersm19 %20(2) pd 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 atraid that he would cannibaize 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 lient offer beer to his customers? Scenario Two: "What if Analysis former school friend of your client is an amateur "pop band and has planted the idea in your clents head of adding a little music to provide live entertainment on the weekends (Friday and Saturday). The friend staees he can guarantee 7 more customers per hour if your clent will hire him at $1,950 a month What is your recommendation: Should the band be hired? Recommendations Show your clent how these recommendations would alffect the bottom ine by recreatings the pro forma tor each scenario, and applying the data analysis to determine proitability & 3 5 6 7 e t y C A4 X Exce AssignmentDinerTempla Atnd of yours wants to ope hronos/u-99149tBa5tes20d01d47b7babbe01992d55617d6/MyFiles/Downloads/2200ExcelDinersm19 % 20(2) pdf Scenario Two: "What if" Analysis A former school friend of your clent is an amateur "pop" band and has planted the idea in your clents head of adding a ite music to provide live entertainment on the weekends (Friday and Saturday). The friend states he can guarantee 7 more customers per hour i your clent wil hire him at $1.950 a month What is your recommendation: Should the band be hired? 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 proformas. 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 2-3 paragraph endeavor C 5 6 7 9 od N tn xX ExAssignmentDinerempla OAtend of yours wants to ope met mate O Fle homeichronos/99149Ba51e620d01647baobe01992d55617d6/My les/Downloads/2200ExcelDinersm19%20(2) pdf 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 comectly (e, appropriate tite, legend where appropriate, data series properly labeled) and they are appropriate for business use. Information needed to complete assianment Calamari, Oysters, Fried shrimp, Caish sandwiches, Salads, French fries, and sodas. Product Selling Prices: Oysters on hait shell will sell for s8.25 each (donen) Fried Shrimp will sell for $10.25 (dozen) Calamari will sell for $4.95 an order Catfeh Sandwich will sell for $5.95- Salads will sell for $4.50 each Fries sell for $1.25 per order Sodas sell for $1.75 a botle Cost of Goods Sold The Cafish sandwich ingredients (tried catfish, cheese, sauce, bun etc) cost $3.15 per sandwich Calamani cost per order $1.90. The fresh Shrimp cost $7.19 per order from the supplier Oysers (sauce, lemon, oysters) cost $5.19 per order Salad ingredents dettuce, tomatoes, cheese, e) cos $1.23 per salad serving Frozen tries and oil average to $.67 per onder Sodas cost about $.7 per 16 or. bomer The building rent is $2775 per month Phone will cost about $220 per month ctricity should cost about $775 a month Insur Adversing and pm s900 a month 11:5 C & 2 3 4 5 7 8 C r t O f h k C 6 m X AssignmentDemp A nd of yours wants to ope ematio home/chronos9914951e20d01647b7batbe0199205561766/Myles/Downloads/2200ExcelDinersm19%20(2) pd Operating Hours The diner will be open six days a week The diner will serve lunch and dinner and will be open from 11am-9pm on weekdays (Monday-Friday) t will need two hourly employee and an assistant manager (or manager) during these hours that the diner is open On Saturdays the restaurant will be open 11am-11pm and will need three hourly employees and an assistant manager (or manger). On Sundays the restaurant is only open dinner 4pm-8pm. assistant manager will be needed during these hours One hourly employee and an Your clent will be the manager and draw a salaly of $48,500 per year (includes benefies). He wil also work in the saore during the busiest fimes, and il in for the assistant manager on days off and sick days. The assistant manager will receive a salary of $37,500 per year inckudes benefes) The hourly workers will be paid $8.25 an hour Monday through Fridays the owner expects an average of 15 customers an hour Saturdays and Sundays the owner expects an average of 40 cusomers an hour The restaurant is located near the beach so there is alot of foot rafic mot days with Sunday being the slowest Demand Rate 1204 2 4 5 6 7 backspace 9 r t e y d f j k b V n X Exce Assgmentiner Tpla Atnd of yours wants to ope x ome/chronos/u9914918a51e20d01d47b7ba0be0199245561706/MyFiles/Downloads/2200ExcelDinersm19%20(2) pd Demand Rate On average, 1/4 of all customer will buy shrimp, 1/4 of the customers will buy oysters, 1/4 wi 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 fatures (chairs, tables etc.): $4,500 Oil painting of your clients momma to hang on the wall: $350 Licenses $1,025 Security deposit $6,900 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 appropriste financial function Assume a tax rate of 23% if Income Betore Taxes (BT) is equal to or is greater than $23,500. Assume a tax rate of 13% if IBT is less than $23.500 You are to calculane the monthly tax payment using the appropriate logical function Assume that sales will grow at an average of 2.25% per month. Assume that each month contains 4.2 weeks Scenario One: "What if Analysis Your client is unsure it 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 sels for 12:0 2 4 5 6 7 E y u S f h k O. O O odN + X Excel AssignmentDiner Templa A triend of yours wants to oper x me/chronos/u-9914918a51e620d01d47b7ba0be01992d5561706/MyFiles/Downloads/220DExcelDinersm19 %20(2) pd 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 atraid that he would cannibaize 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 lient offer beer to his customers? Scenario Two: "What if Analysis former school friend of your client is an amateur "pop band and has planted the idea in your clents head of adding a little music to provide live entertainment on the weekends (Friday and Saturday). The friend staees he can guarantee 7 more customers per hour if your clent will hire him at $1,950 a month What is your recommendation: Should the band be hired? Recommendations Show your clent how these recommendations would alffect the bottom ine by recreatings the pro forma tor each scenario, and applying the data analysis to determine proitability & 3 5 6 7 e t y C A4 X Exce AssignmentDinerTempla Atnd of yours wants to ope hronos/u-99149tBa5tes20d01d47b7babbe01992d55617d6/MyFiles/Downloads/2200ExcelDinersm19 % 20(2) pdf Scenario Two: "What if" Analysis A former school friend of your clent is an amateur "pop" band and has planted the idea in your clents head of adding a ite music to provide live entertainment on the weekends (Friday and Saturday). The friend states he can guarantee 7 more customers per hour i your clent wil hire him at $1.950 a month What is your recommendation: Should the band be hired? 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 proformas. 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 2-3 paragraph endeavor C 5 6 7 9 od N tn
