Question;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;volume.;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;quarter.;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;documents.;? 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.
Paper#40187 | Written in 18-Jul-2015Price : $42