This is due in 1 1/2 hrs I need the answers and the excel formulas on how to do this question, that way I can learn how to do it.
| (A) Production Budget | January | February | March |
| Budgeted Unit Sales | 1,200 | 1,000 | 1,600 |
| Add:Desired Ending finished inventory | | | |
| Next period budgeted sales units | 1,000 | 1,600 | 1,400 |
| Ratio of inventory to future sales | 30% | 30% | 30% |
| Desired ending inventory units | 300 | 480 | 420 |
| Total Required Units | 1,500 | 1,480 | 2,020 |
| Less: Beginning finished inventory | 340 | 300 | 480 |
| Units to produce | 1,160 | 1,180 | 1,540 |
The Sweetwater Candy Company is preparing its budget for next year and has asked you to prepare a cash budget for the first quarter (January-March) of next year Selling price for a single box of choralates: $25 per unit % collected November December January February March . Burgeted total sales are: 40% cash sales 60% credit sales Cash Receipts from sales Sales in s Cash receipts from Cash Sales Collections of month prior Collections of 2 months prior Total cash receipts Credit sales are collected in cash as follows: 75% collected in the month following the sale 25% collected in the 2nd month following the sale * collected December January February March At December 31, the balance in Accounts Heceivable is $24,750, which represents the uncollected portions of the Novinhar and December sales November total sales 1000 units December total sales 1400 units Budgeted sales for the next four months follow: January February March April Sales In units 1,200 1,000 1,500 Raw materials (chocolate) purchases are paid in cash as follows: 40% paid in the month of purchase 60% paid in the month following purchase Cash Payments for purchases DM purchases Cash payments for Current period purchases Prior period purchases Total cash payments 1,400 January February March At December 31, the balance of Accounts Payable is $4,977, which represents the unpaid portion of December's raw materials (chocolate purchases, The actual cost of raw materials in December was $8,295. HINT: You must pull the raw materials casts from the Production Budget. : Direct labor and variable overhead costs are paid in the month applied . Fixed overhead includes $2,520 par manth af depreciatian an factory cquipment. The remaining fixed overhead costs are paid evenly throughout the year. HINT: You must pull the direct labor and owerhear costs from the Production Budget. . The following operating expenses are paid in the ironth incurred: Shipping Is 2% of sales. Sales commissions are 10% of sales. Office salaries are $3,000 per month. Office rent is $1,200 per month. Ini January, rangement plans to pay $10,000 of cash dividends lu ils shareholders. Cash Budget Reginning cash balance + Cash receipts for sale + Sale of equipment ul Total Cash Available Cash Payments for: Direct materials Direct Labor Variable OH Fixed OH Shinaing Expense Sales Commissions Office Salaries Office Rent Cash Dividende Interest on Luuni Total Cash Payments Preliminary cash balance + Amount hormowad - Annuunil of loan repaid Ending cash balance Ending Luunblatice Interest rate per month . In March, management expects to sell equipment that onginally cost $12,000 at a gain of S1,500. Accumulated depreciation on this equipment at the time of sale will be $7,000. The equipment will be sold for cash. A minimum cash balance of $10,000 is required. The cash balance at December 31 is $10,200 Loans are obtained at the end of a month in which a cash shortage occurs, Interest is 124 annually for 1% per month), based on the loan balance at the beginning of the month, and must be If an excess of cash exists, Ivari repayments are inade at the end of the month. At December 31, the loan balance is $0 Required: (A.) Prepare the company's cash burget for each of the month of January, February, and March that includes inurmalin on the luar balance (R.) Prepara sumalametary Scharlles for cash receipts from customers and cash disbursements for raw materials
