Master Budget Project Husker Corporation is a manufacturing company that produces one product. The Corporation is working on their 1st quarter budget and needs your help. The master budget will be based on the following information: a. Unit sales for the upcoming year are projected as follows: Jan. 250, Feb. 300, March 300, April 350 and May 275. b. The selling price is $30 per unit. c. Husker Corporation's desired ending inventory for finished goods is 20% of next month's sales. d. Each finished good takes 1/2 hour of direct labor and 3 units of direct materials. Employees are paid $9.50 per hour, and one unit of direct materials costs $2.50. e. At the end of each month, Husker Corp. plans to have 30% of the direct materials needed for the next month's production units. f. Fixed overhead totals $42,000 for the entire year. Of this total, $12,000 represents depreciation. All other fixed expenses are paid for in cash in the month incurred. The fixed overhead rate per unit is $4.50/unit (note this is based on estimates for the entire year). g. Variable overhead is budgeted at $2 per unit produced. All variable overhead expenses are paid for in the month incurred. h. Fixed selling and administrative expenses total $2,400 per year, including $600 depreciation per year i. Variable selling and administrative expenses are budgeted at $1.50 per unit sold. All selling and administrative expenses are paid for in the month incurred. j. All sales are credit sales. Husker Corp. collects 65% of all sales within the month of the sale and the other 35% is collected in the following month. There are no bad debts. Accounts receivable at year end totaled $2,362.50. k. Husker Corp. buys direct materials on account. Half of the purchases are paid for in the month of acquisition, and the remaining half are paid for in the following month. Accounts payable at the end of last year totaled $1,000.00. 1. In March, $2,000 of equipment will be purchased with cash. m. The beginning cash balance at the beginning of the budgeted year is $12,000. D 1 Requirement Sales Budget Feb. Dec Jan March April May 250 300 300 Juntes Mu bly by: Seling Price Total Sales Revenue 330 530 330 17.500 59.000 sinn Requirement Production Budget Jan Feb. 10 Dec March April May Unites 350l Desired ending very eginning nventory 14 units to produce Direct Materiais Budget Feb. 260 March April 300 1 Beurement 11 TE Units to the produced y by Quantity of OM od per unit -Quantity of OM needed for production 780 900 heet La aaste BIU -A Merge & Center x B D A Total cost of DM purchases Requirement #4 Direct Labor Budget Jan. Feb. Dec. March 1st Quarter Units to be produced Multiply by: DL hrs per unit Multiply by: DL rate -Total Direct Labor Budget Requirement 5 Overhead Budget Jan. Feb. Dec. March 11 Quarte Units to produce Multiply by: variable overhead rate -Variable overhead Axed Overhead Total Overhead Budget "No Requirement 86 6 Dec. Operating Expenses Budget jan. Feb. March toe Sheet1 + Ready O 29 A MacBook esc 8 F2 FE RO Fa 888 is | Moup us Santa Budete came for the su cas Budgeted manufacturing cost per unit ON berat D D *VOD -FON Dernt OM Toalett Nel income Cash Budget ret Det 14 March SORG mo ** Op ma Paste BIU a. Av = = IM Merge & Center C24 X fx B D A FTUAT UP. Exp. 52 Budget 53 54 Requirement #7 But Budgeted Income Statement for the 1st quarter DM 55 Sales 1st Qtr SALES TOTAL +D 56 ECGS Budgeted manufacturing cost per unit (cell "H59")X unit sales (cell "F4") +v0 57 Gross profit +FC 58 Op. Exp. To Net Income 59 60 61 Requirement #8 62 63 Cash Budget Jan. Dec. Feb. March 1st Quarter 64 beg. Cash "The Sales collected in 6S current month +Sales collected 66 from prior month 67 DM Current Month DM Prior Month 68 69 -DL 70 -OH **NO 71 Op. Exp. *** NO 72 Equipment Ending Cash 73 74 Sheet1 Budgeted manufacturing cost per unit DM per unit Cost per direct matieral XDM per unit +DL per unit Labor ratex DL hours per unit +VOH per unit VOH rate based on units produced +FOH per unit FOH loven at $4.50/unit) Total per unit cost April "The ending cash balance of one month becomes the beginning cash balance the following month. You can NOT sum across here. The beginning cash balance for the first quarter come "Remember A/R is the amount not collected from customers at a certain date. Remember A/P is the amount not paid to vendors at a certain date **Note, make sure the total entered here does not include depreciation. That is a non-cash expense and does not cash to decrease **Note, make sure the Op. Exp total entered here does not include depreciation. That is a non-cash expense and does not cath to decrease. Paste BIU 5 > > > E ili Merge & Center +5 +5 $ D 24 x fx A B D E per un Multiply by: DL rate =Total Direct Labor Budget Requirement #5 Overhead Budget Jan. Feb. 8 Dec. March Ist Quarter Units to produce No Multiply by: variable overhead Irate Variable 1 overhead 2 +Fixed Overhead -Total Overhead 3 Budget 4 5 Requirement #6 -6 37 Operating Expenses Budget Jan. Feb. Dec. March 1st Quarter Unit sales 18 Multiply by: variable op. exp. 59 rate 50 Variable op exp. Note + Fixed op. exp 51 Total op. exp. 52 Budget 53