Hi i need help with solving the cash budget schedule.

Managerial Accounting Team Project FALL 2016 - Version 3 Managerial Accounting - Professor Neururer We Gnomes Things Co. produces garden gnomes. Using the information provided on this sheet, prepare WGT's budget for the year 2016 Team Project Guidelines: 1. The project is due the Beginning of CLASS on November 17th 2. Email ONE copy of your Excel Spreadsheet prior to class. Make sure everyone's name is on it! 3. Grading scheme: - 15 points: Peer evaluation - 10 points: Business evaluation - Question 11 - 20 points: Correct calculations and schedules (all but question 11) 4. Use the template provided to create the following: Schedule 1: Sales Budget Schedule 2: Cash receipts Schedule 3: Production Budget in Units Schedule 4: Direct Materials Purchases Schedule 5: Cash Purchases Budget Schedule 6: Direct Labor Budget Schedule 7: Overhead Budget Schedule 8: Selling and Administrative Expense Budget Schedule 9: Ending Finished goods Schedule 10: Budgeted cost of goods sold Schedule 11: Cash Budget Schedule 12: Budgeted Income Statement Schedule 13: Budgeted Statement of Retained Earnings Schedule 14: Budgeted Balance Sheet WGT Balance Sheet Cash Accounts Receivable Inventory Raw Materials FG Inventory Land Buildings and Equip Accum Depr Total Assets Accounts Payable Capital Stock RE Total Liab and Stockholders 01/01/2016 $ 22,000 12,000 350 4,950 8,000 70,000 (29,200) $ 88,100 $ $ 2,580 17,500 68,020 88,100 Use the following to complete the team project: 1. The marketing department projects the following sales levels: Quarter 1, 2016 Quarter 2, 2016 Quarter 3, 2016 Quarter 4, 2016 Quarter 1, 2017 Quarter 2, 2017 8,700 9,800 4,200 3,800 4,600 2,800 Gnomes Gnomes Gnomes Gnomes Gnomes Gnomes The statues are expected to sell for $45.00 EACH 1. WGT sell all merchandise on credit. Historically, WGT receives 50% quarter's sales during the quarter and 50% in the next quarter. of each 2. WGT plans to stock the ending inventory of finished goods to equal 45% of the next quarter's sales. Finished Goods inventory at 01/01/2016 is 300 statues at a cost of $16.5 per unit. 3. The statues are made with both cement and plaster. The statues each require pounds of cement at a cost of $0.70 per pound. 8.00 In addition, the statues require 7 pounds of plaster at $0.60 per pound. At 12/31/2015 WGT had 500 pounds of cement on hand and no plaster on hand. WGT plans to keep 60% of the cement and 60% of the plaster required required for next quarter's production in inventory at the end of each quarter. WGT pays for purchases as follows: 50% in the quarter purchased and 50% in the quarter following the purchase. 4. Each of the statue requires 0.7 hrs of direct labor at $7.25 per hour. Employees are paid on the last day of each month for that month's work. Other costs are paid in the quarter unless otherwise noted. 5. Variable overhead is estimated at $3.00 per year. Fixed overhead includes depreciation of per direct labor hour. Fixed overhead costs are $8,000 per year. $40,000 6. Variable selling and administrative costs are $4.50 $30,000 per year. (This includes per unit. Fixed selling and administrative costs are $7,500 of depreciation per year) 7. WGT makes quarterly income tax payments of $8,000 before taxes (consider underpayments on your balance sheet). The coprorate income tax rate is 8. WGT pays quarterly dividends of $5,000 40% of net . 9. WGT will purchase additional equipment using cash on the following schedule (depreciation is already included above): Quarter 1 Quarter 2 Quarter 3 Quarter 4 $ $ $ $ 4,800 3,500 - 10. WGT has to maintain a minimum cash balance of $10,000 All borrowings are made at the end of the quarter and paid back at the end of the first quarter where there is a cash surplus to make the debt payments. All borrowings and repayments are made in $1,000 increments. Interest is paid at the time of repayment and is calculated at 3.00% per year (no Compounding). 11. In addition to completing the budget, write a one to two page(s) executive summary as though you are the CFO and you are informing the CEO of the following informaton: What projections were made in creating the budget? Were these projections reasonable given the current economic environment and the industry in which WGT Co. operates What might occur if your projections are incorrect (some financial figures are helpful here, but not a full budget) We note that actual dollar values for WGT Co. do not reflect current market values or conditions. Ignore the dollar values and discuss in general, the economic and industry trends for the industry WGT Co. Project #3 Team Project FALL 2016 - Version 3 Managerial Accounting - Professor Neurure Team Members: - 15 points: Peer evaluation - 10 points: Business evaluation - Ques - 20 points: Correct calculations and sc Schedule 1: Sales Budget Sales in Units x sales price Sales revenue Cash received from current quarter sales Accounts Receivable from current quarter sales Schedule 2: Cash receipts Current quarter sales - cash receipts Cash Receipts from prior quarter receivables Q1 Q2 8,700 45 391,500 9,800 45 441,000 195,750 195,750 220,500 220,500 Q1 195,750 12,000 207,750 Q2 220,500 195,750 416,250 Project #3 Schedule 3: Production Budget in Units Beginning Balance: Finished Goods (units) Plus: Production of Finished Goods (units) Less: Sales of Finished Goods (units) Ending Balance: Finished Goods (units) Q1 Q2 Schedule 4: DM Purchases: Cement Units to be produced Amt of cement per unit (lbs.) Cement needs for production (lbs.) Q1 8,700 8 69,600 9,800 8 78,400 Beginning Balance: Cement (lbs) Plus: Cement Purchases (lbs) Less: Cement Used in Production (lbs) Ending Balance: Cement 500 116,140 69,600 47,040 47,040 51,520 78,400 20,160 Cement to be purchased (lbs) Cement cost per lb. Total cement purchase cost 116,140 $0.70 81,298 $ 51,520 $0.70 36,064 300 12,810 8,700 4,410 $ 4,410 7,280 9,800 1,890 Q2 Schedule 4: DM Purchases: Plaster Units to be produced Amt of plaster per unit (lbs.) Plaster needs for production (lbs.) Q1 8,700 7 60900 9,800 7 68600 Beginning Balance: Plaster (lbs) Plus: Plaster Purchases (lbs) Less: Plaster Used in Production (lbs) Ending Balance: Plaster 102,060 60,900 41,160 41,160 45,080 68,600 17,640 Plaster to be purchased (lbs) Plaster cost per lb. Total plaster purchase cost $ 102,060 $0.60 61,236 $ 45,080 $0.60 27,048 Total DM purchase cost: Cement + Plaster $ 142,534 $ 63,112 Schedule 5: Cash Purchases Budget Current Quarter Purchases: Cash Q1 71,267 Q2 Q2 31,556 Project #3 Purchases Payable: Cash Payments Total Cash Outlay per Quarter: 2,580 73,847 71,267 102,823 Project #3 Schedule 6: Direct Labor Budget Units to be produced DL needed per unit DL hours needed cost per hour Total Budgeted Direct Labor (dollars) Q1 Q2 Schedule 7: Overhead Budget DL hours needed VOH rate /hour Budgeted variable OH Q1 Q2 Q1 Q2 8,700 Depreciation Other fixed costs Budgeted fixed OH Total budgeted OH expense Schedule 8: SG&A Budget Units sold Variable Selling & Administrative Rate Budgeted Variable Selling & Administrative Costs Depreciation Other SG&A Budgeted Fixed Selling & Administrative Costs Total budgeted SG&A expense Project #3 Schedule 9: Ending Finished goods Qty. Per Unit Cost Per Qty. Total OH Total Production Units Cost Per Unit Ending Balances: Inventory - DM, DL DM - Cement DM - Plaster Direct Labor Total Per Unit Inventory Costs: Units in Ending Inventory: Ending Inventory: DM, DL Ending Balance: Inventory - Overhead Variable Overhead Fixed Overhead Total Per Unit Inventory Costs: Units in Ending Inventory: Ending Inventory: Overhead Total Ending Inventory Balance: Finished Goods Schedule 10: Budgeted COGS Beginning Balance: Finished Goods Costs incurred during the period Direct materials used - cement Direct materials used - plaster Direct labor used Overhead - total Cost of Goods Available For Sale Ending Inventory Cost of Goods Sold Project #3 Schedule 11: Cash Budget Cash Sources: Beginning Balance: Cash Cash Receipts Cash available for use Cash Uses: Disbursements for purchases Direct Labor Overhead payments Sales & General Administrative Payments Taxes Paid Dividends paid Equipment purchases Total Cash Disbursements Cash Balance - pre debt Loans (repayments) Interest Payments on Loans Net Borrowing Impact on Cash Ending Balance: Cash Schedule 12: Budgeted income statement Sales Cost of Goods Sold Gross Margin Selling and General Administrative Expenses Interest Expense Net Income before Taxes Tax Expense Net Income Schedule 13: Budgeted Retained Earnings Retained Earnings Calculation Beginning Balance: RE Plus: Net Income Less: Dividends Paid Retained Earnings Q1 Q2 Project #3 Schedule 14: Budgeted Balance sheet Cash Accounts Receivable Inventory Raw materials Finished Goods PP&E Land Building and Equipment Accumulated Depreciation Acct Payable Tax Payable Capital stock RE Total L&SE Beg Bal Jan. 1, 2016 22,000 12,000 350 4,950 8,000 70,000 (29,200) 88,100 2,580 17,500 68,020 88,100 Project #3 m Project FALL 2016 - Version 3 rial Accounting - Professor Neururer Your Points points: Peer evaluation points: Business evaluation - Question 11 points: Correct calculations and schedules (all but Q-11) Q3 Q4 4,200 45 189,000 3,800 45 171,000 94,500 94,500 85,500 85,500 Q3 94,500 220,500 315,000 Q4 85,500 94,500 180,000 Total 26,500 1,192,500 Total 596,250 522,750 1,119,000 Project #3 $ Q3 1,890 4,020 4,200 1,710 Q4 Q3 Q4 Total 1,710 4,160 3,800 2,070 4,200 8 33,600 3,800 8 30,400 20,160 31,680 33,600 18,240 18,240 34,240 30,400 22,080 31,680 $0.70 22,176 $ 34,240 $0.70 23,968 $ Q3 Q4 28,270 26,500 Total 26,500 212,000 233,580 212,000 233,580 163,506 Total 26,500 4,200 7 29400 3,800 7 26600 17,640 27,720 29,400 15,960 15,960 29,960 26,600 19,320 $ 27,720 $0.60 16,632 $ 29,960 $0.60 17,976 $ 122,892 $ 38,808 41,944 $ 286,398 Q3 19,404 $ Q4 185,500 204,820 185,500 204,820 Total 20,972 Project #3 31,556 50,960 19,404 40,376 268,006 Project #3 Q3 Q4 Total Q3 Q4 Total Q3 Q4 Total Project #3 Cost Per Unit OH Per Unit Total Project #3 Q3 2016 Dec. 31, 2016 Q4 Total Project #3 End Bal Dec. 31, 2016 Project #3 Team Project FALL 2016 - Ve Managerial Accounting - Profess Team Members: - 15 points: Peer evaluation - 10 points: Business evaluatio - 20 points: Correct calculation Schedule 1: Sales Budget Sales in Units x sales price Sales revenue Cash received from current quarter sales Accounts Receivable from current quarter sales Schedule 2: Cash receipts Current quarter sales - cash receipts Cash Receipts from prior quarter receivables Q1 8,700 45 391,500 195,750 195,750 Q1 195,750 12,000 207,750 Project #3 Schedule 3: Production Budget in Units Beginning Balance: Finished Goods (units) Plus: Production of Finished Goods (units) Less: Sales of Finished Goods (units) Ending Balance: Finished Goods (units) Q1 Schedule 4: DM Purchases: Cement Units to be produced Amt of cement per unit (lbs.) Cement needs for production (lbs.) Q1 300 12,810 8,700 4,410 8,700 8 69,600 Beginning Balance: Cement (lbs) Plus: Cement Purchases (lbs) Less: Cement Used in Production (lbs) Ending Balance: Cement Cement to be purchased (lbs) Cement cost per lb. Total cement purchase cost 500 116,140 69,600 47,040 $ 116,140 $0.70 81,298 Schedule 4: DM Purchases: Plaster Units to be produced Amt of plaster per unit (lbs.) Plaster needs for production (lbs.) Q1 Beginning Balance: Plaster (lbs) Plus: Plaster Purchases (lbs) Less: Plaster Used in Production (lbs) Ending Balance: Plaster 102,060 60,900 41,160 8,700 7 60900 Plaster to be purchased (lbs) Plaster cost per lb. Total plaster purchase cost $ Total DM purchase cost: Cement + Plaster $ 102,060 $0.60 61,236 142,534 Project #3 Schedule 5: Cash Purchases Budget Current Quarter Purchases: Cash Purchases Payable: Cash Payments Total Cash Outlay per Quarter: Q1 71,267 2,580 73,847 Project #3 Schedule 6: Direct Labor Budget Units to be produced DL needed per unit DL hours needed cost per hour Total Budgeted Direct Labor (dollars) Q1 Schedule 7: Overhead Budget DL hours needed VOH rate /hour Budgeted variable OH Q1 8,700 Depreciation Other fixed costs Budgeted fixed OH Total budgeted OH expense Schedule 8: SG&A Budget Units sold Variable Selling & Administrative Rate Budgeted Variable Selling & Administrative Costs Depreciation Other SG&A Budgeted Fixed Selling & Administrative Costs Total budgeted SG&A expense Q1 Project #3 Schedule 9: Ending Finished goods Qty. Per Unit Ending Balances: Inventory - DM, DL DM - Cement DM - Plaster Direct Labor Total Per Unit Inventory Costs: Units in Ending Inventory: Ending Inventory: DM, DL Ending Balance: Inventory - Overhead Variable Overhead Fixed Overhead Total Per Unit Inventory Costs: Units in Ending Inventory: Ending Inventory: Overhead Total OH Total Ending Inventory Balance: Finished Goods Schedule 10: Budgeted COGS Units Beginning Balance: Finished Goods Costs incurred during the period Direct materials used - cement Direct materials used - plaster Direct labor used Overhead - total Cost of Goods Available For Sale Ending Inventory Cost of Goods Sold Project #3 Schedule 11: Cash Budget Cash Sources: Beginning Balance: Cash Cash Receipts Cash available for use Cash Uses: Disbursements for purchases Direct Labor Overhead payments Sales & General Administrative Payments Taxes Paid Dividends paid Equipment purchases Total Cash Disbursements Cash Balance - pre debt Loans (repayments) Interest Payments on Loans Net Borrowing Impact on Cash Ending Balance: Cash Schedule 12: Budgeted income statement Sales Cost of Goods Sold Gross Margin Selling and General Administrative Expenses Interest Expense Net Income before Taxes Tax Expense Net Income Schedule 13: Budgeted Retained Earnings Retained Earnings Calculation Beginning Balance: RE Plus: Net Income Less: Dividends Paid Q1 Project #3 Retained Earnings Project #3 Schedule 14: Budgeted Balance sheet Cash Accounts Receivable Inventory Raw materials Finished Goods PP&E Land Building and Equipment Accumulated Depreciation Acct Payable Tax Payable Capital stock RE Total L&SE Beg Bal Jan. 1, 2016 22,000 12,000 350 4,950 8,000 70,000 (29,200) 88,100 2,580 17,500 68,020 88,100 Project #3 Team Project FALL 2016 - Version 3 Managerial Accounting - Professor Neururer Your Points - 15 points: Peer evaluation - 10 points: Business evaluation - Question 11 - 20 points: Correct calculations and schedules (all but Q-11) Q2 Q3 Q4 9,800 45 441,000 4,200 45 189,000 3,800 45 171,000 220,500 220,500 94,500 94,500 85,500 85,500 220,500 195,750 416,250 Q3 94,500 220,500 315,000 Q2 Q4 85,500 94,500 180,000 Total 26,500 1,192,500 Total 596,250 522,750 1,119,000 Project #3 Q2 Q3 4,410 7,280 9,800 1,890 1,890 4,020 4,200 1,710 Q2 $ Q4 Total 1,710 4,160 3,800 2,070 Q3 28,270 26,500 Q4 Total 26,500 9,800 8 78,400 4,200 8 33,600 3,800 8 30,400 47,040 51,520 78,400 20,160 20,160 31,680 33,600 18,240 18,240 34,240 30,400 22,080 51,520 $0.70 36,064 $ 31,680 $0.70 22,176 $ 34,240 $0.70 23,968 $ Q2 Q3 9,800 7 68600 212,000 233,580 212,000 Q4 4,200 7 29400 233,580 163,506 Total 26,500 3,800 7 26600 185,500 41,160 45,080 68,600 17,640 17,640 27,720 29,400 15,960 15,960 29,960 26,600 19,320 $ 45,080 $0.60 27,048 $ 27,720 $0.60 16,632 $ 29,960 $0.60 17,976 $ 122,892 $ 63,112 38,808 41,944 286,398 $ $ 204,820 185,500 $ 204,820 Project #3 Q2 31,556 71,267 102,823 Q3 19,404 31,556 50,960 Q4 Total 20,972 19,404 40,376 268,006 Project #3 Q2 Q3 Q4 Total Q2 Q3 Q4 Total Q2 Q3 Q4 Total Project #3 Cost Per Qty. Total Production Cost Per Unit Cost Per Unit OH Per Unit Total Project #3 Q2 Q3 2016 Dec. 31, 2016 Q4 Total Project #3 Project #3 End Bal Dec. 31, 2016