Bottling is a juice bottler. Bobâ€™s
produces bottled orange juice from fruit concentrate purchased from suppliers
in Florida, Arizona, and California. The
only ingredient in the juice is the concentrate provided by the supplier. The juice is processed, pasteurized, and
bottled for sale in 16 ounce plastic bottles that are provided by each supplier
with their own labels already attached.
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 next year. Bobâ€™s can sell each
bottle of orange juice for $2.50 per bottle.
Bobâ€™s believes it
can meet future sales requirements by maintaining an ending inventory equal to 10%
of the next quarter’s budgeted sales volume.
must use concentrate in Corder to make its juice. Bobâ€™s has determined that it takes one gallon
of concentrate for every 5 bottles of finished product (1/5 of a gallon, or .20
of a gallon). Each supplier provides
Bobâ€™s with a gallon of concentrate and the 5 plastic bottles needed for each
gallon at a total unit price of $2.00. Bobâ€™s
requires 10% of next quarterâ€™s raw material needs to be on hand at the end of
the budget period.
is highly automated. A worker can
process 100 bottles of orange juice in one hour (1/100). Bobâ€™s factory worker costs per employee average
of $40 per hour.
has the following manufacturing overhead costs:
Total variable overhead costs per
unit = $0.40, (40 cents).
Total fixed overhead costs per
quarter = $72,500 per quarter.
Bobâ€™s has both variable and fixed expenses
in selling their juices consisting of:
selling expenses are 5% of sales revenues
Administrative Expenses = $10,000
sales make up 25% of Sales Revenues. The
other 75% of revenues are Sales on Credit.
80% of credit sales are collected in the quarter of the sale.
20% of credit sales are collected in the quarter following the sale.
20% Accounts Receivable carried forward to quarter 1 from last yearâ€™s
sales is $75,000.
cash payments are forecast to include the following;
75% of Raw Materials are paid for in the quarter purchased.
25% of Raw Materials are paid for in the quarter following the purchase.
25% of Accounts Payable carried forward to quarter 1 from last yearâ€™s
purchases = $23,000.
Manufacturing overhead included $5, 000 Depreciation Expense per
All other expenses are paid in cash during the quarter incurred.
Management plans to invest in new equipment in the first quarter that
has a total cost of $250,000. Bobâ€™s will
pay 80% of the purchase price in cash during the first quarter, and then the
remaining 20% in the second quarter.
cash management includes the following;
Cash on hand at the beginning of quarter 1 was $200,000. The minimum
cash balance must remain at or above $200,000.
Bobâ€™s has an agreement with the bank allowing it to make short term
borrowing and repayments of cash in $5,000 increments. No interest is charged
if the loans are repaid by the end of the next quarter.
Bob did not have any outstanding loans at the beginning of the 1st
had the following balances of note at the end of the year
Property Plant and Equipment (Net) = 750,000
Common Stock = $800,000
Note that this is a corporation, so the equity section of the balance
sheet should include common stock and retained earnings.
the following budgets for the year broken into quarters.
Raw Materials Purchases Budget
Direct Labor budget
Manufacturing Overhead budget
Budgeted Manufacturing Cost Per Unit
Cost of Goods Sold Budget
Selling and Administrative expense budget
Budgeted Income Statement
Budgeted Cash receipts
Budgeted Cash Payments
Budgeted Balance Sheet for the end of the year
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.