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
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.
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.
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
Oil painting ofyour clientâ€™s mommatohang on thewall: $350
Security deposit: $6,500
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.
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?
Aformer schoolfriend ofyour clienthasasmallmusicaltrio andhasplantedthe ideainyour clientâ€™s headofaddingasmallmusical trio toprovidelivemusic onthe weekends (SaturdayandSunday). Thefriendstateshecanguarantee7more customers per hour ify