Question

In: Economics

Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year...

Using Microsoft Excel, construct a monthly proforma cash budget for your client for the first year of operations.

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.75 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 six days a week.
The diner will serve lunch and dinner and will be open from 11am – 9pm on weekdays (Monday – Friday).
It 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.
One hourly employee and an assistant manager will be needed during these hours.
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 2 salary of $37,500 per year (includes benefits).
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 customers an hour.
The restaurant is located near the beach so there is alot of foot traffic most days with Sunday being the slowest.

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 23% if Income Before Taxes (IBT) 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 calculate 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.

Solutions

Expert Solution


Related Solutions

a Using the information provided, construct a monthly cash budget for October through December 2014. Based...
a Using the information provided, construct a monthly cash budget for October through December 2014. Based on your analysis, will Noble enjoy a surfeit of cash, or require external financing? b Construct a pro forma income statement for the first fiscal quarter of 2015 and a pro forma balance sheet as of December 31, 2014. What is your estimated external financine needed for December 31? c Does the December 31, 2014, estimated external financing equal your cash surplus (deficit) for...
Using Excel, Create a Household Budget Spread Sheet. Your spreadsheet should list monthly household budget and...
Using Excel, Create a Household Budget Spread Sheet. Your spreadsheet should list monthly household budget and various household expenses for 3 months. You will need to use a Totals Column or Row, and Formula to show if you are spending more money than you are bringing in or if you have a surplus! Also insert a Chart using any part of the data in your spreadsheet.
What are the causes of market failure in competitive markets? Using Microsoft Excel or PowerPoint, construct...
What are the causes of market failure in competitive markets? Using Microsoft Excel or PowerPoint, construct a graphical example of market failure.This should be embedded into a word document. (PLEASE ANSWER THIS QUESTION WITH AN ORIGINAL ANSWER. I HAVE RECEIVED ANSWERS FROM EXPERTS THAT HAVE JUST REPOSTED SOMEONE ELESE'S ANSWER. THIS IS NOT RIGHT. ALSO, PLEASE CONSTRUCT A GRAPH FOR EACH CAUSE AND PROVIDE AN EXPLANATION ABOUT IT. THANK YOU
Using, Microsoft Excel, how do you construct a 95% confidence interval for the difference in means?...
Using, Microsoft Excel, how do you construct a 95% confidence interval for the difference in means? I have two extremely long lists of numbers and need someone to explain the process.
Using Excel to Plot a Histogram This exercise is to be completed using Microsoft Excel and...
Using Excel to Plot a Histogram This exercise is to be completed using Microsoft Excel and will be turned in on Thursday, September 20th in class. You are to follow the steps we used in class on 9-13-2018 to construct the histogram for the data consisting of the ages of the 50 most powerful women in the world from 2012 (Source: Page 39 of textbook via Forbes Magazine). Your work should have columns for: 1. the raw data; 2. the...
(a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project Cash flows, NPV,...
(a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project Cash flows, NPV, IRR and PayBack Period (c) Develop Problem-Solving and Critical Thinking Skills 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) $   200,000 2) New equipment cost $ (200,000) 9) Sales increase per year 5% 3) Equipment ship & install cost $     (35,000) 10) Operating cost: $ (120,000) 4) Related start up cost $       (5,000)     (60 Percent of...
Using Microsoft Excel, create an investment cash-flow diagram that will have a present worth of zero...
Using Microsoft Excel, create an investment cash-flow diagram that will have a present worth of zero at MARR = 7%. The study period needs to be exactly 18 years and each year should have at least one unique cash-flow that is different from the cash-flows over the other years. Your answer should contain a table showing the cash-flows for each year and a graphical representation of the cash-flows (cash-flow diagram).
What, if any, are your fear about using Microsoft Excel? If you have no fears, and...
What, if any, are your fear about using Microsoft Excel? If you have no fears, and /or have used it before, what can you tell your class members about your experience
On this topic, you are required to prepare your monthly cash budget for the month of...
On this topic, you are required to prepare your monthly cash budget for the month of June 2020, if (assuming) there are 4 different sources of income and 12 different expenses and debt repayments. “Preparing monthly cash budget is an indicator for your personal financial growth’’. In reference to this statement, you are required to provide the reasons/importance of preparing your monthly cash budget. (1200 words) Note: Please write 1200 words
Generate and provide your answers to ALL questions using a Microsoft Excel Worksheet(s) and submit this...
Generate and provide your answers to ALL questions using a Microsoft Excel Worksheet(s) and submit this Worksheet as a separate document with your Word document file. Note: You may answer this question in two or more Excel Worksheets – one Worksheet containing the table and the other Worksheet containing all the plots. Alternatively, you may choose to provide each plot in a separate Worksheet.   Demonstrate how the spectral emissive power inside a blackbody cavity changes with respect to the following...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT