Here is all the data set information for the excel sheets.

Wasatch Manufacturing is preparing its master budget for the first quarter of the upcoming year. The following data pertain to Wasatch Manufacturings operations:

Relevant Account Balances as of December 31 (prior year):

Cash - $17,000

Accounts Receivable, net - $73,500

Accounts Payable - $37,000

Sales and connections:

Actual sales in December were $105,000. Selling price per unit is projected to remain stable at $15 per unit throughout the budget period. Sales for the first five months of the upcoming year are budgeted to be as follows:

Month

January

February

March

April

May

Total Sales

$120,000

$135,000

$129,000

$141,000

$102,000

Sales are 30% cash and 70% credit. All credit sales are collected in the month following the sale.

Production & Materials:

Wasatch Manufacturing has a policy that states that each months ending inventory of finished goods should be 20% of the following months sales (in units).

Of each months direct materials purchases, 25% are paid for in the month of purchase, while the remainder is paid for in the month following purchase. Four pounds of direct materials is needed per unit at $1.00 per pound. Ending inventory of direct materials should be 15% of next months production needs.

Conversion Costs:

Most of the labor at the manufacturing facility is indirect, but there is some direct labor incurred. Each unit requires 0.10 direct labor hours. The direct labor wage rate is $15 per hour. All direct labor is paid for in the month in which the work is performed.

Monthly manufacturing overhead costs are $10,000 for factory rent, $6,000 for other fixed manufacturing expenses, and $1.25 per unit for variable manufacturing overhead. No depreciation is included in these figures. All expenses are paid for in the month in which they are incurred.

Operating Expense:

Operating expenses are budgeted to be $1.30 per unit sold plus fixed operating expenses of $2,200 per month. All operating expenses are paid in the month in which they are incurred.

Cash Data:

Computer equipment for the administrative offices will be purchased in the upcoming quarter. In January, Wasatch Manufacturing will purchase equipment for $20,000 (cash), while Februarys cash expenditures will be $8,000, and Marchs cash expenditure will be $25,000.

Wasatch Manufacturing has a policy that the ending cash balance in each month must be at least $15,000. It has a line of credit with a local bank. The company can borrow in increments of $1,000 at the beginning of each month, up to a total outstanding loan balance of $100,000. The interest rate on these loans is 1.25% per month simple interest (not compounded). The company would pay down on the line of credit balance in increments of $1,000 if it has excess funds at the 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.

Income Statement:

Depreciation on the building and equipment for the general and administrative offices is budgeted to be $10,000 for the entire quarter, which includes depreciation on new acquisitions.

For purposes of the Budget Manufacturing Cost Per Unit calculation Fixed MOH is $.75.

The companys income tax rate is projected to be 23% of operating income less interest expense. The company pays $28,000 cash at the end of February in estimated taxes.

I have provided all infromation including the wxcel sheets I have done before hand. please help me with the last three tables, THANK YOU!

