Reference no: EM13289193

Bob's Bottling is a juice bottler. Bob's produces bottled orange juice from fruit concentrate purchased from suppliers in Florida, Arizona, and California. The only ingredients in the juice are water and concentrate. The juice is blended, pasteurized, and bottled for sale in 12 ounce plastic bottles.

Bob's expects sales volume to be 250,000 units in the first quarter, with 25,000 unit increases in each succeeding quarter. Bob's believes that sales will continue to increase in this nature into the first quarter of 2014. Bob's can sell each bottle of orange juice for $2 per bottle.
Bob's believes it can meet future sales requirements by maintaining an ending inventory equal to 20% of the next quarter's budgeted sales volume.

Bob's Bottling must use concentrate in order to make its juice. Bob's has determined that it takes one gallon of concentrate for every 10 bottles of finished product (1/10). Each gallon of concentrate costs $4.00. Bob's also requires 10% of next quarter's raw material (concentrate) needs to be on hand at the end of the budget period. 

Bob's Bottling is highly automated. A worker can process 100 bottles of orange juice in one hour (1/100). Bob's factory workers are paid an average of $15 per hour. 
Bob's has the following manufacturing overhead costs:
Variable overhead:
Indirect Materials - $5 per direct labor hour
Indirect Labor - $4 per direct labor hour
Utilities - $0.50 per direct labor hour
Maintenance - $0.50 per direct labor hour
Fixed overhead:
Supervisory Salaries - $22,700 per quarter
Depreciation - $45,000 per quarter
Property Taxes - $2,500 per quarter

Bob's has both variable and fixed expenses in selling their juices consisting of:
Variable:
Sales Commissions - $0.25 per unit
Shipping Costs - $0.10 per unit
Fixed:
Sales Salaries - $25,000 per quarter
Office Salaries - $7,000 per quarter
Depreciation - $5,000 per quarter
Property Taxes - $1,500 per quarter


Bob's collects 60% of the current quarter's sales in the quarter of the sale and the other 40% is collected in the next quarter. Accounts Receivable at December 31, 2012 was $80,000. Bob's has a policy of paying 50% of the direct materials purchases in the quarter of purchase and the balance in the quarter after the purchase. Accounts Payable at December 31, 2012 was $10,750. All direct labor costs, manufacturing overhead, and selling and administrative expenses are paid in the quarter incurred. 

Bob's beginning cash at January 1, is expected to be $200,000. Beginning in January, Bob's desires to keep a cash balance of at least $200,000 on hand at the end of any month. If the projected cash balance is less than that, a line of credit at Bob's local bank will be used to make up the shortage. If Bob's draws on the line of credit, the company is charged an interest rate of 4% annually. If the line of credit is used, money is borrowed at the beginning of the quarter. Repayments are made at the end of the quarter in which there is sufficient cash (over $200,000) to pay back the entire line of credit. Last, Bob's pays estimated income taxes on a quarterly basis. Bob's estimates that its federal tax liability is 20% of income before income taxes.
Bob's plans to buy some new machinery in January at a cost of $153,500 (assume depreciation had already been included in the above figures). The company also plans on paying a dividend of $25,000 in Quarter 4. 
Bob's had the following balances of note at the beginning of January:
Building and Equipment $500,000 Accumulated Depreciation $ 93,400
Common Stock $ 300,000 Retained Earnings $ 428,916

Prepare the following budgets for the year 2013 broken into quarters. Please include total for the year, unless otherwise stated.

Sales budget
Production budget
Materials budget for concentrate 
Labor budget
Manufacturing Overhead budget
Selling and administrative budget
Cost of Goods Sold per unit calculation
Budgeted income statement for the YEAR
Collections budget 
Materials payment budget
Cash budget (no annual total required)
Budgeted balance sheet as of Dec. 31

You must use Excel to do this project. All work must be in one Excel File. Multiple excel tabs may be used if the student desires, but only one file will be accepted.

You are to show all calculations - either within the cell, beside the cell, and by linking excel formulas. Budgets should be presented in good form - to include similar formatting, $ where necessary, double underlines, etc.