Rubric Criteria:
Your current score:
Sheets are appropriately labeled
5 points
All information cells on cash proforma are referenced back to the
assumption page – Formulas and functions, calculations are correct, all
fields have values, twelve months are presented and an annual totals are
given as well as a monthly totals. Hard coded numbers even if correct will
result in zero points.
Assumptions and startup worksheets setup and complete.
40 points
10 points
Recommendation are based on cash proforma information and what if
analysis. Recommendation is typed in text box – minimum 4 sentences and
are based on cash proforma information and what if analysis – Alternative
scenarios must be shown as proformas with the changes.
15 points
The charts are present and reflect worksheet content.
10 points
Taxes are calculated correctly using boolean logic and appropriate
calculation.
10 points
Loan payment is calculated using a function..
10 points
Score (out of 100)
0
Revenue:
January February
March
April
May
Fruit Explosion POPS
Vanilla Vanilla POPS
Chocolate Mousse POPS
Mango Tangerine POPS
Strawberry Crème POPS
Pina Colada POPS
Strawberry Kiwi POPS
Orange Crème POPS
Chocolate Sea Salt POPS
Cookies & Crème POPS
12 Pack POPS
6 Pack POPS
Medium Frozen Ice
Large Frozen Ice
Soda
Bottled Water
Monthly Revenue
Expenses:
COGS
January
February
March
April
May
January
February
March
April
May
Rent
Phone
Electricity
Insurance
Advertising
Hourly Wages
Salaries
Loan Payment
Total Expenses
Income Before Tax
Tax
Net Income
Cash Flow
TPNI/Gross Margin
June
July
August
September
October
November
June
July
August
September
October
November
June
July
August
September
October
November
December
Total
December
Total
December
Total
Formulas:
Products: (Retail Price) X Amount purchesed per Month
Assumptions Made:
Products: (Retail Price)
Fruit Explosion POPS
Vanilla Vanilla POPS
Chocolate Mousse POPS
Mango Tangerine POPS
Strawberry Crème POPS
Pina Colada POPS
Strawberry Kiwi POPS
Orange Crème POPS
Chocolate Sea Salt POPS
Cookies & Crème POPS
12 Pack POPS
6 Pack POPS
Medium Frozen Ice
Large Frozen Ice
Soda
Bottled Water
COGS
Fruit Explosion Pops COGS
Vanilla Vanilla Pops COGS
Chocolate Mousse Pops COGS
Mango Tangerine Pops COGS
Strawberry Crème Pops COGS
Pina Colada Pops COGS
Strawberry Kiwi Pops COGS
Orange Crème Pops COGS
Chocolate Sea Salt Pops COGS
Cookies & Crème Pops COGS
12 Pack Pops COGS
6 Pack Pops COGS
Medium Frozen Ice COGS
Large Frozen Ice COGS
Soda COGS
Bottled Water COGS
Fixed Costs:
Rent
Phone
Electricity
Insurance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
4.85
3.85
4.35
4.35
3.75
4.85
4.35
3.75
4.35
3.25
32.00
15.00
3.75
4.25
1.95
1.25
January
$
0.90
$
0.40
$
0.95
$
0.70
$
0.70
$
0.90
$
0.80
$
0.50
$
0.95
$
0.70
$
7.20
$
4.20
$
0.95
$
1.10
$
0.90
$
0.08
TOTAL COGS
$
$
$
$
3,600
1,000
1,300
1,000
Advertising
Operating Information:
# of days open (weekdays)
# of days open (weekends)
Hours open (weekdays)
Hours open (weekends)
Hourly wage
Customers per hour weekdays
Customers per hour weekends
Hourly employees/day (weekdays)
Hourly employees/day (weekends)
$
Customers per Week/Month
Cust Week Day
Cust Weekend Day
Cust per Week
Cust per Month
4
2
8
12
$
12.50
18
30
1
2
Customers Buying Products
% of Customers Purchasing Fruit Explosion POPS
% of Customers Purchasing Vanilla Vanilla POPS
% of Customers Purchasing Chocolate Mousee POPS
% of Customers Purchasing Mango Tangerine POPS
% of Customers Purchasing Strawberry Crème POPS
% of Customers Purchasing Pina Colada POPS
% of Customers Purchasing Strawberry Kiwi POPS
% of Customers Purchasing Orange Crème POPS
% of Customers Purchasing Chocolate Sea Salt POPS
% of Customers Purchasing Cookies & Crème POPS
% of Customers Purchasing 12 Pack POPSPOPS
% of Customers Purchasing 6 Pack POPS
% of Customers Purchasing Medium Frozen Ice
% of Customers Purchasing Large Frozen Ice
% of Customers Purchasing Soda
% of Customers Purchasing Bottled Water
Manager Annual Salary
Assistant Manager Annual Salary
500
January
15%
30%
30%
15%
30%
15%
10%
15%
40%
35%
10%
10%
15%
12%
10%
10%
$
$
40,000
28,500
Growth rate
Weeks Per Month
2.43%
4.2
Loan period (years)
Loan interest rate
5
7.20%
Taxes:
If income before tax is equal or greater than:
If income before tax is less than:
Total Customers per month
January
$
$
125,000 Tax rate =
125,000 Tax rate =
Coded Document
February March
April
May
June
July
Customers per Week/Month
Cust Week Day
Cust Weekend Day
Cust per Week
Cust per Month
Formulas:
Cust per hour X hours open X Days open weekday
Cust per hour X hours open X Days open weekend day
Add them together
cust per week times weeks per month
Total Customers per month
February March
April
May
June
July
February March
April
May
June
July
22.00%
15.00%
August
September October
November December
Hourly Wages
Wages/Weekday
Wages/Weekend Day
Wages/Week
Wages/Month
Formulas:
Hourly Worker X Hourly WageX hours open X Days open weekend
Hourly Worker X Hourly WageX hours open X Days open weekday
Add them together
cust per week times weeks per month
August
September October
November December Formulas:
(Total Customers per month of
August
September October
November December Formulas:
Total Customers per month X %
Total Customers per month of Jan X Growth rate) + Total Customers per month of Jan
Start Up Costs:
Kitchen equipment
Cash Register and Sales equipment
Initial inventory
Pre-opening marketing
Store Setup (chairs, tables, decor etc.)
Security deposit
Initial insurance payment
Total
$ 12,250.00
$ 2,550.00
$ 5,000.00
$ 1,000.00
$ 3,500.00
$ 4,500.00
$ 1,500.00
$ 30,300.00
Owner’s Equity
Cash Reserves
Loan Amount
$ 18,000.00 Formulas:
Loan Amount – Owner’
$ 45,000.00
an Amount – Owner’s Equity
Perez POPS
Please read the instructions carefully.
A new client of yours, Katie Perez wants to open an ice cream shop near the downtown shopping
center.
She will beselling Frozen POPS, Frozen Ices, Frozen POPS Packs, Soda and Bottled
Water. Her grand opening will be January 1, 2024.
Katie 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 – “Perezpops.xls” 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.
•
Do not remove the Grading Criteria Worksheet.
•
Place the finished cash pro forma on a worksheet labeled “Cash ProForma”.
•
Calculations and functions must be used to determine values as necessary (i.e.
payment, taxes, cogs, sales, etc.
•
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 additional worksheets for your What if Scenario recommendation. Events
•
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).
1
•
Chart One –” Monthly Product Revenue” – this will show the monthly revenue for
each of your five products for the entire year. Charts should be on their own
worksheet.
•
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. Charts
should be on their own worksheet.
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: Specialty POPS, Frozen Ice, Soda and Bottle water.
Product Selling Prices:
POPS
Fruit Explosion $4.85
Vanilla Vanilla $3.85
Chocolate Mousse $4.35
Mango Tangerine Surprise $4.35
Strawberry Crème $3.75
Pina Colada $4.85
Strawberry Kiwi $4.35
Orange Crème $3.75
Chocolate sea salt $4.35
Cookies & Crème $3.25
Pop Packs
12pk $32.00
6pk $15.00
Frozen Ice
Medium $3.75
Large $4.25
Soda -$1.95 a bottle
Bottled Water – $1.25 a bottle
Cost of Goods Sold:
POPS
Fruit Explosion $0.90
VanillaVanilla $0.40
Chocolate Mousse $0.95
Mango Tangerine $0.70
Strawberry Crème $0.70
Pina Colada $0.90
Strawberry Kiwi $0.80
Orange Crème $0.50
1
Chocolate sea salt $0.95
Cookies & Crème $0.70
Pop Packs
12pk $7.20
6pk $ 4.20
Frozen Ice
Medium $ 0.95
Large $1.10
Sodas cost about $.90 per 16 oz. bottle
Water cost $.08 per 16 oz. bottle
Demand Rates:
% of customers purchasing Fruit Explosion
% of customers purchasing Vanilla Vanilla
% of customers purchasing Chocolate Mousse
% of customers purchasing Mango Tangerine Surprise
% of customers purchasing Strawberry Crème
% of customers purchasing Pina Colada
% of customers purchasing Strawberry Kiwi
% of customers purchasing Orange Crème
% of customers purchasing Chocolate sea salt
% of customers purchasing Cookies & Crème
15%
30%
30%
15%
30%
15%
10%
15%
40%
35%
% of customers purchasing Frozen Ice Medium
% of customers purchasing Frozen Ice Large
% of customers purchasing Pop 6pk
% of customers purchasing Pop 12pk
% of customers purchasing sodas
% of customer purchasing water
15%
12%
10%
10%
10%
10%
Operating Hours:
Weekdays (Monday – Thursday). Open 8 hours.
Weekends Friday, Saturday Open 12 hours.
Employees:
•
•
•
One hourly employee needed weekdays.
Two hourly employees needed weekends.
Hourly employees are paid $12.50 per hour.
•
Your client will be the manager and draw a salary of $40,000 per
year.
The assistant manager will receive a salary of $28,500 per year.
•
1
Product demand on average:
•
•
Weekdays the owner expects 18 customers per hour.
Friday – Saturday the owner expects an average of 30 customers per hour.
Start-up costs
Kitchen equipment: $12,250
Cash register and sales equipment: $2,550
Initial inventory: $5,000
Pre-opening marketing: $1,000
Store setup (chairs, tables, decor etc.): $3,500
Security deposit: $4,500
First Insurance Payment: $1,500 and then Insurance will be $1000 a month.
The building rent is 3600.00 per month.
Phone/Internet will cost about $1000 per month.
Electricity should cost about $1300 per month.
Advertising and promotion will be $500 a month.
Your client has $18,000 and plans to borrow $45,000 from the bank with a five-year loan at
7.2% interest. You are to calculate the monthly loan payment using the appropriate financial
function.
Assume a tax rate of 22% if Income Before Taxes (IBT) is equal to or is greater than
$125,000.
Assume a tax rate of 15% if Income Before Taxes (IBT) is less than $124,999.
You are to calculate the monthly tax payment using the appropriate boolean/logical function.
Assume that sales will grow at an average of 2.43% per month.
Assume that each month contains 4.2 weeks.
1
What If Scenario
Katie would really like to have a food truck to expand the business because there are lots of local outdoor
events where she could sell her products. There is a used food truck available, but it will require some
modifications to work for her business. Additionally, there will be extra expenses for operating it.
The following are the costs for this that have to be accounted for – some are ongoing monthly, and some are
one-time. She would like to pay for the used food truck over time like a car loan, but it will be at a different
interest rate and term than her existing loan. New Trucks cost between $50 and $100K so she was really
excited when she found an ad locally for the following used truck:
Georgia Food Truck For Sale: $33,000. -5.9 24 valve Cummins diesel engine, Automatic transmission -2000
Freightliner MT45 with 363,132 miles, needs new tires.
The good news? She was able to negotiate the price to $29,900 but will still have to buy tires. The truck can
be bought on a loan over 6 years at 7.15% interest.
To get it fully operational she will need the following: Tires $3600, Paint or Wrap to brand with Perez POPS $2500, Upgraded commercial freezer $3900, additional licensing for the food truck $145, Truck Insurance
$650 monthly separate from the business insurance.
To operate this part of the business, she will need to hire 2 additional workers that have commercial driver’s
licenses. She will have to pay these 2 people $15 per hour.
The plan is to attend events and sell Perez POPS. Events vary month to month. But, each event will involve
paying the workers for an 8 hour day which includes the setup time and working the event.
At events, Perez POPS will be selling an estimated 1000 pops per event. Below is the schedule of events
over the year and the approximate number of pops sold at each event.
Month
January
February
March
April
May
June
July
August
September
October
November
December
Number of Events
0
3
4
5
7
10
10
10
6
4
2
2
Approximate number of pops sold at each event:
Fruit Explosion
Vanilla Vanilla
Chocolate Mousse
Mango Tangerine Surprise
Strawberry Crème
Pina Colada
Strawberry Kiwi
Orange Crème
100
125
100
50
125
50
75
50
1
Chocolate sea salt
Cookies & Crème
175
150
In a new spreadsheet, using the existing Proforma – add on these additional needs to get the food truck
business started. Should Katie invest in this opportunity now? Why or why not. Add a textbox to the
spreadsheet for your recommendation/answers to these questions.
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.
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.
Reminder:
Submit the completed Project in an Excel file to the D2L assignment Dropbox. Name the file
YourlastnameExcel.xlsx – Remember (Google Sheets, Numbers, Links, PDFs are all unacceptable – must be
an Excel File).
1

Purchase answer to see full
attachment