Cash ProForma

UsingMicrosoftExcel,constructamonthlyproformacashbudgetfor yourclientfor thefirstyear of operations.

 

Usethe fileattachedExcelTemplate  –“PizzeriaTemplate”as your startingpoint. This is thefilethatmust bedownloadedandused asthebasis for your assignment. Do not make anychangesto thispre–designed template.

 

Do not useatemplatefrom aprevious semester–this is considered academic dishonestyandwill besubject todisciplinaryaction.

Placethefinishedcash proformaonaworksheetlabeled“Cash ProForma“. Place allyourcaseassumptionsdataonaseparateworksheet.Label the

worksheet“Assumptions“(note: eachpiece ofdatamustappear initsowncell on the Assumptionsheet).

 

Placeyour start-upcosts onathirdworksheetlabeled“StartupCosts“

 

Create two additional worksheetsforyour recommendations.  Label oneworksheet

“BeerRecommendation”,label the other“EntertainmentRecommendation”.

 

AppropriateCharts (graphs):Youwill becreatingtwoseparatecharts socreateand label two additional worksheetsfor thecharts(eachchart will beinits own worksheet).

 

Chart One –”MonthlyProductRevenue” –this willshowthemonthlyrevenue for eachofyourfive productsfor theentireyear.

ChartTwo–“TotalProduct Net Income”– Youwantto track thetotal productnet incomefortheyear todetermineanytrendsor projectionsin productsales.

 

Makesurebothchartsareformattedcorrectly(i.e. appropriate title,legendwhere appropriate,dataseries properlylabeled) andtheyare appropriatefor businessuse.

 

Information neededto completeassignment:

 

Product Selling Prices:

Pizzaswill sellfor $10.25each

Wings will sellfor $4.95anorder (8wings to an order) Salads will sellfor$3.50each

Friessellfor $1.15perorder

Sodassellfor $1.25 abottle

 

Cost of Goods Sold:

The pizzaingredients(dough,cheese,sauce,etc.)cost$7.15perpizza. The frozenchickenwings cost$3.19per orderfrom thesupplier.

Saladingredients(lettuce,tomatoes,cheese,etc.) cost $1.23per saladserving

Frozenfriesandoilaverage to$.67 per order

Sodascostabout $.7per 16 oz. bottle

 

Thebuildingrentis$2750permonth. Phonewill cost about$320permonth. Electricityshouldcostabout$675amonth. Insurancewill be$850 amonth.

Advertisingand promotionwill be$900 amonth.

 

Operating Hours:

 

The diner will be opensixdays aweek (closedonMonday).

 

The diner will servelunch and dinnerandwillbeopenfrom 11am –7pmon weekdays (Tuesday–Friday).  Itwillneedonehourlyemployeeand an assistant manager (or manager) duringthesehours thatthediner is open.

 

On Saturdays andSundays thestore willbe open11am– 11pmandwill needtwo hourlyemployeesand an assistant manager(or manger).

 

Your clientwill be themanager and drawasalaryof$48,500 per year (includes benefits). Hewill alsowork in thestoreduringthebusiest times,andfill inforthe assistantmanager ondays off andsick days.  The assistantmanagerwill receivea salaryof $37,500 per year (includes benefits).Thehourly workers willbe paid $7.00 anhour.

 

TuesdaythroughFridays the owner expectsan averageof17customersanhour.

 

Saturdays andSundays theowner expectsan averageof38customersanhour.

 

DemandRate:

 

On average,threefourths ofallcustomer willbuya pizza, 1/2 ofthecustomers will buychickenwings,3/4ofthemwill buyasalad,allofthembuyfrenchfries,and everycustomer will purchaseasoda.

Start–up costs for thediner includes: Kitchenequipment:$16,250

Cashregister andsales equipment:  $1,250

Initial inventory:$5,500

Pre–openingmarketing: $3,500

Dinerfixtures (chairs,tablesetc.):$4,500

Oil painting ofyour client’s mommatohang on thewall:  $350

Licenses:$1,025

Security deposit: $6,500

FirstInsurancePayment: $850

 

Your client has$10,000 and plans toborrowtherestfromthebankwithafive–year loanat6.1% interest.You are tocalculatethemonthlyloan payment usingthe appropriatefinancialfunction.Theloanamountis $50,000.

 

Assumeatax rateof23%ifIncomeBefore Taxes(IBT)isequalto orisgreaterthan

$23,500.  Assumea taxrate of13%ifIBTisless than$23,500.You are tocalculate themonthlytaxpayment using theappropriatelogicalfunction.

 

Assumethatsaleswill growat an averageof 2.50%permonth. Assumethateachmonthcontains4.2weeks.

 

 

 

Scenario One:“Whatif”Analysis

 

Your clientis unsure if heshouldsellbeer at thediner.Hethinkshecansella beer to everysecondcustomer (exceptonSundayswhenno beercan besold) andit seemstobelucrative becausethebeer sells for$4.75eachandcosts himonly

$1.10topurchase.Unfortunatelyyour clientis afraidthathewouldcannibalize his soft drink saleswith the beer customers (onesoftdrink lessfor everybeer sold).It willcost him$5,250topurchase thebeer licensefromthecountyandhismonthly insurancecostswouldrise byanother $275 per month.

 

What isyour recommendation:Shouldyour clientoffer beer tohis customers?