Need complete solution in excel for the following assignment. Thanks!
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. 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 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.
Assume a 4.2 week month.
Assume a tax rate of 30%.