I need help with parts 8-10 but most importantly part 8 the Cash Budget. Any assistance would be great. I believe I have done all the parts up to that point fine just having diffulties with the expenses (equipment purchases) and the calculating the financing. Thank you.

------------------------------------------------------------------------------------------------------------

Our controller, Veronica Verrengia, is negotiating with our bamboo suppliers in China. We need the Softball Bat Divisions Master Budget for 2018 for our corporate strategic planning process, and we cannot wait for Veronicas return from China. We would like you to prepare the Softball Bat Divisions Master Budget for 2018 The deliverables are as follows:

1.Sales budget, including a schedule of expected cash collections.

2.Production budget.

3.Direct materials budget, including a schedule of expected cash disbursements for materials.

4.Direct labor budget.

5.Manufacturing overhead budget, with a calculation of a pre-determined overhead rate.

6.Ending finished goods inventory budget calculating the expected value of the finished goods inventory as of December 31, 2018.*

7.Selling and administrative expense budget.

8.Cash budget.

9.Budgeted income statement for the year ended December 31, 2018.*

10.Budgeted balance sheet for December 31, 2018.*

All the Master Budget schedules (except those marked with an asterisk) for the Softball Bat Division should include a column for each quarter and a total column for the fiscal year. We only need annual totals for the budgeted financial statements (schedules 9 and 10) and we only need a year-end total for the value of finished goods inventory (schedule 6).

You can print more than one schedule per page, but do not have a page break in the middle of a budget schedule. We like to be able to view an entire budget schedule without flipping back and forth between pages. Please also use a type font of between 10-12 points for printing. We also need you to submit (via e-mail) the Excel spreadsheet that you used to create the budget schedules you print so we can use the spreadsheet as a starting point for future budgets. Send the Excel spreadsheet as an email attachment.

Ive attached a brief description of the Softball Bat Division to the budget data Veronica gave me before she left for China. We eagerly await your results. Sincerely,

Tom Verrengia

Tom Verrengia, President

Baseball bats have been traditionally made from hardwoods primarily Northern White Ash, and more recently, Rock and Sugar Maple. These trees take 40-45 years to grow to the point where they can be harvested for lumber suitable for bats. Once established, bamboo is one of the fastest growing plants in the world. The chutes can be continually harvested without damaging the underlying root structure, which will keep sending up additional chutes. Bamboo can be grown on land not suitable for other crops, or as a natural fence with its roots holding the soil and preventing erosion. Thus bamboo is an environmentally friendly alternative to traditional hardwoods as a material source for baseball bats. Bamboo also has a tensile strength greater than steel. A bamboo bat delivers superior performance and resistance to breakage, while retaining the feel of a hardwood bat.

Since bamboo chutes are hollow, bamboo bats are made by pressing bamboo "strips" into billets, and then turning these billets into bats. The Softball Bat Division specializes in making softball bats. All the softball bats have a two-tone finish with a clear lacquer handle and a colored barrel. Two examples are shown in the photos to the right. Lacquer is treated as an indirect material and included as part of variable overhead because the cost per bat is so small. Bamboo is the only direct material.

During 2018, the selling price will be $82 per bat. The Softball Bat Division expects to sell the following number of bats during 2018.

Quarter	First	Second	Third	Fourth
Bats sales expected	71,000	84,000	76,000	80,000

The collection pattern for Accounts Receivable is as follows:

65 percent of all sales are collected within the quarter in which they are sold o 35 percent of all sales are collected in the following quarter.

There are no bad debts/uncollectibles.

Due to higher than expected demand this year the Softball Bat Division expects to have no finished bats in inventory. To avoid having that problem in the coming fiscal year, the Softball Bat Division would like to have the ending inventory of bats at the end of each quarter equal 25 percent of the budgeted sales for the next quarter. They would like to have 20,000 bats on hand at the end of 2018.

Quarter First Ending FG inventory in bats as a percentage of the next	Second	Third	Fourth
quarters budgeted sales 25%	25%	25%	?
Ending FG inventory in bats ?	?	?	20,000

Each bat requires 40 ounces of bamboo strips. The Softball Bat Division buys bamboo for $0.90 per ounce and expects the price to remain constant throughout the year. They expect to have to have 409,000 ounces of bamboo strips on hand at the beginning of 2018. At the end of each of the first three quarters, the Softball Bat Division would like to have their direct materials inventory quantity to equal 10 percent of the amount required for the following quarters planned production. At the end of the year, Softball Bat Division would like to have 360,000 ounces of bamboo on hand.

Quarter First Ending DM inventory as a % of the next quarters	Second	Third	Fourth
production requirement 10%	10%	10%	?
Ending DM inventory in ounces ?	?	?	360,000

The Softball Bat Division buys its bamboo on account. It pays for 70% of its purchases of direct materials in the quarter in which they were purchased and 30% in the quarter after they were purchased.

