I need help please!
Create a cash budget include a schedule of cash collections and payments and create a finished goods inventory calculation
This is what I have so far. I NEED HELP WITH THE LAST PART AND I WANNA MAKE SURE I DID EVERYTHING RIGHT. ALSO NEED A FINISHED GOODS INVENTORY CALCULATION
PSC Industries Budget Project Spring 2019 INPUT SECTION SALES 2nd QuarterQuarter 4th Quarter 2018 43,000 1st Quarter 2019 37,300 4th Quarter 2019 43,000 2019 2019 Budgeted Sales in units 34,500 31,000 Budgeted Selling Price $530 per production unit (Finished Good) RECEIVABLES Receivables Collection Schedule 91.50% quarter of sale 5.00% quarter following sale 3.50% uncollectible 100.00% Policy Entire projected uncollectible receivables are written off each quarter INVENTORY COSTS Direct Labor 4.5 hours 19.50 per direct labor hour Payment for Raw Materials 85% quarter of purchase 15% quarterfollowing purchase Payment Policy + OPERATING EXPENSES Wages and Salaries Payment Payment Policy 2 times monthly on the 15th and 30th Fully paid each month Variable Selling & Administrative Exp 3.50% of selling price Fixed Selling& Administrative Exp Depreciation Other Selling & Administrative Exp $55,000 per quarter $180,000 per quarter CAPITAL EXPENDITURES Equipment Purchases $5,450,000 end of 4th quarter TREASURY ACTIVITIES Dividends to be Declared $400,000 per quarter Minimum Cash Balance $250,000 Required at end of every quarter Interest Rate for Investing (short-term) 3.50% Annually Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter Interest Rate for Investing (short-term) 3.50% Annually Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter All of the ending cash balance for the quarter remains in Cash at the beginning of the next quarter Interest Rate for borrowing 12% Annually Interest Policy $10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance Borrowing occurs at the beginning of the quarter Repayments occur at the end of the iquarter FLEXIBLE BUDGET Actual Units Produced First Quarter 43,000 units (use for Flexible Budget Tab only) Flexible Budget - Analyze at 2 Production Levels Production level 1 Production level 2 45,000 units 35,000 units PRIOR YEAR RESULTS PSC Industries Balance Sheet 12/31/2018 ASSETS Cash Inventory Accounts Receivable (net) Plant and Equipment 6,150,000 3,748,000 5,050,000 29,400,000 LIABILITIES AND EQUITY Accounts Payable Common Stock Retained Earnings 8,100,000 26,234,500 $ 10,013,500 44,348,000 1 SCHEDULE OF CASH COLLECTIONS Third Fourth Quarter First Second Annual Quarter Quarter Quarter 5 Credit Sales 19,769,00018,285,00016,430,00022,790,00077,274,000 Collected quarter of sale Collected quarter following sale 18,088,635$16,730,775 5,050,000 $ 15,033,45020,852,85070,705,710 821,500 $7,774,200 $ 78,479,910 988,450 914,250 S 15,947,700$21,674,350 Total Collections $23,138,635 1 Net Accounts Receivable Balance 1,139,500 S 1,139,500 1,139,500 4 SCHEDULE OF CASH PAYMENTS 5 Fourth Quarter Annual First Quarter Second Quarter Third Quarter 9 Purchases this quarter 12,255,343$ 10,280,031 8,403,038$9,488,656 $ 40,427,068 1 Payments for this quarter's purchases 2 Payments for last quarter's purchases S8,100,0 1,838,301.45 S1,542,004.65S 1,260,455.70 1,762 23 Total payments $18,517,042 $ 10,576,328 $8,684,587 $ 9,325,813$ 47,103,770 25 Accounts payable balance $1,423,298.40 1,423,298 7 CASH BUDGET First Quarter Second Quarter Third Fourth Quarter Annual 30 Quarter 6,150,000 $ 1,694,279 $ 1,735,5512 2,815,7506,150,000 1 Beginning Cash Balance 2 Cash Collections 33 From credit sales 4 current quarter 5 previous quarter 36 Total Cash Available $18,088,635$16,730,775 15,033,450 $20,852,850 $ 70,705,710 821,500 7,774,200 29,288,635$19,413,504$ 17,683,251$ 24,490,100 $84,629,910 5,050,000 $ 988,450 $ 914,250 $ 38 Cash Disbursements: 39 For Raw Materials 40 Current quarter, paid for 41 42 Direct Labor 43 Overhead 4 Selling and Administrative Expense 10,417,042$8,738,026$7,142,582$8,065,358$ 34,363,008 8,100,000 1,838,301$1,542,005$ 1,260,456 12,740,762 4,633,200$3,334,500$2,764,125 $3,378,375 14,110,200 3,172,200$2,539,500$ 2,261,625$ 2,560,875$ 10,534,200 977,650 3,424,590 Previous quarter, paid for 871,915 $ 819,975 $ 755,050 $ 977,650 3,424,590 $5,450,000 $ 5,450,000 400,000 400,000 27,594,35717,670,303 $14,865,387$ 22,092,713 $ 81,022,760 871,915 $ 819,975S 755,050 $ Selling and Administrative Expense Equipment Purchases Dividends 400,000 $ 400,000 S 400,000 $ Total Cash Disbursements Actual cash balance Minimum cash balance Excess (Deficiency) of Cash Leave Financing section empty: Financing: (should not be required) 1,694,279$1,735,551$2,815,750 $ 2,397,251 $ 2,397,251 250,000 250,000 251 2,147,251 250,000 $ 1,444,279 $ 250,000 S 250,000 $ 2,565,750 2,147, Borrowing Repayment Interest Total Financing Cash Increase (Decrease) Cash Balance before interest on balance Interest Earned on Cash Balance Cash Balance after interest on balance Grayed cells are not used
