Question

LBL Corporation is preparing its master budget for the first quarter of the upcoming year. The following contains detail on LBL’s operations necessary for their master budget:

•Sales Information:

oActual / Projected Sales are as follows:

♣December (Prior Year; Actual): $75,000

♣January (Estimated): $85,000

♣February (Estimated): $91,000

♣March (Estimated): $96,000

♣April (Estimated): $112,000

♣May (Estimated): $120,000

oUnits are sold at $11 each

oSales in a month are paid in cash for 40% and on credit for the remainder with credit collection occurring in the month following the sale.

HINT: To help you with several budgets, you will need sales in UNITS (not total dollars as listed above). You can calculate the number of sales units by taking the total sales / sales price per unit.

•Partial balance sheet as of 12/31 of the prior year:

oCash$5,050

oA/R, net$112,000

oInventory$37,600

oPP&E$145,000

oA/P$35,700

oCapital Stock$140,900

oRetained Earnings$29,000

HINT: You may not need all this information.

•LBL plans to produce enough units for sales expected in the period as well as have a cushion in ending finished goods inventory of 20% of the following month’s sales units expected.

•Direct Materials:

oThree pounds of materials are needed to create each unit.

oWhen LBL purchases raw materials, they pay 30% in the month of purchase and the rest the following month. The cost is $1.50 per pound of material.

oManagement wants ending inventory to be equal to 20% of next month’s production needs.

HINT: Direct materials are the only thing that LBL pays for, in part, in the following month. All other expenses are paid for in the month they occur. Therefore, the balance for A/P is for raw materials.

•Direct Labor:

oThere is little DL necessary at LBL with only 0.05 direct labor hours needed per unit.

oLabor costs are paid in the month incurred at a rate of $9 per hour.

•Other Manufacturing Costs:

oVariable overhead costs $1.10 per unit.

oLBL pays plant rent at a steady rate of $6,000 per month and $4,000 per month for all other fixed manufacturing costs. For the units expected for the year, fixed overhead costs are $0.80 per unit.

oAll expenses are paid in the period incurred, and the above costs do not include depreciation.

•Capital Expenditures: New computer equipment will be phased into LBL’s admin offices over the next year with first quarter purchases as follows: January: $10,000, February: $13,000, and March: $15,000.

•Operating/SG&A Expenses:

oBudgeted costs are $1.25 per unit.

oDepreciation for the admin office’s buildings/equipment is estimated at $5,000 for the quarter.

oAdditionally, they pay $2,000 for fixed operating expenses per month

•Financing:

oLBL wants cash to be at a minimum balance of $5,000 each month.

oIn case of a cash shortage, they have a line of credit with a bank for up to $75,000, which is borrowed and repaid in $1,000 increments. Simple interest applies to borrowed amounts at 1% per month outstanding with accumulated interest paid at the end of each quarter for any borrowed amounts throughout the period. If the company has surplus cash beyond the minimum balance required in a month, it would repay as much of any outstanding loans as possible without violating its minimum balance policy.

•Taxes:

oThe current applicable tax rate is 35%.

oWhile taxes are incurred each month of operations, it is paid quarterly with a $12,000 payment expected in February only.

Homework Required: Using the information above, prepare the following budgets for the first quarter (January, February, March AND a Quarter total, where applicable) in Excel:

1.Sales budget

2.Production budget

3.Direct material budget

4.Schedule of cash collections

5.Cash payments for:

a.Direct material purchases (based off of purchases found in requirement 3. above)

b.Direct labor

c.Manufacturing overhead

d.Operating expenses

(Create a separate budget for each cash payments budget listed above)

6.Combined cash budget

HINT: Many of the items on here will come from previous budgets you created in 1-5 above.

7.Budgeted manufacturing overhead per unit (HINT: There is no time period/monthly budget needed for this)

HINT: The Fixed MOH per unit was given to you.

8.Budgeted income statement for the quarter ending March 31 (Hint 1: This is a quarterly statement, so you do not need to do each month but rather a combined statement for January 1 through March 31; Hint 2: The COGS amount is found, in part, by using the COGS per unit you find in budget 7. above).

Excel Directions and Information:

•Create professional-looking budgets, formatting as you see fit to look professional and appropriate. Be sure to use a title for each budget (you can use multiple worksheets or one worksheet; just be sure to label each clearly).

•For budgets 1. through 6. above, you must use must use five columns: *Explanatory labels for your rows, January, February, March, Quarter Total. Budgets 7. and 8. will only require two columns.

*The first column should be dedicated to adequately explaining each row of data in your budget; See column in yellow as an example:

•Use must use formulas for calculations (do not do the calculations on a calculator and then simply type the numbers into Excel). For example, in the Sales Budget above, the “Total Sales Revenue” for each month would require a formula to calculate the total (=Unit Sales*Unit Selling Price).

•Use the SUM (or similar) function for subtotals/totals where appropriate (for instance, this would be used frequently for the Quarter column).

•If data/numbers come from a previous budget, you must reference the cell from a prior budget as opposed to simply re-typing in the numbers. For example, the combined cash budget would reference many items already computed in a prior budget; reference the cell from the prior budget with the data/information as opposed to typing in the number.

Answer 1

1.Sales Budget	Dec.(Act.)	Jan .	Feb.	Mar.	Qtr. Total	Apr.	May
Budgeted total sales $	75000	85000	91000	96000	272000	112000	120000
S.P./unit	11	11	11	11	11	11	11
Sales units expected	6818	7727	8273	8727	24727	10182	10909
Cash sales(40%*sales $)		34000	36400	38400	108800
Credit Sales(60%)		51000	54600	57600	163200

2.Production Budget
Sales units expected	6818	7727	8273	8727	24727	10182
Ending Fin.gds.(20%*next mth sales units)	1545	1655	1745	2036	2036	2182
Total units needed	8364	9382	10018	10764	26764	12364
Less:Beg. Fin gds	1364	1545	1655	1745	1545	2036
Production units needed	7000	7836	8364	9018	25218	10327

3. Direct material budget
Production units needed	7000	7836	8364	9018	25218	10327
D/M pds. reqd./unit	3	3	3	3	3	3
Total pds. Reqd. for the mth's prodn.	21000	23509	25091	27055	75655	30982
Ending Inv. Reqd.(20%*next mth. req.)	4702	5018	5411	6196	6196
Total pds. needed	25702	28527	30502	33251	81851	30982
Less:Beg.D/M pds.	4200	4702	5018	5411	4702	6196
Purchases of D/M (pds) needed	21502	23825	25484	27840	77149	24785
Cost/pd.	1.5	1.5	1.5	1.5	1.5	1.5
Total costfor D/M	32253	35738	38225	41760	115724	37178
Cash payment for D/M	9676	10721	11468	12528	34717	11153
Credit pmt. For D/M(70%)		25017	26758	29232	81007	26025

4.Schedule of Cash Collections
Cash sales(40%*sales $)		34000	36400	38400	108800
From Credit sales		112000	51000	54600	217600
Total sales collections		146000	87400	93000	326400

5.a. Cash pmt. For D/M purchases
Cash payment for D/M		10721	11468	12528	34717
Credit pmt.(Foll.mth.)		35700	25017	26758	87475
Total pmt. For D/M purchases		46421	36484	39286	122192
b. Direct labor
Production units needed		7836	8364	9018	25218
D/L hrs.reqd. at 0.05 hrs./unit		392	418	451	1261
Total D/L costs at $ 9/hr.		3526	3764	4058	11348
c.Manufacturing OH
Variable OH costs at 1.10/unit		8620	9200	9920	27740
Fixed Ohs(at 0.80/unit)		6269	6691	7215	20175
total cash pmt. For mfg, OHS		14889	15891	17135	47915
Operating/S,G&A budget
Total Budgeted cost(1.25*Sales units)		9659	10341	10909	30909
Out of which:
Fixed opg. Exp.		2000	2000	2000	6000
ie. Cash S,G&A   &
Depn.build./eqpt.		5000	5000	5000	15000

6.Combined Cash budget
Beg. Bal.		5050	74213	78474	5050
Total sales collections		146000	87400	93000	326400
Total cash available		151050	161613.1	171474.2	331450
Disbursements:
Pmt. For D/M purchases		46421	36484	39286	122192
Pmt. D/L costs		3526	3764	4058	11348
Pmt. For mfg, OHS		14889	15891	17135	47915
Pmt. For S,G&A exp.		2000	2000	2000	6000
CAPEX for comp.eqpt.		10000	13000	15000	38000
Income tax			12000		12000
Total Disbursements		76837	83139	77479	237454
Surplus/Deficit		74213	78474	93996	93996
Add: Borrowings
Less: Repayments
Less: Int. on borrowings
Ending Balance		74213	78474	93996	93996

7. Budgeted Mfg. OH per unit
Variable MOH/unit	1.1
Fixed Mfg. OH/unit	0.8
Total mfg. OH/unit	1.9
8. Budgeted Income Statement for the Quarter
Sales revenue		272000
Less: COGS
D/M(24727*3*1.5)	111272
D/L(24727*0.5*9)	111272
Mfg. OH(24727*1.9)	46981	269524
Gross profit		2476
S,G & A exp.(24727*1.25)		30909
Net Loss		-28433