Need help with preparing a budget spreadsheet, got stuck at "retails chargeback. Need help with the calculation. I have the spreadsheet, but cannot attach in here. Below is the project case instruction and senarios:

HOT DIGGIDY DOG BUDGETING CASE

Brothers Joe and Ed Krauts 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 Dogs 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 templates 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

AND

HAVE A HOT DIGGIDY DAY! J

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 partners capital account (i.e. owners 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 month.

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 L)	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 business.

HDDs 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 Marchs sales needs. To calculate Decembers 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 Krauts 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 managers 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.

Spreadsheet Color Codes

Enter a #

Enter a formula

Don't enter anything

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 optimistic.

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.

Changes:

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 $5,000.

Lower the average order revenue to from $6.50 to $6.25.

Increase the retail COGS to 35% of sales.

Raise the managers annual salary to $60,000.

In order to attract quality employees, budget an $11/hr wage rate (before loadings).

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 -