Comprehensive Budgeting Problem Fall 2005

Excel #3

Comprehensive Budgeting Problem Fall 2016

The Bobcat Beverage Company, Inc.

Comprehensive Budgeting Problem

The assignment for this problem is to prepare the Bobcat Beverage Company, Inc., comprehensive budget for September 2015 and October 2015. Your budget MUST be completed in EXCEL. DO NOT share the template in Google Sheets – if you do so the file cannot be uploaded to the dropbox resulting in a zero grade. The format of the Proforma Income Statement must be a contribution margin income statement. NOTE: You MUST use formulas to link the budgets and values within the budgets where possible. Failure to exploit Excel in linking and using formulas will result in a zero for this assignment.

The Bobcat Beverage Company, Inc. sells a wide variety of beverages and snack foods.

The Bobcat Beverage Co., Inc. Sales Forecast for Sept. to Nov., 2015
Sales Revenue (all Sales are on Credit) / September / October / November
$820,000 / $940,000 / $980,000

Bobcat Beverage Company, Inc

Balance Sheet

As of August 31, 2015

Assets: / Liabilities:
Cash / $ 25,800 / Accounts payable / $ 81,859
Accounts receivable / 595,000 / Sales commissions payable / 59,500
Inventory / 50,688 / Advertising Expense Payable / __ 121,250
Prepaid Insurance / 33,000 / Income taxes payable / __ 22,000
Current assets / $ 704,488 / Dividends payable / 0
Current liabilities / $ 284,609
Property, Plant & Equipment / Long-term debt / 320,000
Land / 100,000
Plant & Equipment / 400,000 / Stockholders' equity
Accumulated depreciation / (100,000) / Common stock / $ 95,000
Retained earnings / 404,879
Total assets / $1,104,488 / Total SE & Liabilities / $1,104,488

Policies and Plans used by The Bobcat Beverage Company, Inc., in budgeting

All Sales are on Credit. Sales are collected 28% in the month of sale and 72% in the month following sale.

Cost of goods sold is budgeted to be 42% of sales.

The Bobcat Beverage Company, Inc. plans to end each month with inventory levels equal to 9% of the next month’s cost of sales.

The company pays for 80% of the purchases of merchandise in the month of the purchase and 20% in the following month.

5.The Bobcat Beverage Company, Inc. pays a sales commission of 7.5% on all sales. The selling commission is paid in the month after the salesmen earn the commission.

6.The company believes that advertising expense is a mixed cost. Upon reviewing their past two years of financial data, they believe that advertising expense (y variable) is related to sales (x variable). They use the high-low method to determine the variable rate and fixed portion of advertising expense. [Calculate the high-low method by hand, not in Excel]

Sales Advertising Expense

High Month1,000,000 235,000

Low Month 600,000 165,000

7.The company pays all of its advertising expense in the month AFTER it is incurred.

The Bobcat Beverage Company, Inc. estimates its general and administrative expenses using a flexible budget formula: Other general & administrative expense =15% of budgeted sales plus $60,000. The general and administrative expenses budgeted with this formula are paid in the month in which they are incurred.

Depreciation is $20,000 per month on the property, plant and equipment owned on August 31, 2015 for the period of this budget.

On July 31, 2015, the company purchased and paid cash of $36,000 for a twelve-month policy covering the period August 1, 2015 to July 31, 2016 and recorded the cost in Prepaid Insurance.

On September 15, 2015, the company purchased Land for $150,000, paying cash.

The Bobcat Beverage Company, Inc. records interest expense and accrues interest payable at the rate of 1% per month (simple interest) based on the beginning balance of Long-Term Debt for that month. The Bobcat Beverage Company, Inc. will pay interest in the month it is incurred.

The Bobcat Beverage Company must maintain a minimum cash balance of $25,000. If it must borrow any funds, it must borrow in $1,000 increments. Any excess cash will be used to pay down long-term debt. The company may either borrow funds or repay funds, but not both in the same month.

The Bobcat Beverage Company, Inc. records income tax expense and accrues income tax payable monthly using a 30% estimated tax rate. Income taxes are paid in the month AFTER they are incurred.

The company will declare a cash dividend on September 20, 2015 for $20,000. The cash dividend will be paid on October 15, 2015. No other dividends were declared or paid.

Once you have completed the budget, determine the following balances. On the template, make sure you link your answer to the appropriate cell. Failure to link to the appropriate cell will result in zero credit for that answer. Failure to complete this part of the assignment will result in a ZERO for the entire assignment, regardless if you completed the six budgets.

Total Cash Receipts for September and October
Total Inventory Purchases for September and October
Total Cash Payments for Inventory Purchases for September and October
Total Variable Selling & Administrative Costs for September and October
Total Fixed Selling & Administrative Costs for September and October
Total Cash Payments (S&A) for September and October
Total Cash Surplus (Deficit) for September and October
Total New Borrowing (Repayments) for September and October
The Contribution Margin for September and October
Total Interest Expense for September and October
Pre-tax Income for September and October
Income tax expense for September and October
Ending Balance of Accounts Receivable for September and October
Ending Balance of Inventory for September and October
Ending Balance of Prepaid Insurance for September and October
Ending Balance of Accumulated Depreciation for September and October
Ending Balance of Accounts Payable for September and October
Ending Balance of Commissions Payable for September and October
Ending Balance of Long-Term Debt for September and October
Ending Balance of Retained Earnings for September and October

Rules regarding the completion of the Excel Assignments:

1)You MUST use Microsoft EXCEL and not any other spreadsheet program. If you are a Mac user, make sure you use Microsoft Excel to complete your assignments. Any file created or shared by any other spreadsheet program (Google Sheets, Apple Numbers) may not upload to the ACCT 1020 website and will not be graded.

2)You MUST use the Excel Templates provided on for this semester.

3)You MUST download the Excel Templates using YOUR account from Each template is individually coded to prevent academic dishonesty. Any student found using the template from another student’s account will receive an automatic zero on the assignment and be subject to further penalties for academic dishonesty. In addition, any student allowing another student to use his/her account from will also receive a zero on the assignment.

4)You MUST sign the academic honesty pledge or you will lose points per your instructor’s discretion.

5)You MUST use formulas and links whenever possible. If you simply type in your solutions rather than use formulas (in ANY part of the assignment), you will receive a ZERO – NO EXCEPTIONS.

6)You MUST submit your Excel files per your instructor’s request. Failure to submit your Excel file correctly according to your instructor’s request will result in points deduction per your instructor’s discretion. Please note that each instructor may have his/her own required method of turning in the assignment!

7) To receive credit, your instructor must RECEIVE your assignment by the due date (no later than11pm without exception).

8)This is a GROUP assignment. You MUST work with individuals IN YOUR CLASS. YOU MAY NOT WORK WITH SOMEONE IN ANOTHER SECTION (even if you both have the same instructor)!