1. Master Budget Project ASSIGNMENT for Chapter 14

Valley Distributors, a wholesale company, is considering whether to open a new distribution center. The center would open January 1, 2021. To make the decision, the planning committee requires a master budget for the centers first quarter of operation (January, February, and March of 2021).

Required You are to construct the first quarter master budget based on the following expectations:

a. January sales units are estimated to be 86,000 and the selling price is $66.00 per unit. The company collects 35% of the sales in cash and the remaining are 65% are credit sales. The company expects number of units to grow 5.5% per month. Prepare a sales budget. b. The company expects to collect 100% of accounts receivable in the month following the sale. Prepare a schedule of expected cash receipts. c. Cost of goods sold will be 60% of sales. Company policy is to budget an ending inventory balance equal to 35% of the next months projected cost of goods sold. Assume Valley expects April cost of goods sold to be $4,500,000. Valley had no inventory as of January 1, 2021. Prepare an inventory purchases budget. d. All inventory purchases are on account and not paid in cash at time of purchase. The company pays 45% of accounts payable in the month of purchase. It pays the remaining 55% in the following month. Prepare a schedule of expected cash payments for inventory purchases. e. Budgeted monthly selling and administrative expenses are:

Salary Expense (Fixed)

$400,000

Sales Commissions

4.5% of Sales

Supplies Expense (Fixed)

95,000

Utilities (Fixed)

$115,000

Depreciation on Center Equipment (Fixed)*

see below

Rent

4% of Sales

Miscellaneous (Fixed)

$180,000

*The capital expenditures budget shows that Valley must purchase $720,000 of equipment on January 1 to establish the new center. Valley will pay for the equipment on January 31. The equipment is expected to have a 5-year useful life and a $10,000 salvage value and will depreciate on a straight-line basis. Make sure to calculate depreciation ON A MONTHLY BASIS. Prepare a selling and administrative expense budget. f. Sales commissions and rent expense are paid in the month after the month in which they are incurred. All other expenses are paid in the month in which they are incurred. Prepare a schedule of cash payments for selling and administrative expenses. g. When completing the cash budget, remember to include the equipment that was purchased for $720,000 as noted in part e.

Valley had no cash as of December 31, 2020. Valley has access to a line of credit. Valley borrows and prepays from the line of credit on the last day of the month in increments of $4,000. It pays interest of 1.75% per month in cash on the last day of the month. There was no budgeted interest expense for January since no amount would be borrowed until January 31, 2021. Company policy is to maintain an ending cash balance of at least $30,000. Use this information and the budgets prepared in parts b, d, and f to prepare a cash budget. h. Complete the first quarter pro forma income statement. i. Complete the March 31 pro forma balance sheet. Here are some tips.

Accounts Receivable amounts still to be collected from customer as of 3/31 Inventory balance on hand as of 3/31 Accounts Payable amt of inventory purchased that company still owes as of 3/31 Sales Commission Payable amount owed to employees as of 3/31 Rent Payable amount owed to vendor for rent as of 3/31

Line of Credit amount still borrowed as of 3/31 (if any)

Retained Earnings remember formula to calculate retained earnings. Assume no beginning retained earnings and no dividends expected to be paid in 2021.

j. Complete the first quarter pro forma statement of cash flows. Make sure to consider if the activity should cause cash to increase or decrease. k. Enter 75,000 units in Jan. number of units sold and then record the new operating income. Please note this is different than net income. Operating income is before interest expense on the income statement. l. Please return original January sales to 86,000 units before submitting master project.

REMEMBER: You must use formulas throughout the entire budget including all the financial statements. The only places you will type in number are the following:

January column ONLY

# of units sold (budget 1)

% of Previous Months A/R (budget 2)

Less Beginning Inventory (budget 3)

% of Prior Months Purchases (budget 4)

% of Prior Months Sales Com. (budget 6)

% of Prior Months Rent Exp. (budget 6)

Beginning Cash Balance (budget 7)

Interest Expense (budget 7)

Purchase Equipment (budget 7)

January March columns:

Selling Price (budget 1)

Salary, Utilities, Supplies, and Misc expense (budget 5 only)

For depreciation: Type a formula for the calculation of monthly depreciation

Borrowing (Repayment) (budget 7)

Remember - Budget 3: March Ending Inventory needs to be calculated based on percentage of Aprils expected cost of goods sold, which will not be on your Excel file. You can type in the Aprils cost of goods sold amount multiplied by the percentage.