G H 1 2 3 4 5 WASATCH MANUFACTURING Master Budget NOTE: Cells highlighted in blue contain static numbers (inputs) and not formulas. Sales Budget December February Unit sales 7,000 8,000 9,000 Unit selling price 15 15 15 Total sales Revenue 105,000 120,000 135,000 6 January 7 March 8,600 15 129,000 April 9,400 15 141,000 May 6,800 15 102,000 8 9 10 11 12 13 Reg. 1 Cash Collections Budget January 36,000 73,500 109,500 February 40,500 84,000 124,500 March 38,700 94,500 133,200 Quarter 115,200 252,000 367,200 14 15 Cash sales 16 Credit sales 17 Total collections 18 19 Reg. 2 20 21 22 23 Unit sales 24 Plus: Desired ending Inventory 25 Total needed 26 Less: Beginning inventory 27 Units to produce 28 29 Reg. 3 Production Budget January 8,000 1,800 9,800 1,600 8,200 February 9,000 1,720 10,720 1,800 March 8,600 1,880 10,480 1.720 8,760 Quarter 25,600 1,880 27.480 1,600 25,880 31 Direct Materials Budget Template + Ready Excel Budget Problem Template View Tell me Define Name AutoSave Dom BE Home Insert Draw Page Layout Formulas Data Review fx E. ?-A Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & Function Used Time Reference Trig F62 x fx Trace Precedents a Trace Dependents Remove Arrows For More Functions Create from Selection D 29 Req. 3 March 8,760 4 31 32 33 34 35 36 37 38 39 40 41 42 43 Units to be produced Multiply by: Quantity of DM needed per unit Quantity of DM needed for production Plus: Desired ending Inventory of DM Total quantity of DM needed Less: Beginning inventory of DM Quantity of DM to purchase Multiply by: Cost per pound Total cost of DM purchases Direct Materials Budget January 8,200 4 32,800 5,352 38,152 14.920) 33,232 1.00 33,232 February 8,920 4 35,680 5,256 40,936 15,352) 35,584 1.00 35,584 35,040 5,328 40,368 15,256) 35,112 1.00 35,112 Quarter 25,880 4 103,520 5,328 108,848 14.920) 103,928 1.00 103928 44 May 6,800 45 46 Unit Sales Plus: Desired End Inventory Total Needed Less: Beginning Inventory Units to produce DM needed per unit Quantity of DM needed for production April 9,400 1,360 10,760 1,880 8,880 4 35,520 Reg. 4 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 March Cash Payments for Direct Material Purchases Budget January February 37,000 8,308 24,924 8,896 December purchases (From AP) January purchases February purchases March purchases Tntal dichircaments Template Quarter 37,000 33,232 35,584 8,778 114 5941 26,688 8.778 35.466 45 R 33820 Ready Insert Draw Page Layout Formulas Data Review View Tell me v [? - A - AutoSum Recently Financial Logical Text Used Define Name Create from Selection Trace Precedents a Trace Dependents FRemove Arrows Formu Sho ert tion Date & Lookup & Math & Time Reference Trig More Functions ex fx A B C D H 37,000 8,308 December purchases (From AP) January purchases February purchases March purchases Total disbursements 24,924 8,896 26,688 8.778 35.466 37,000 33,232 35,584 8,778 114.594 45,308 33,820 Req. 5 Cash Payments for Direct Labor Costs January February 8,200 8,920 0 0 820 892 15 15 122.000 133.800 Quarter 25,880 0 Units Produced Multiply by: Hours per unit Direct Labor Hours Multiply by: Direct Labor rate per hour Direct Labor Cost March 8,760 0 876 15 131.400 2,588 15 388,200 Reg. 6 Cash Payments for Manufacturing Overhead Budget January February Rent (fixed) 10,000 10,000 Other MOH (fixed) 6,000 6,000 Variable manufacturing overhead 10.250 11.150 Total disbursements 26.250 27150 March 10,000 6,000 10.950 26,950 Quarter 30,000 18,000 32,350 80,350 Reg. 7 Variable operating expenses Fixed operating expenses Total disbursements Cash Payments for Operating Expenses Budget January February 10,400 11,700 2,200 2,200 12,600 13,900 March 11,180 2,200 13,380 Quarter 33,280 6,600 39,880 Template A B D E Reg. 8 90 91 92 Combined Cash Budget January 93 February March Quarter 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 Cash balance, beginning Plus: cash collections (req. 1) Total cash available Less cash payments: DM purchases (req. 4) Direct labor (req. 5) MOH costs (reg 6) Operating expenses (req 7) Tax payment Equipment purchases Total cash payments Ending cash before financing Financing: Borrowings Repayments Interest Total financing Cash balance, ending Req. 9 112 113 114 115 116 117 118 119 120 121 Budgeted Manufacturing Cost per Unit Direct materials cost per unit Direct labor cost per unit Variable MOH cost per unit (given data) Fixed MOH per unit (given data) Cost of manufacturing each unit Reg. 9 Budgeted Manufacturing Cost per Unit Direct materials cost per unit Direct labor cost per unit Variable MOH cost per unit (given data) Fixed MOH per unit (given data) Cost of manufacturing each unit Reg. 10 Wasatch Manufacturing Budgeted Income Statement For the Quarter Ended March 31 Sales Cost of goods sold Gross profit Operating expenses Depreciation expense (given data) Operating income Less: interest expense Less: provision for income tax Net income