Week 5 Homework Assignment-Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calibri - 11 A A Wrap Text Auto Fil- General Paste BIU - A Merge & Center E Conditional Format as Cell Formatting Insert Delete Formar Clear- Table Styles Clipboard Font Alignment Number Styles Cells K11 A D F G H J 1 Comprehensive Budgeting Problem 20 points Silverman Manufacturing is preparing its master budget for the first quarter of the upcoming year. The following data pertain to Silverman Manufacturing's operations Current Assets as of December 31 (prior 2 3 4,500 Accounts receivable, net 49,000 6 Inventory 15,320 7 Property, plant and equipment, net 8 Accounts payable iCapital stock 10 Retained earnings S 121,500 42,400 125.000 22.920 (a) Actual sales in December were $70,000. Selling price per unit is projected to remain stable at $10 per unit throughout the budget period. Sales for the first 11 five months of the upcoming year are budgeted to nuary as follows: 80,000 13 ry 92.000 14 March 15 April 16 May 99,000 S 85,000 70% credit . All credit sales are collected the month following the sale. 17 (b) Sales are 30 % Silverman Manufacturing has a policy that states that each month's ending inventory of finished goods should be 25% of the following month's sales (in 18 units). each month's direct material purchases, 20 % are paid in the month of purchase, while the remiander is paid for in the month following purchase. Two (d) pounds of direct material is needed per unit at $2 per pound. Ending inventory of direct materials should be 10% of next month's production needs. (e) Monthly manufacturing conversion costs are $5,000 for factory rent, $3,000 1 20 manufacturing overhead. No depreciation is included in these figures. All expenses are paid in the month in which they are incurred. r other fixed manufacturing expenses, and $1.3 unit for variable Assgmt 1 Assgmt 2 Select destination and press ENTER or choose Paste PLL 1911M 0Conditional Format as Formatting Table Styles Insert Delete Format Cel % , Merge & Center A Paste BIU Cells Styles Numben Alignment Clipboard Font X K26 H G F D C R A (d) Of each month's direct material purchases, 20 % are paid in the month of purchase, while the remiander is paid for in the month following purchase. Two 19 pounds of direct material is needed per unit at $2 per pound. Ending inventory of direct materials should be 10 % next month's production needs. (e) Monthly manufacturing conversion costs are $5,000 for factory rent, $3,000 for other fixed manufacturing expenses, and $1.20 per unit for variable 20 manufacturing overhead. No depreciation is included in these figures. All expenses are paid in the month in which they are incurred. (f) Computer equipment for the administrative offices will be purchased in the upcoming quarter. In January, Silverman Manufacturing will purchase 21 equipment for $5,000 (cash) while February's cash expenditure will be $12,000 and March's cash expenditure will be $16,000. (g) Operating expenses are budgeted to be $1 per unit sold plus fixed operating expenses of $1,000 per month. All operating expenses are paid in the month in 22 which they ae incurred. (h) Depreciation on the building and equipment for the general and administrative offices is budgeted to be $4,800 for the entire quarter, which includes 23 depreciation on new acquisitions. (i) Silverman Manufacturing has a policy that the ending cash balance in each month must be least $4,000. It has a line of credit with a local bank. The company can borrow in increments of S1 t the beginning of each month, up to a total outstanding loan balance of $100,000. The interest ra on these loans is 1 % per month simple interest (not 24 end of the quarter. The company would also pay the accumulated interest at the end of the quarter on the funds borrowed during the quarter. ). Silverman Manufacturing would pay down on the line of credit balance if it has excess funds at the i) The company's income tax rate is projected to be 30 % of operating income less interest expense. The company pays $10,000 cash at the end of February in 25 estimated taxes. 26 Requirements 27 (1) Prepare a schedule of cash collections for January, February and March, and for the quarter in total, Use the followine format: Cash Collections Budget January February March Quarter 30 Cash Sales 31 Credit Sales Assgmt 1 Assgmt 2 + ENTER PLL 1911M