PLEASE USE EXCEL

Scenario One

A new client of yours, Ray Marquez, wants to open a kiosk at the local mall selling smoothies, specifically fruit smoothies, protein smoothies, green smoothies (kale, vegetables, etc.), muscle building smoothies, fat burner smoothies, and French fries.

You, as an independent consultant, have been hired to analyze your clients ideas and help him determine whether his plan looks to be profitable or not.

You will do this by developing a pro-forma cash budget for his business venture (note: a pro-forma cash budget is a financial document that helps forecast income and expenses over a period of time). If done correctly, it can be used to plan for and manage the business.

He is planning to open his business on the 1^st of October 2020 in order to take advantage of the start of the holiday season.

Assignment:

Using a financial spreadsheet application (Microsoft Excel), construct a monthly

pro-forma cash budget for your clients first year of operations.

Use the file called Smoothie Shack Template for your starting point. This is the file that must be downloaded and used as the basis for your assignment. Do not make any changes to this pre-designed template other than instructed.

You will place your proforma analysis on a worksheet labeled Cash Proforma".

Place all your case assumptions data on a separate worksheet and label the worksheet "Case Assumptions.

Place your startup costs on a third worksheet labeled "Startup Costs"

Create an additional worksheet for your what-if scenario analysis. Label the worksheet What-If Scenario.

Charts (graphs) You will be creating two appropriate charts so create and label two additional worksheets for the charts. The labels should be descriptive regarding their content. Each chart will be in its own worksheet.

Chart One Create a chart showing the total revenue for each product for the year. This chart will be used to compare each products revenue contribution to the total revenue.

Chart Two: Create a chart showing the monthly net income for the year. This chart will be used to determine any sales trends.

Make sure both charts are formatted properly (i.e. appropriate title, legend where appropriate, data series properly labeled, etc.), and are appropriate for business use.

Assumptions provided by your client:

Product Selling Prices:

Fruit smoothies will sell for $5.95 Protein smoothies will sell for $6.25

Green smoothies will sell for $5.25

Muscle building smoothies will sell for $5.50 Fat burner smoothies will sell for $5.50

French fries will sell for $1.95

Cost of Goods Sold (COGS):

Fruit smoothies = $1.78 Protein smoothies = $1.82

Green smoothies = $1.10

Muscle building smoothies = $.95 Fat burner smoothies = $.85

French fries = $.75

Fixed Expenses:

Mall kiosk rent = $2,200 per month.

Communication expenses (phone, Internet, Wi-Fi, etc.) = $1,150 per month.

Utilities = $1,500 a month.

Insurance = $900 a month.

Advertising/promotion = $500 a month.

Operating Hours:

Your client plans on being closed on Mondays. During the week (Tuesday Friday), the kiosk will be open from 11am 9 pm. It will need two hourly employees and a manager during these hours.

On the weekends (Saturday and Sunday), the kiosk will be open 11am 11 pm and will need three hourly employees and a manger. Your client will be the manager and draw a salary of $35,000 per year. 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 an annual salary of $28,500. The hourly workers will be paid $8.50 an hour.

During the week, the owner expects an average of 10 customers an hour. During the weekend, the owner expects an average of 25 customers an hour.

Demand Rate:

Your client believes that on average, every other customer will buy a Fruit Smoothie, 3/4 will buy a Protein Smoothie, a quarter of them will buy a Green Smoothie, one out of every four will purchase a Muscle Building Smoothie, one out of 5 will purchase a Fat Burning Smoothie, and every customer will purchase French fries.

Startup costs for the diner includes:

Kitchen equipment: $250,000

Sales support equipment: $50,000

Beginning inventory: $3,500

Pre-opening marketing: $2,000

Licenses: $2,500

Security deposit: $4,000

First Insurance Payment: $900

Your client has $12,000 and plans to borrow the rest from the bank with a 7 year loan at 6.85% interest.

(Use the PMT financial function to calculate the monthly loan payment)

Assume a tax rate of 15% if Income Before Taxes (IBT) is equal to or is greater than $25,500. Assume a tax rate of 12% if IBT is less than $25,500.

(Use the IF logical function to calculate the monthly tax payment)

Your client feels that once the business gets going, monthly sales for all products will grow an average of .75 % per month starting in November. With the spring season coming in, he feels the growth will jump to 1.25% starting in March and remain at that level for the remainder of the fiscal year.

Assume that each month contains 4.2 weeks.

Cash Proforma Analysis:

Show your client how his business ideas will affect his bottom line by creating a cash proforma for this scenario. Apply and show your data analysis in regards to how it affects profitability.

Scenario Two: What if Analysis

Your client recently ran into a former high school friend of his who operates her own marketing promotion company. Her company specializes in working with small businesses and has ensured him that she can guarantee his business 7 extra customers an hour if he enters into an annual marketing agreement with her company. Furthermore, because he is an old acquaintance, she will give him a discount on the annual agreement. The discounted price will be $2,400/mo. This annual agreement would be in addition to your clients monthly Advertising and Promotion expense.

What is your recommendation: Is it profitable to enter into this annual marketing agreement?

(Hint): you do not have to start this new pro-forma from scratch, but note, it is a completely independent pro-forma. This new pro-forma (worksheet) must update accordingly from the data worksheets.

Use a formatted text box (not a comment) to explain your recommendations. This will be approximately a 2-3 paragraph write-up. The recommendation should be complete, concise, and correct (use Spell Check).