Budget Lesson in Excel

See attached excel file

----------------------

INSTRUCTIONS: YOU ARE PREPARING THE BUDGET FOR DELUXE LAWNMOWER COMPANY. BELOW IS THE FIRST PART OF THE SALES BUDGET. COMPLETE THIS BUDGET.
SALES BUDGET
SALES PRICE OF THE DELUXE MODEL IS $250 DELUXE LAWNMOWER
USE A FORMULA IN OR FILL IN A NUMBER FOR ALL OF THE CELLS IN YELLOW.
JAN FEB MAR APR MAY JUN JUL AUG SEP
NUMBER 1,000 2,000 3,000 5,000 10,000 20,000 20,000 15,000 5,000
PRICE $250
Total Revenue

BASED ON THE INFORMATION ABOVE, YOU CAN NOW CALCULATE THE PRODUCTION BUDGET FOR DELUXE.
REQ EI THIS MONTH IS 1/2 NEXT MONTH'S SALES IN UNITS PRODUCTION BUDGET
SALES DELUXE LAWNMOWER
USE A FORMULA OF FILL IN A NUMBER FOR ALL OF THE CELLS IN YELLOW
JAN FEB MAR APR MAY JUN JUL AUG SEP
SALES
REQ E.I.
TOTAL REQ
LESS B.I ?
TOTAL PRODUCTION NEEDS

SAME INSTRUCTION AS ABOVE
EACH MOWER REQUIRES 4 BLADES. MATERIALS BUDGET
REQUIRED ENDING INVENTORY IS 1/10 DELUXE LAWNMOWER
OF NEXT MONTH'S PRODUCTION NEEDS
COST PER BLADE IS $10 EACH. JAN FEB MAR APR MAY JUN JUL AUG SEP
PROD NEEDS
NUMBER OF BLADES
TOTAL MATLS REQ
ADD DESIRED EI
TOTAL MATLS REQ
LESS BI
TOTAL PURCHASES

COST PER BLADE
TOTAL COST

SEE BELOW - THE WAGES ARE $10/HOUR. THE AGREEMENT OF THE WORKERS WITH DELUXE IS THAT THEY GET PAID THE GREATER OF THEIR TOTAL EARNINGS OR $20,000 PER MONTH
AVERAGE LABOR COST PER UNIT - $10 LABOR BUDGET
GUARANTEED LABOR IN $ PER MONTH - $20,000 DELUXE LAWNMOWER

JAN FEB MAR APR MAY JUN JUL AUG SEP
PROD NEEDS
LABOR COST PER UNIT
TOTAL LABOR COST
GUARANTEED LABOR $$
LABOR BUDGETED

INDIRECT MATERIALS - $2 PER UNIT
INDIRECT LABOR - .25 * DIRECT LABOR OVERHEAD BUDGET
ALL OTHER COSTS ARE FIXED DELUXE LAWNMOWER
DEPRECIATION IS $7500 JAN FEB MAR APR MAY JUN JUL AUG SEP
INDIRECT MATERIALS
INDIRECT LABOR
TAXES 2000
UTILITIES 1000
DEPRECIATION 7500
TOTALS
LESS DEPRECIATION
TOTAL CASH NEEDS

VARIABLE SELLING COST PER UNIT IS $2
FIXED SELLING AND ADMINISTRATIVE EXPENSE IS $30,000 PER MONTH SALES AND ADMIN BUDGET
DELUXE LAWNMOWER
JAN FEB MAR APR MAY JUN JUL AUG SEP
SALES IN UNITS
VARIABLE SELLING COST PER UNIT
VARIABLE SELLING, TOTAL
OTHER FIXED SELLING,ADMIN
TOTAL SELLING AND ADMIN
ALL OF DELUXE'S SALES ARE ON ACCOUNT. BELOW IS THE COLLECTION PATTERN
COMPANY COLLECTS 50% OF ITS SALES IN THE MONTH
OF THE SALE, 40% IN THE MONTH FOLLOWING AND 5%
IN THE SECOND MONTH FOLLOWING. THE REST IS BAD DEBT CASH RECEIPTS BUDGET
JAN FEB MAR APR MAY JUN JUL AUG SEP
SALES
SEE INFO AT END OF BUDGET (NOV AND DEC) TO DETERMINE COLLECTIONS JAN, FEB
CASH COLLECTIONS SAME MO
CASH COLLECTIONS FOLLOWING
CASH COLLECTIONS 2ND FOLLOW

