Can someone assist with this before 8am?? I have a majority complete but am struggling with the recommendations + graph. Thanks so much!

Smoothie Doozie A friend of yours wants to open a store that sells smoothies. You volunteer to help your friend develop a pro forma cash budget -- this is a document that helps forecast income and expenses over a period of time, and can be used to manage the business if done correctly. Your friend tells you the following: Smoothies range in price from $2.50 - $4.50. The average price is $3.15 The ingredients fruit, ice, syrup, sherbert, cups, and straws cost about $0.95 per smoothie. The store rent is $1,750.00 per month. Phone costs $65.00 per month. Electricity should cost about $135.00 a month. Insurance will be $110.00 a month. Advertising and promotion is planned to start out at $1,200.00 a month and will change to 13% of sales in the sixth month. Health food will also be sold at the store, and the markup will be 100%. The store will be open 11 hours a day and will need one hourly employee and an assistant manager during each of the hours that the store is open. On Fridays and Saturdays the store will need two hourly employees and an assistant manager. Your friend will be the manager and draw a salary of $27,000.00 per year (includes benefits). Your friend 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 $19,000.00 per year (includes benefits). The hourly workers will be paid $7.50 an hour. The store will be open six days a week (closed on Sunday). Sundays through Thursdays the owner expects an average of 9 customers an hour. Fridays and Saturdays, the owner expects an average of 18 customers an hour. Each customer will buy one smoothie. 53% of the customers will purchase a health food snack. Average purchase price for the snack is $2.15. Start up costs for the store include: Machinery $16,500.00 Initial health food inventory (replenished as used). - $1,350.00 Initial stock of smoothie (replenished as used). - $1,900.00 Pre-opening advertising - $2,300.00 Store fixtures (counter, chairs, tables etc.) - $11,000.00. Licenses - $500.00 Rent Deposit - $1,750.00 Fist Insurance Payment - $110.00 Your friend has $18,000.00 and plans to borrow the rest from the bank with a five year loan at 5% interest. Assume that sales will grow at 6% per month. 1. Construct a monthly pro-forma cash budget for your friend for the first year of operations. a. Place all your assumptions on one worksheet. Name the worksheet "Assumptions" b. Place your start up costs on a second worksheet named "Startup Costs" c. Place the cash proforma on another worksheet named Cash Pro-forma Budget" 2. How much money should your friend borrow? Explain your reasoning. Include your loan calculations. 3. When is the dollar breakeven point (e.g., Month 1, 2, etc.)? Do not calculate the amount. 4. Graph the Revenue and the Net Income after Taxes. Name this worksheet "Graph" 5. Suggest three reasonable business recommendations to your friend. Show your friend how these recommendations would affect the cash budget. Use a text box to explain your changes. Name the worksheet "Recommendations" 6. Type the answers to Questions 2, 3 and 5 in a Worksheet labeled \"Answers.\" No constant numbers should be used on the worksheets. In other words, don't type raw numbers in the cells. Use formulas that refer to cells rather than the values in those cells. Assume a 4.2 week month. Assume a tax rate of 30%. Smoothie Doozie WMBA 6070 Entrepreneurship Due: September 26, 2016 not later than 6:00 pm. Submit using the DropBox tool. This should be in the form of an Excel Workbook as instructed. Background A friend of yours wants to open a retail store that sells smoothies. You volunteer to help your friend develop a pro forma cash budget -- this is a document that helps forecast income and expenses over a period of time, and can be used to manage the business if done correctly. You will receive an email with the specific information from your friend on or about the first day of class. Each of you will have different numbers and a different set up. Instructions 1. Construct a monthly pro-forma cash budget for your friend for the first year of operations. a. Place all your assumptions on one worksheet. Name the worksheet "Assumptions" b. Place your start up costs on a second worksheet named "Startup Costs" c. Place the cash proforma on another worksheet named \"Cash Pro-forma Budget" 2. How much money should your friend borrow? Explain your reasoning. Provide your loan calculations using the Excel formula. 3. When is the dollar break even point? (Profits = zero). Use a text box to identify it. 4. Graph the Revenue and the Net Income after Taxes. Name this worksheet "Graph" 5. Suggest three (3) reasonable business recommendations to your friend. Show your friend how these recommendations would affect the cash budget (new spreadsheet). Create a new cash pro forma budget worksheet that incorporates all of the recommendations. Name the worksheet "Recommendations." 6. Type your answers to questions 2, 3 and 5 in a Worksheet labelled \"Answers.\" No constant numbers should be used on the worksheets. All constants should be limited to the Assumptions worksheet or the Startup Costs worksheet. This will allow you to do \"what-if\" or sensitivity analysis. Assume a 4.2 week month. Don't forget to multiply weekly sales by 4.2 when calculating monthly sales. Use the Excel file in the assignment tool as a template to help you get started. Do not change the items in the file. Use the same labels so it is easy to identify all of the required items and values. Grading Rubric: All information cells are referenced back to the assumption page and set up correctly...............................................................................30 points Loan payment is calculated using a function........................................10 points Recommendations are based on spread sheet \"what if\" analysis..........20 points The chart is present and reflects worksheet content............................. 10 points Taxes are calculated correctly using IF statement ................................10 points Twelve months are presented and an annual total is given.................. 20 points 1. Do not type raw data in each cell including, but not limited to, price of smoothie, cost of goods sold, interest rates, number of customers per hour, advertising percentage of sales, etc. 2. Refer to the Discussion Board for constructive advice on completing the technical aspects of this assignment (i.e., Excel formulas). Average Smoothie Price Cost of Goods Health food Health food CGS Fixed Costs Rent Phone Electricity Insurance Advertising Months 1-5 Months 6-12 Hours open Sunday - Thursday Friday and Saturday Hourly wage Asst. Managers' salary Manager's salary Customers per hour M - Th Customers per hour F - S Percent customers purchasing snacks Growth per month Loan period Interest rate Tax Rate Start Up Costs: Machinery Initial health food inventory (replenished as used).Initial stock of smoothie (replenished as used). Pre-opening advertising Store fixtures (counter, chairs, tables etc.) - . Licenses Rent deposit First Insurance payment Total Owner's Equity (Funds available from Owner) Do not forget reserves based on cash flow Loan Amount This extra money will become Beginning cash on the Cash P Assume initial cash reserves of $4,000. Since I have more than one set of values that are sent, you will find you have to create an initial 12-month cash budget to get an idea if you need to borrow more money (need a cash reserve of more than $4,000) to cover possible losses (some of you will might take longer to get a positive monthly income than others). * There are over 35 combinations of the data you are given (e.g., price of Smoothie, advertising, rent, Owner's Equity, etc.). cash budget to get an idea if you need to borrow more money (need a cash reserve of more than $4,000) to cover possible losses (some of you will might take longer to get a positive monthly income than others). * There are over 35 combinations of the data you are given (e.g., price of Smoothie, advertising, rent, Owner's Equity, etc.). cash on the Cash Pro-Forma Budget Worksheet. Cumulative Cash Flow becomes Cash Balance in the following month. Month 1 Month 2 Month 3 Month 4 Beginning Cash/ Cash Balance Revenue Smoothies Health Food TOTAL Expenses CGS Rent Phone Electricity Insurance Advertising Wages Salaries Loan Payment TOTAL EXPENSES Net Cash Flow Before Tax Tax Net Income Monthly Cash Flow Cumulative Cash Flow This row shows you the cumulative cash flow you have from month-to-month. If you find after your first attempt to create the 12-month cash budget you have a negative cumulative cash flow, you are going to have borrow more money to cover your cash losses. In other words, you might need more than a $4,000 cash reserve which is also your Beginning Cash in Cell B5 above. Since this is a cash pro-forma, you may ignore depreciation and interest expense. Smoothie Doozie Template Month 5 m month-to-month. If you dget you have a negative money to cover your cash cash reserve which is also Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Total List and explain your two recommendations and include a NEW spreadsheet below that incorporates them. t incorporates them. Answers: Question 2 Question 3 Question 5