Excel help and analysis!! easy


Excel Project Assignment (100 Points) Danielle’s Diner BEFORE YOU BEGIN:  Read all instructions completely before starting this assignment.  You must use Excel to complete the assignment and you must start with the template provided on D2L attached to this assignment in the Dropbox. Backstory: A new client of yours, Danielle Stevens, wants to open a small seafood restaurant selling oysters, fried shrimp, calamari, French fries, and sodas. She is planning to open the restaurant on September 1, 2015. You, as an independent consultant, have been hired to develop a cash pro forma budget (this is a spreadsheet that helps forecast income and expenses over a period of time) for her business venture. 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 Excel Template – “Updated – Business Case – Excel Assignment” – as your starting point. This is the file that must be downloaded and used as the basis for your assignment.  Before getting started, please save the template as YourLastName_Excel Assignment Do not make any changes to this pre-designed template. Do not use a template from a previous semester – this is considered academic dishonesty and will be subject to disciplinary action. Labeling 1. Place the finished cash pro forma on a worksheet labeled “Cash ProForma”. 2. Place all your case assumptions data on a separate worksheet. a. Label the worksheet “Assumptions” (note: each piece of data must appear in its own cell on the Assumption sheet). 3. Place your start-up costs on a third worksheet labeled “Startup Costs” 4. Create two additional worksheets for your recommendations. a. Label one worksheet “Beer Recommendation”, label the other “Entertainment Recommendation”. 5. 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).2 a. Chart One –”C1 Monthly Product Revenue” – this will show the monthly revenue for each of your six products for the entire year. b. Chart Two – “C2 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. Notes: The charts only need to depict information from the original cash proforma. 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. Assumptions 1. Product Selling Prices a. Oysters on half shell will sell for $12.25 each (dozen) b. Fried Shrimp will sell for $8.25 (dozen) c. Calamari will sell for $7.95 an order d. Fries sell for $4.25 per order e. Sodas sell for $1.00 a bottle 2. Cost of Goods Sold a. Oysters (sauce, lemon, oysters) cost $6.19 per order. b. The fresh Shrimp cost $7.15 per order from the supplier. c. Calamari cost per order $3.90. d. Frozen fries and oil average to $.85 per order e. Sodas cost about $.75 per 16 oz. bottle 3. Fixed Costs a. The building rent is $5772 per month. b. Phone will cost about $245 per month. c. Electricity should cost about $825 a month. d. Insurance will be $900 a month. e. Advertising and promotion will be $1,250 a month. 4. Operating Hours: a. The diner will be open six days a week (closed on Monday). b. The diner will serve lunch and dinner and will be open from 11am – 7pm on weekdays (Tuesday – Friday). It will need one hourly employee and an assistant manager (or manager) during these hours that the diner is open. c. On Saturdays and Sundays the store will be open 11am – 11pm and will need two hourly employees and an assistant manager (or manger). 5. Salaries a. Your client will be the manager and draw a salary of $52,575 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. b. The assistant manager will receive a salary of $33,500 per year (includes benefits). c. The hourly workers will be paid $7.25 an hour.3 6. Customers a. Tuesday through Fridays the owner expects an average of 20 customers an hour. b. Saturdays and Sundays the owner expects an average of 45 customers an hour. 7. Demand Rate: a. 3 out of 4 customers will buy shrimp b. 75% of the customers will buy oysters c. 1/4 will buy Calamari d. All of the customers will buy French fries e. Every customer will purchase a soda. 8. Sales Assumptions a. Assume that sales will grow at an average of 2.15% per month. b. Assume that each month contains 4.2 weeks. Start-up Costs 1. Diner a. Kitchen equipment: $19,275 b. Cash register and sales equipment: $1,350 c. Initial inventory: $5,575 d. Pre-opening marketing: $3,500 e. Diner Fixtures (chairs, tables etc.): $4,500 f. Mini sculpture of your client’s favorite fish to hang on the wall: $350 g. Licenses: $1,725 h. Security Deposit: $6,000 i. First Insurance Payment: $900 2. Financing a. Your client has $15,000 and plans to borrow the rest from the bank with a five-year loan at 5.1% interest. b. You are to calculate the monthly loan payment using the appropriate financial function. Danielle is applying for a $52,000 loan from the bank. c. 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. Breakeven Point 1. The breakeven point needs to be calculated for the 5 products in the original cash proforma. a. This information should appear on the assumptions page. 2. Create a graph to depict the breakeven point a. This information should appear on the same worksheet as chart 1. NOTE: As with the other charts, the BEP graph should be formatted correctly (i.e. appropriate title, legend where appropriate, data series properly labeled) and appropriate for business use.4 “What If” Scenarios 1. Scenario One: “What if” Beer Analysis Your client is unsure if he should sell beer at the diner. He thinks he can sell a beer to every second customer (except on Sundays when no beer can be sold) and it seems to be lucrative because the beer sells for $4.75 each and costs him only $2.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 $6,575 to purchase the beer license from the county and his monthly insurance costs would rise by another $200 per month. What is your recommendation: Should your client offer beer to his customers? 2. Scenario Two: “What if” Entertainment Analysis A former school friend of your client is an amateur comedian and has planted the idea in your client’s head of adding a comedy act to provide live entertainment on the weekends (Saturday and Sunday). The friend states he can guarantee 10 more customers per hour if your client will hire the comedian at $2,570 a month. What is your recommendation: Would it be a profitable idea to hire the trio? 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 forma’s. They must update accordingly from the data worksheets. Plan on showing your analysis and discussing the pro forma 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 3 paragraph endeavor; each paragraph should be clearly defined and a minimum of 4 sentences long.

Calculate the price
Make an order in advance and get the best price
Pages (550 words)
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Upload your instructions
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with Australia Assessments
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
Writer did do a good job in answering my question and assignment!
Customer 454777, July 8th, 2020
Social Work and Human Services
Good work.
Customer 462793, July 8th, 2022
Excellent work.
Customer 459155, June 7th, 2022
Business and administrative studies
Commendable work.
Customer 453375, April 28th, 2022
The work is of really good quality and was done on time.
Customer 454439, June 1st, 2020
Business and administrative studies
The grade was 100%- Oh man I will be back again. Thank you.
Customer 463293, April 27th, 2023
ASCI 491: Operational Applications in Aeronautics
Well articulated.
Customer 457731, April 12th, 2022
Satisfactory work.
Customer 456821, April 14th, 2022
Home Repair & Home Maintenance
Good job!
Customer 463337, November 21st, 2022
Health Care
Good job
Customer 454209, June 2nd, 2020
English 101
Impeccable output.
Customer 462717, April 20th, 2022
Excellent work
Customer 456821, February 26th, 2022
Customer reviews in total
Current satisfaction rate
3 pages
Average paper length
Customers referred by a friend
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat