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. (please reach out to me niawhite007 gmail

Average Smoothie Price Cost of Goods Health food Health food CGS Fixed Costs Rent Phone Electricity Insurance Advertising hours open Sunday - Thursday Friday and Saturday Hourly wage Asst. Managers' salary Manager's salary 3.35 1.15 4.3 2.15 1650 85 120 150 1250 10 10 7.75 20,000 24,000 Total Customers Customers-Smoothies Customers-Health Snacks 3024 1481.76 1542.24 Percent purchasing health 51% Growth per month 6% Loan period Interest rate 5 9% Tax Rate 28% Start Up Costs: Machinery 15,500 Initial health food inventory (replenished as use 1,350 Initial stock of smoothie (replenished as used). 2,000 Pre-opening advertising Store fixtures (counter, chairs, tables etc.) - . 2,100 9,000 Licenses Rent deposit First Insurance payment 200 1,650 150 Total Owner's Equity Do not forget reserves based on cash flow Loan Amount 31,950 Month 1 Month 2 Month 3 Revenue Smoothies Health Food 4963.896 TOTAL Expenses CGS Rent Phone Electricity Insurance Advertising Wages Salaries Loan Payment TOTAL EXPENSES Net Income Before Tax Tax Net Income Cash Flow Since this is a cash pro-forma, you may ignore depreciation. Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Total Advertising Factor Growth rate Month 1 Revenue Smoothies Health Food TOTAL Expenses CGS Rent Phone Electricity Insurance Advertising Wages Salaries Loan Payment TOTAL EXPENSES Net Income Before Tax Tax Net Income Cash Flow Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Total 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.35 The ingredients fruit, ice, syrup, sherbert, cups, and straws cost about $1.15 per smoothie. The store rent is $1,650.00 per month. Phone costs $85.00 per month. Electricity should cost about $120.00 a month. Insurance will be $150.00 a month. Advertising and promotion is planned to start out at $1,250.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 110%. The store will be open 10 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 $24,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 $20,000.00 per year (includes benefits). The hourly workers will be paid $7.75 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. 51% 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 $15,500.00 Initial health food inventory (replenished as used). - $1,350.00 Initial stock of smoothie (replenished as used). - $2,000.00 Pre-opening advertising - $2,100.00 Store fixtures (counter, chairs, tables etc.) - $9,000.00. Licenses - $200.00 Rent Deposit - $1,650.00 Fist Insurance Payment - $150.00 Your friend has $18,000.00 and plans to borrow the rest from the bank with a five year loan at 9% 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. What is the dollar breakeven point? 4. Graph the Revenue and the Net Income after Taxes. Name this worksheet "Graph" 5. Double the marketing and advertising. Assume this increases the growth rate to 9%. Would this be a good business decision? Why or why not? Name your worksheet "Double" 6. Suggest two reasonable business recommendations to your friend. Show your friend how these recommendations would effect the cash budget. Use a text box to explain your changes. Name the worksheet "Recommendations" No constant numbers should be used on the worksheets. Assume a 4.2 week month. Assume a tax rate of 28%. Smoothie Doozie Submit using the DropBox tool. This should be in the form of an Excel Spreadsheet with comments and notes 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. When is the dollar break even point? (Profits = zero). Use a text box to identify it. 3. Graph the Revenue and the Net Income after Taxes. Name this worksheet "Graph" 4. Double the marketing and advertising. Assume this increases the growth rate to a number that will be given to you in your email. Would this be a good business decision? Why or why not? Name your worksheet "Double" 5. Suggest two 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" 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\" analysis. Assume a 4.2 week month. 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...............................................................................3% of points Loan payment is calculated using a function........................................1% of points Recommendations are based on spread sheet \"what if\" analysis..........1% of points The double advertising question is answered.......................................2% of points The chart is present and reflects worksheet content.............................1% of points Taxes are calculated correctly using Boolean logic..............................1% of points Twelve months are presented and an annual total is given..................1% of points Smoothie Doozie Submit using the DropBox tool. This should be in the form of an Excel Spreadsheet with comments and notes 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. When is the dollar break even point? (Profits = zero). Use a text box to identify it. 3. Graph the Revenue and the Net Income after Taxes. Name this worksheet "Graph" 4. Double the marketing and advertising. Assume this increases the growth rate to a number that will be given to you in your email. Would this be a good business decision? Why or why not? Name your worksheet "Double" 5. Suggest two 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" 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\" analysis. Assume a 4.2 week month. 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...............................................................................3% of points Loan payment is calculated using a function........................................1% of points Recommendations are based on spread sheet \"what if\" analysis..........1% of points The double advertising question is answered.......................................2% of points The chart is present and reflects worksheet content.............................1% of points Taxes are calculated correctly using Boolean logic..............................1% of points Twelve months are presented and an annual total is given..................1% of points