Sales projections are as follows: 4th 1st 25,000 24,000 V Unit Sales Cost of materials Labor mix hourly cost 23 $.14 $18 23 $.15 $18 2nd 30,000 24 $.15 1 $18.50 3rd 34,000 24 7 1 $18.50 $.16 Variable $3.90 $4.00 $4.00 $4.05 Selling Expense 4th 45,000 25 $.17 $19 1st 27,000 25 $.17 $19 7 Percent of cash sales collected in the period 40% Percent of credit sales collected in period of sale 40% Percent of credit sales collected after quarter of sale 20% 2nd 29,000 25 Percentage of purchases paid for in the period of purchase 65% Percentage of purchase paid for in the subsequent quarter 35% Direct labor hours required to produce one unit .25 hours Variable Manufacturing OH rate per direct labor hour is $4.25 Minimum required cash balance $25.000 $.17 $4.10 $4.10 $4.10 $19.5 Desired ending inventory percentage of next quarters production needs 10% Amount of materials needed for one unit to be produced is 8 pounds Desired ending inventory percentage of next quarters materials needs 15% Quarterly interest rate on loans outstanding charged from the beginning of the quarter 3% V Tax rate 30% Fixed Factory overhead quarterly costs is made up of $14,000 Depreciation expense, $19,000 security expense, $61,000 indirect labor expense. Fixed SG&A quarterly expense is made up of $55,000 advertising expense, $95,000 executive salaries, $10,000 insurance expense, $14,000 Property tax expense, $10,000 depreciation expense. Beginning balances on the balance sheet are as follows: o Cash 50,000, Accounts Receivable (you figure out), Raw materials (you figure out), finished goods (you figure out), Land $80,000, Buildings and Equipment $700,000, Accumulated depreciation $292,000, Accounts payable (you figure out), Common Stock $200,000, no loans outstanding, Retained earnings (you figure out....hint, back into this) Additional budget information: Capital Cash 1st $80,000 $10,000 2nd $100,000 $10,000, 3rd $60,000 $10,000 4th $20,000 $10,000 V Dividends paid out Using excel, Prepare all budgets necessary in order to prepare proforma financials.