HOT DIGGID Y DOG – BUDGETING CASE
Brothers Joe and Ed Kraut’s have long dreamed of one day opening “Hot Diggidy Dog” - a
hot dog restaurant serving up the tastiest dogs and homemade California-style toppings. They
finally save up enough money to take the plunge and ask you, their Financial Consultant, to
help them plan their finances for the first year of operations.
Use the information in the case and the blank Excel template to construct a master budget for
Hot Diggidy Dog’s first year of operations. You must enter numbers and use Excel formulas
where necessary. No credit will be given for copying numbers into your spreadsheet directly
from the answer key check figures. Also, DO NOT change any aspect of the template’s
format – even inserting a single cell could cause major problems!
Note: Complete Scenario 1 before completing Scenario 2. Doing so will save you lots of
time and teach you the most efficient and least taxing way to construct budgets and “what- if”
scenarios using Excel.
HAVE A HOT DIGGIDY DAY!
SCENARIO 1: “BASE CASE”
Hot Diggidy Dog (HDD) is capitalized with $100,000 cash. Joe Kraut contributes $30,000
cash from his personal savings and Ed Kraut contributes $20,000 from his personal savings.
The remaining $50,000 is obtained through an 8% $150,000 line-of-credit at the local bank.
The line-of-credit allows the business to borrow up to $150,000 of principal at any time if
necessary, though at the inception they will only be borrowing $50,000.
Joe and Ed agree to split the profits (i.e. net income) according to their equity share
percentages in the business. In order to maximize expansion potential, Joe and Ed agree not
to withdraw any money for salaries or dividends for themselves in the first year of operations.
Therefore, all earnings will directly fund each partner’s capital account (i.e. owner’s equity).
HDD uses the accrual method of accounting in constructing its projected balance sheet and
income statement. For budgeting items that use Year 2 January figures, assume that Year 2
January operations will be identical in all respects (except “Grand Opening” Advertising) to
Year 1 January operations.
On January 1, Joe and Ed invest the $100,000 and start Hot Diggidy Dog. The funds are
invested as follows:
$60,000 pays for the buildout (i.e. re- modeling the store, signage, permits buying
cooking equipment, tables, chairs, etc.). All buildout costs will be uniformly
depreciated over their expected useful life of 5 years using straight-line depreciation
with an assumed $10,000 salvage value.
Joe spends $5,000 on “Grand Opening” advertising that will only run during the first
month of operations.
The remaining cash is used as working capital.
More information on the line-of-credit and cash requirements:
Any amounts borrowed or repaid on the line-of-credit are made at the end of the
HDD requires a $10,000 minimum cash balance (before interest payments) at month
end. In other words, any month-end cash balances below $10,000 can only be below
$10,000 to the extent of interest payments in that month. Any deficiencies in cash
balances below the $10,000 pre- interest minimum are compensated for by borrowing
against the line-of-credit. Any cash balances above the $10,000 pre-interest minimum
at month-end are used to pay down the line-of-credit at the end of the month.
Monthly interest payments (and monthly interest expenses) on the 8% line-of-credit
are determined at month-end by multiplying the beginning of the month balance on the
line-of-credit by 1/12 of the annual interest rate.
For instance, if the April beginning line-of-credit balance is $1,000, the interest
payment (and expense) for April is: 1/12 x 8% x $1000 = $6.70
Please note: The coding on Excel for borrowings and repayments on the line-of-credit
has been provided due to its technical complexity. Please do not alter these lines of
code, but do complete the rest of the cash budget properly so that the borrowings and
repayments schedules show the correct figures. Also, the beginning balance for the
line-of-credit in January is $50,000 since this amount is borrowed at the
commencement of operations (i.e. on day 1).
Sales and COGS/Inventory Data
The projected average number of sales transactions by hour are as follows:
Hour # Transactions
11-12 AM 30
12 AM -1 PM 40
1 PM – 2 PM 35
3 PM – 4 PM 5
4 PM – 5 PM 0
5 PM – 6 PM 15
6 PM – 7 PM 35
7 PM – 8 PM 45
8 PM – 9 PM 30
9 PM – 10 PM 10
Total Daily Sales 245
Monthly sales indices and the # days/month are given below:
Month Monthly Sales Index # Days in Month
January 1.0 31
February .9 28
March .9 31
April 1.0 30
May 1.2 (little league season!) 31
June 1.2 (little league season!) 30
July 1.3 (summertime fun!) 31
August 1.3 (summertime fun!) 31
September .8 (back to school ) 30
October .7 31
November .7 30
December .9 31
You can calculate the number of monthly sales transactions with the following formula:
(# of Daily Sales Transactions) x (# Days in Month) x (Monthly Sales Index)
Other Useful Sales, Inventory and Operations information:
The average order revenue is expected to be about $6.50 for a “Diggidy Combo”
consisting of a Diggidy Dog, fries and soda (these are the only three items HDD sells).
For the sake of simplicity, assume HDD only sells “Diggidy Combos” in its retail
HDD’s retail Cost of Goods Sold (COGS) is approximately 30% of sales. This is an
average across all items in the Diggidy Combo and includes an allowance for waste
and supplies and condiments used by customers.
HDD is open every day of the year, including all major holidays and keeps the same
hours every day (11 AM – 10 PM).
In order to maintain an effective inventory buffer, HDD purchases 60% of its food
inventory needs in the month of the sale and 40% of its needs one month in advance.
For instance, if HDD budgets COGS of $100 during the month of March, it will
purchase $60 worth of inventory in March and $40 in February to meet March’s sales
needs. To calculate December’s “advance” purchases (i.e. purchases for year 2
January sales) just enter $5,924 in cell M174.
HDD pays for half of its inventory purchases in the month of the purchase and for the
remaining half in the month after the purchase. Wanting to conserve cash, the Krauts
decide not to take any early payment discounts.
HDD expects about 65% of its sales to be in cash, and 35% to be in credit cards.
Because HDD accepts credit cards and cash, it does not typically have Accounts
Receivable for individual orders. However, HDD estimates that 1% of all its credit
card sales will be charged back (i.e. uncollectible). Furthermore, HDD pays .25 per
credit card transaction plus 2% of the transaction value (i.e. a $5 credit card order
would run $.25 + .02 x $5 = $.35.
Catering Business Information
The Krauts also expect to do some catering business in their first year of operations.
Catering customers will pick up their orders at the store so that there are no
transportation costs to budget. HDD estimates approximately $1,000 in catering sales
per month starting in July (i.e. the Krauts believe it will take approximately six months
to earn a reputation that allows them to sell catering services). Assume that catering
customers pay 50% of their catering charges on the day of the order (i.e. during the
month the services are sold), the remaining 40% in the month following the order and
that 10% of catering sales are uncollectible. Further assume that all (collectible)
catering charges are paid in cash according to the aforementioned schedule. Because
catering involves additional paper and supplies expenses, the Kraut’s estimate that
catering COGS will be approximately 40% of sales.
Labor Cost Information
HDD is staffed at all times with one manager who earns an annual salary of $50,000
plus 35% of salary loadings and two employees who each earn a wage rate of $10/hr
plus 30% loadings. Assume the manager’s annual salary is paid at month-end evenly
throughout the year and that employees are paid at month-end for the cumulative
number of hours worked that month.
Though the store is open from 11 AM – 10 PM, both employees arrive to “prep” for
the day at 9 AM and leave at 11 PM after cleaning up. Ignore overtime considerations
when calculating employee labor costs.
Other Expenses Information
HDD has the following other expenses (*please note the payment timing):
Annual Insurance (paid fully on January 1st): $2,500
Yearly Advertising Budget (spent evenly spent each month): $12,000
Legal, Accounting & Administrative Expenses (spent evenly spent each month): $3,000
Miscellaneous Other Expenses (spent evenly each month): $500
HDD will not pay any income taxes until April of next year (though - remember - it will still
have accrual tax expense this year!). Assume a tax rate of 30% for HDD.
Enter a #
Enter a formula
SCENARIO 2: “WORST CASE”
Examining the spreadsheet you created for the “Base Case” Scenario, Joe and Ed Kraut are
impressed with your financial modeling skills, but worry that the projections may be too
Your job now is to re-cast the spreadsheet you built in Scenario 1 with the following new
information. Note: You do not need to re-create a spreadsheet from scratch. Simply copy the
Scenario 1 spreadsheet into a new worksheet page and modify only those numbers that
require changes according to the “Worst Case” information below.
Please mark all your changes on the spreadsheet in BLUE FONT COLOR so that they are
easy to detect.
Offer a promotional coupon for 10% off the total order price in the local
newspaper. Monthly advertising costs will increase by $200. The Krauts
estimate that about 20% of all orders will involve a coupon redemption
while the remaining 80% will sell at full price. This promotion will be
available all year.
Halve the # of sales transactions for each hour of operation.
Raise the line-of-credit interest rate from 8% to 9%.
Raise the total buildout cost from $60,000 to $70,000.
Spend $10,000 on initial “Grand Opening” advertising instead of just
Lower the average order revenue to from $6.50 to $6.25.
Increase the retail COGS to 35% of sales.
Raise the manager’s annual salary to $60,000.
In order to attract quality employees, budget an $11/hr wage rate (before
Assume that 20% of Catering Sales are uncollectible and that 2% of its
credit card sales are charged back (i.e. uncollectible).
Supplies require 70% of purchases to be paid in the month of purchase (and
30% will be paid in the month after the purchase).
Assume a $0 Salvage Value on Buildout Assets.
Increase Miscellaneous Other Expenses to $1,000 per month.
- END OF CASE -