League Schedule Excel Template

Document Sample
League Schedule Excel Template Powered By Docstoc
					                      HOT DIGGIDY 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.

                                     GOOD LUCK
                              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.

Color Codes

Enter a #
Enter a formula
Don't enter
                            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 -

Description: League Schedule Excel Template document sample