Each bat requires .85 hours (51 minutes) of direct labor. Employees engaged in direct labor will be paid an estimated $16.00 per labor hour. Wages and salaries are paid on the 15^th and 30^th of each month.

Variable manufacturing overhead is estimated to be $14.00 per direct labor hour for the coming fiscal year. All variable manufacturing overhead expenses are paid for in the quarter incurred.

Fixed manufacturing overhead is estimated to total $540,000 each quarter, with $360,000 of the total amount each quarter representing depreciation on machinery, equipment and the factory. All other fixed manufacturing overhead expenses are paid in cash in the quarter incurred. Display the pre-determined overhead rate to the nearest penny, but do not round the underlying calculation.

Variable selling and administrative expenses are estimated to be $8.00 per bat sold. Fixed selling and administrative expenses are expected to total $280,000 each quarter, with $48,000 of the total amount each quarter representing depreciation on the office space, furniture and equipment. Other than depreciation, all selling and administrative expenses are paid for in the quarter incurred.

At the end of the fourth quarter of 2018 the Softball Bat Division plans to buy new machinery and equipment for $1,400,000. The new machinery and equipment will be acquired at the very end of the year, so it will not be used in production and sales during the coming year and it will not be depreciated until the following year. The Softball Bat Division expects to pay 25% down and finance the remaining 75% of the equipment cost with a note payable. No interest payable will accrue on the note payable until after December 31, 2018.

Softball Bat Division will pay quarterly dividends of $40,000 to its corporate parent, Bamboo Bat Company.

Bamboo Bat Company would like the Division to maintain a minimum cash balance of $200,000. If after accounting for cash receipts and disbursements (including dividends) in the cash budget, the budgeted cash available cash will fall below $200,000 in any quarter, the Division will need to borrow additional cash. The Division has arranged a line of credit allowing it to borrow in $10,000 increments. Assume borrowing will take place at the beginning of any quarter in which the available cash would otherwise be below $200,000 so that at no time during the quarter will the cash balance fall below $200,000. The bank charges the Division interest at the rate of 1.25% per quarter (paid at the end of each quarter). The Division will also pay down the principal balance on the line of credit if they have sufficient cash to do so, at the end of each quarter. In addition to interest payments, the Division will make a partial repayment of principal in $10,000 increments until a further $10,000 increment would reduce their ending cash balance below $200,000.

The budgeted balance sheet for the Softball Bat Division on December 31, 2017 (which is the same as the budgeted balance sheet at the beginning of business January 1, 2018) is presented below. The Bamboo Bat Company owns 100% of the Capital Stock of the Softball Bat Division.

SOFTBALL BAT DIVISION BAMBOO BAT COMPANY

BUDGETED BALANCE SHEET

DECEMBER 31, 2017

ASSETS LIABILITIES & EQUITY

Cash	$ 220,000	Accounts Payable	$ 925,000
Accounts Receivable Raw Material	1,400,000	Notes Payable	0
Inventory	368,100	Capital Stock	2,000,000
Plant and Equipment	1,700,000	Retained Earnings	__763,100
TOTAL ASSETS	$3,688,100	TTL LIAB. & SE	$3,688,100

Weretelnik Alex -Saved insert Draw Page Layout Farmulas Data Revin Vicw Tell me whst you wart to do Auto Sum A rill 2 wrap Text Merge & Center, $.96 , %a4% Conditional Format as Cell Formattir,3" Table- Styles Sort & Find & Filter Select Insert Delete .uu Format Painter 32 3) Direct Material Budget 33 Quarter Second 92,000.00 7,000 C0 31,000.00 35 Materials per unit 36 Production Needs 37 Add: Desired End In 38 Total Needed 39 Less: Begin Inv 40 ounces 40 ounces 3,280,000.00 308,000.00 3,588,00a.do 328,000.00 3,260,000.00 2,934,000.00 3,080,000.00 320,000.00 3,400,00o.do 308,000.00 3,092,000.00 2,782,800.00 3,200,000.00 360,000.00 328,000.00 4,009,000.00 409,000.00 3,599,000,00 320,000.00 3,240,000.00 2,916,000.00 409,000.0o 13,191,000.00 11,871,900.00 41 Materials to be Cash Disbursement Material Total 44 Quarter 45 Accounts Payable 25,000.00 3,239,100.00 Flrst Quarter Purchases 7 70x3,239,100.00 48 30%x 3,239,100.00 971,730.00 2,934,000.00 2,053,800.00 Second Quarter Purchase 50 70% x 2,934,000.00 51 30%x 2,934,000.00 880,2c0.c0 2,782,800.00 1,94796000 Third Quarter Purchase 53 7096x 2,782,800.00 54 30%x 2,782,800.00 834,840.00 Fourth Quarter Purchase 2,916,000.00 56 70% x 2,8B0,000.00 57 30% x 2,880,000.00 58 Total Cash Disbursements 3,192,370.00 2,828,160 00 11.922,100.00 61 4) Direct Labor Budget Type here to search 11/12/2017