all I need you to do is follow the instructions

Sprinkles Ice Cream Shoppe

Please read the instructions carefully.

A new client of yours, Lilly Waters wants to open an ice cream shop near the downtown shopping center. She will sell ice cream cones or in a cup, shakes, sundaes, and soda. Her grand opening will be January 1, 2022.

LIlly needs help figuring out how her business will perform the first year in business. She has hired you as an independent consultant, prepare a cash proforma budget for her business venture . This is a spreadsheet that helps forecast income and expenses over a period of time).It can be used to plan 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 file attached Excel Template – “Sprinkles” as your starting point. Download and use this file as the basis for your assignment. Do not make any changes to this pre-defined template items (this means start with the items that already included and that are expected in the spreadsheet). You may add your information to the existing sheets. You may add extra worksheets as needed as well update the template.

Do not use a template from a previous semester – this is academic dishonesty and will be subject to disciplinary action.

  • Place the finished cash pro forma on a worksheet labeled “Cash ProForma”.
  • Place all your case assumptions data on a separate worksheet. Label the worksheet “Assumptions” (note: each piece of data must appear in its own cell on the Assumption sheet).
  • Place your start-up costs on a third worksheet labeled “Startup Costs”
  • Create two additional worksheets for your recommendations. Ice Cream Cakes and IcedCoffee.
  • 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).

page1image43093952

1

  • Chart One –”Monthly Product Revenue” – this will show the monthly revenue for each of your five products for the entire year.
  • Chart Two – “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.
  • 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.Information needed to complete assignment:Products: Sugar cones, ice cream cups, milkshakes, sundaes, toppings, soda and bottle water.Product Selling Prices:I scoop Cup or Sugar Cone – $2.25
    2 scoops Cup or Sugar Cone – $3.25 3 scoops Cup or Sugar Cone – $4.25I scoop Sugar Cone – $2.25
    2 scoops Sugar Cone – $3.25 3 scoops Sugar Cone – $4.25Small Shake – $2.95 Medium Shake – $3.75 Large Shake – $4.50Small Sundae (1 topping) – $2.55 Medium Sundae (1 topping) – $3.55 Large Sundae (1 topping) – $4.65Additional topping: .50 per toppingSoda -$1.25 a bottle
    Bottled Water – $1.00 a bottleCost of Goods Sold:Ice Cream Cones (Ice cream ingredients and cone) cost $1.10 for 1 scoop and .20 cents extra for each additional scoop
    Ice cream Cups (Ice cream ingredients and cup) cost $1.25 and .20 cents per extra for each additional scoop

Small Shake cost $1.40 Medium Shake cost $1.55 Large Shake cost $1.75 Small Sundae cost $1.35

1

Medium Sundae cost $1.50 Large Sundae cost $1.75 Additional Toppings cost $.10

Sodas cost about $.7 per 16 oz. bottle Water cost $.6 per 16oz bottle

The building rent is $2500 per month. Phone/Internet will cost about $1000 per month.

Electricity should cost about $1200 a month. Insurance will be $900 a month.
Advertising and promotion will be $800 a month.

Operating Hours:

Sprinkles will be open seven days a week.
Sprinkles will serve ice cream products all day and will be open

11am – 8 pm on weekdays (Monday – Thursday, Sunday). 11am – 11pm Friday, Saturday
Employees:

  • Two hourly employees on Friday, Saturday
  • One hourly employee needed during Monday – Thursday, Sunday
  • Either an assistant manager or manager is required during the Sprinkles Operating hours.
  • Your client will be the manager and draw a salary of $35,000 per year (including benefits). She will work during the busiest times and fill in for the assistant manager. Approximately 40%.
  • The assistant manager will receive a salary of $22,400 per year(including benefits). Approximately 60%.
  • Hourly workers will be paid $8.75 an hour. Demand Rate:
    • Monday – Thursday, Sunday the owner expects 10 customers per hour.
    • Friday – Saturday the owner expects an average of 20 customers per hour..

1

Product demand on average:

o 1/2 of all customers will buy 1 scoop Ice Cream Cones o 1/2 of all customers will buy 2 scoop Ice Cream Cones o 1/3 of all customer will buy 3 scoop Ice Cream Cones o 1/2 of all customers will buy 1 scoop Ice Cream cups o 1/2 of all customers will buy 2 scoop Ice Cream cups o 1/3 of all customer will buy 3 scoop Ice Cream cups

o 1/4 of all customers will buy Medium Shakes
o 1/4 of all customers will buy Large Shakes
o 1/2 of all customers will buy Small shakes
o 1/4 of all customers will buy a Large Sundae
o 1/3 of all customers will buy a Medium Sundae o 1/4 of all customers will buy Small Sundae

o 3/4 of all customers will buy a Soda
o 1/4 of all customers will buy a Bottled Water
o 1/4 of all customers will buy an additional topping

Kitchen equipment: $10,250
Cash register and sales equipment: $1,350
Initial inventory: $4,500
Pre-opening marketing: $1,500
Store fixtures (chairs, tables etc.): $4,500
Oil paintings of your client’s momma and grandma to hang on the wall: $350 Licenses: $1,025 Security deposit: $4,500
First Insurance Payment: $750

Your client has $8,000 and plans to borrow the rest from the bank with a five-year loan at 2.5% interest. You are to calculate the monthly loan payment using the appropriate financial function.

Assume a tax rate of 21% if Income Before Taxes (IBT) is equal to or is greater than $13,500. Assume a tax rate of 13% if IBT is less than $13,500. You are to
calculate the monthly tax payment using the appropriate logical function.

Assume that sales will grow at an average of 1.50% per month. Assume that each month contains 4.2 weeks.

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.

1

Start-up costs

You do not have to start from scratch, but note, these are completely independent pro formas. They must update accordingly from the data worksheets.

Plan on showing your analysis and discussing the proforma 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 2-3 paragraph endeavor.

Scenario One: “What if” Analysis for adding flavored ice coffees.

Your client is unsure if she should sell flavored ice coffees. She thinks she can sell a coffee to every second customer and it seems to be lucrative because the coffee sells for $3.75 each and costs him only $1.60 to purchase.

Unfortunately your client is afraid that he would cannibalize his soft drink sales with the coffee customers (one soft drink less for every coffee sold). It will cost him $5,250 to purchase the equipment and insurance costs would rise by another $155 per month due to the hot equipment needed to make the coffee.

What is your recommendation: Should your client offer flavored coffee to her customers?

Scenario Two: “What if” Analysis

Your client would like to consider adding ice cream cakes to the list of items available for sale. She is wanting to offer cakes with customized cake, ice cream decorations.

She thinks she can sell 20 small cakes a week and 25 large cakes a week. It seems to be lucrative because the small cake is $16.95 and the large cake is $23.95. Costs are $5.45 per small cake and $7.40 per large cake.

It will cost her $3,250 to purchase the equipment and insurance costs would rise by another $255 per month due to the hot equipment needed to make the cakes.

What is your recommendation? Would it be profitable to sell the cakes?

Calculate the price
Make an order in advance and get the best price
Pages (550 words)
$0.00
*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 Lets Write For You
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.
Testimonials
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.
11,595
Customer reviews in total
96%
Current satisfaction rate
3 pages
Average paper length
37%
Customers referred by a friend
OUR GIFT TO YOU
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