Comprehensive Budgeting Problem Fall 2005



Excel #3

Comprehensive Budgeting Problem Fall 2013

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 2013 and October 2013. Your budget MUST be completed in EXCEL. 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., 2013 |

| |September |October |November |

|Sales Revenue (all Sales are on Credit) | | | |

| |$880,000 |$910,000 |$940,000 |

Bobcat Beverage Company, Inc

Balance Sheet

As of August 31, 2013

|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

1. All Sales are on Credit. Sales are collected 25% in the month of sale and 75% in the month following sale.

2. Cost of goods sold is budgeted to be 52% of sales.

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

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

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.

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 Month 960,000 135,000

Low Month 400,000 65,000

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

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

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

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

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

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

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

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

12. The company will declare a cash dividend on September 20, 2013 for $20,000. The cash dividend will be paid on October 15, 2013. 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.

1. Total Cash Receipts for September and October

2. Total Inventory Purchases for September and October

3. Total Cash Payments for Inventory Purchases for September and October

4. Total Variable Selling & Administrative Costs for September and October

5. Total Fixed Selling & Administrative Costs for September and October

6. Total Cash Payments (S&A) for September and October

7. Total Cash Surplus (Deficit) for September and October

8. Total New Borrowing (Repayments) for September and October

9. The Contribution Margin for September and October

10. Total Interest Expense for September and October

11. Pre-tax Income for September and October

12. Income tax expense for September and October

13. Ending Balance of Accounts Receivable for September and October

14. Ending Balance of Inventory for September and October

15. Ending Balance of Prepaid Insurance for September and October

16. Ending Balance of Accumulated Depreciation for September and October

17. Ending Balance of Accounts Payable for September and October

18. Ending Balance of Commissions Payable for September and October

19. Ending Balance of Long-Term Debt for September and October

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

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 than 11pm 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)!

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download