Beth’s Bats 2014 Budgeting Problem
Beth’s Bats is a baseball bat company. Beth produces professional bats on lath machines that form the
bats from select high grade straight grain maple wood. She sells these bats to professional ball players
and adult players who want the same bats as used by the pros.
Beth expects sales volume to be 20,000 units in the first quarter, with 2,000 unit increases in each
succeeding quarter. Beth believes that sales will continue to increase in this nature into the first
quarter of next year. Beth can sell each bat for $80.00. Beth believes it can meet future sales
requirements by maintaining an ending inventory equal to 5% of the next quarter’s budgeted sales
Beth’s Bats must purchase high quality straight grain maple wood in order to make the bats. The wood is
purchased from a supplier as a 4”x 4” in board that is 8 feet long. Beth is capable of making 2 individual
42” bats from each 8 foot board she orders. Beth can purchase these special boards for $45 each. Beth
requires 10% of next quarter’s raw material needs to be on hand at the end of the budget period.
Beth’s Bats is uses an automated lathe system that allows a worker to set up multiple machines at the
same time. The machine does all of the cutting for the bat, then adds the preservative and burns the
company logo on the bat. A single worker can process 10 bats an hour at an average cost of $60 per
labor hour.
Beth has the following manufacturing overhead costs:
Total variable overhead costs per unit = $2.40.
Total fixed overhead costs per quarter = $286,440 per quarter.
Beth has both variable and fixed expenses in selling their bats consisting of:
Variable selling expenses are 20% of sales revenues
Fixed Administrative Expenses = $250,000
Beth has cash 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.
Beth 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 $25, 000 Depreciation Expense per quarter.
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
$200,000. Beth will pay 50% of the purchase price in cash during the first quarter, and then the
remaining 25% in the second and third quarters.
Beth 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. Beth’s Bats 2014 Budgeting Problem
Beth 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
You may assume that Beth has the following amounts at the end of the year that should be applied to
the Budgeted Balance Sheet along with the balance sheet amounts presented in the other budget
Property Plant and Equipment (Net) = 750,000
Long Term Liabilities = $0
Common Stock = $800,000
Retained Earnings = 1,094,925.
Note that this is a corporation, so the equity section of the balance sheet should include
common stock and retained earnings.
Prepare the following budgets for the year broken into quarters. These budgets must use the same
format as the ones provided in our text.
Sales budget
Production budget
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
Cash Budget
Budgeted Balance Sheet for the end of the year
Additional Instructions:
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. The final product should be capable of being used for what-If analysis. To accomplish
this the various budgets must be linked so that a change in one will change the amounts in all
others. Budgets should be presented in good form – to include similar formatting, $ signs where
necessary, double underlines, etc.
You are to work independently. Part of the purpose of this project is to evaluate the student’s
ability to create a Budget. This includes not only creating the proper format, but also using the
correct formulas in the cells to allow for what if analysis to be performed. If you need help,
please ask the instructor in the Budget Project area of the discussion board in D2L. Evidence that
you received outside assistance including the use of internet templates will result in a grade of
zero for this assignment.