TOTAL CASH COLLECTIONS
YOU WILL BE GETTING FIGURES FROM OTHER BUDGETS FOR THIS PART.
THE COMPANY BUYS ALL MATERIALS ON ACCOUNT.
PAYS 70% OF AMOUNT OWED MONTH OF SALE, REMAINING THE FOLLOWING MONTH CASH DISBURSEMENTS BUDGET
A/P DEC 31 OF PREVIOUS YEAR IS 20000 JAN FEB MAR APR MAY JUN JUL AUG SEP
FROM MATERIALS BUDGET

PAYMENTS FOR DIRECT MATERIALS
SAME MONTH
MONTH FOLLOWING SALE 20000
TOTAL MATERIALS PAYMENTS
TOTAL LABOR PAYMENTS
TOTAL OVERHEAD PAYMENTS
SELLING AND ADMIN
TOTAL MONTHLY CASH NEEDS

SEE BELOW - CAN YOU FIGURE THIS OUT?
BOND INTEREST PAYMENTS $2,000,000 EVERY JAN 1 AND JULY 1
DIVIDENDS $1,500,000 IN NOVEMBER
PURCHASE EQUIPMENT WORTH $2,500,000 IN AUGUST
BORROW END OF MONTH, PAY BACK INTEREST AT 10% END OF MONTH
MINIMUM REQUIRED CASH, $30,000
CASH BUDGET
JAN FEB MAR APR MAY JUN JUL AUG SEP
BEGINNING CASH 30000
TOTAL CASH COLLECTIONS
TOTAL CASH AVAILABLE

LESS MONTHLY CASH NEEDS
SUBTOTAL
OTHER EXPENDITURES
BOND INTEREST
DIVIDENDS
EQUIPMENT
PAYMENT OF INTEREST
CASH EXCESS OR (SHORTAGE)
BORRROWING NECESSARY
CASH END OF MONTH

DON'T GO ANY FURTHER

DELUXE LAWNMOWERS
INCOME STATEMENT
FOR THE YEAR ENDED DECEMBER 31, 2005

SALES (FROM SALES BUDGET)
LESS COST OF GOODS SOLD:
BEGINNING INVENTORY 6000
ADD COST OF GOODS MANUFACTURED TOTALS FROM MATERIALS,LABOR,OH
COST OF GOODS AVAILABLE
LESS ENDING INVENTORY $6,000
COST OF GOODS SOLD
GROSS PROFIT
LESS OTHER OPERATING EXPENSES FROM SALES AND ADMIN BUDGET
NET INCOME

DELUXE LAWNMOWERS
BALANCE SHEET
31-Dec-05

ASSETS
CASH -FROM CASH BUDGET
ACCTS RECEIVABLE-FR CASH COLL
INVENTORY 6000
EQUIPMENT 61833455
LESS DEPRECIATION 10500000
TOTAL ASSETS

LIABILITIES AND ST EQUITY
ACCOUNTS PAYABLE FROM CASH DIS
BONDS PAYABLE 40000000
INTEREST PAYABLE $2,000,000
TOTAL LIABILITIES

STOCKHOLDERS' EQUITY STATEMENT OF RETAINED EARNINGS
COMMON STOCK 1000000 BEG RE 3000000
RETAINED EARNINGS ADD NET INCOME
TOTAL STOCKHOLDERS' EQUITY SUBTOTAL
TOTAL LIABILITIES AND SE LESS DIVIDENDS
ENDING RE 0