Explore BrainMass

Explore BrainMass

    Budget Lesson in Excel

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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

    © BrainMass Inc. brainmass.com June 4, 2020, 1:16 am ad1c9bdddf
    https://brainmass.com/business/cash-budgeting/budget-lesson-excel-389634

    Attachments

    Solution Summary

    Budget lessons in Excel are examined.

    $2.19

    ADVERTISEMENT