Explore BrainMass

Explore BrainMass

    Master Budget

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

    Budgeting

    Your task is to make a master budgeting template in a spreadsheet. Obtain the pre-formatted spreadsheet (master.xls). The information necessary for making the master budget is given in Part I of the spreadsheet. You need to enter formulas in Part II.

    Once you enter all required formulas, you can use this master budget template for any budgeting situation.

    Instructions

    ? Enter participants' names and team number in designated cells at the beginning of the spreadsheet.
    ? In the master budget spreadsheet, you need to complete Part II by entering formulas in relevant colored cells - in all yellow cells like the following

    ? Make sure you enter right formulas, not numbers. After completion, change some numbers in Part I, and check whether your budgets are changed accordingly.

    Do not enter any numbers. Formulas should not contain any numbers. For example, let's assume the following. This is a hypothetical example.

    Cell Number Description
    A10 $100,000 Amount of Borrowing
    B30 0.05 Interest Rate
    K1 You need to determine! Amount of Annual Interest Expense

    In cell K1, enter the formula only, "+A1*B30" or "=A1*B30").
    Do not enter "10000*0.3", "+A1*0.3", or "100000*B30".

    ? If you just need to copy a number from a particular cell, put the formula "+cell number" or "=cell number".. For example, assume that in cell A50, you have to copy the number in cell B26. Then you have to enter "+B26" or "=B26" in cell A50.

    ? In Part I, (c), the interpretation is as follows:
    To make 1 unit of the product,
     2 lbs of direct materials are needed (1 lbs of the material costs $1.50).
     0.5 hour of direct labor is needed (1 hour of labor costs $6).
     Variable overhead would be applied at $1.40 per direct labor hour. (So, 1 unit of the product includes $1.40* 0.5 = $0.70 for variable overhead)
     Including fixed overhead, unit manufacturing cost is $7.00 (To manufacture 1 unit of the product, it costs $7.00).

    ? Formulas have to be entered for each month (Jan., Feb., and Mar.) and for the quarter.
    -In Sections (3) and (4) of the spreadsheet file, you need to put formulas for April and May too, for some items (Blue areas).
    -In Sections (10) and (11) of the spreadsheet file, the formulas for the whole quarter are needed (not for individual months).

    ? In Section (4) of the spreadsheet, Budgeted variable overhead is:
    Budgeted direct labor hours multiplied by Variable overhead rate (POR).

    ? You need to use some complex logical functions (e.g., If, Greater than, etc. and their combinations) in somewhere in Section (9) of the spreadsheet.

    ? All numbers are clean (no decimal point) except for Lines 177, 188, and 207. The Cash Budget part of the spreadsheet is formatted to round numbers to the nearest dollar. Off-balance in the budgeted balance sheet implies that something must be wrong.

    ? For all negative numbers, format them in such a way that the numbers are in parentheses {e.g., instead of "-360" it should be "(360)" }

    © BrainMass Inc. brainmass.com June 3, 2020, 9:56 pm ad1c9bdddf
    https://brainmass.com/business/cash-budgeting/206810

    Attachments

    Solution Preview

    Please see the attached file

    They have completed the January part. Please look at the cell formula to understand how the figures have been calculated. I have also put some comments to explain more.

    For the quarterly income statement and balance sheet also I have put some explanations of how to calculate the figures.

    PART I. COMPANY INFORMATION

    (a) Balance Sheet, December 31, 2008

    ASSETS
    Current Assets
    Cash $7,000
    Accounts Receivable $10,000
    Raw Materials Inventory $10,350 for 6,900 lbs
    Finished Goods Inventory $10,500 for 1,500 units
    Prepaid Insurance $2,400

    Property, Plant and Equipment
    Plant and Equipment $70,000
    Less: Accumulated Depreciation $5,000 $65,000

    Total Assets $105,250

    LIABILITIES & STOCKHOLDERS' EQUITY
    Current Liability
    Accounts Payable $8,000

    Stockholders' Equity
    Common Stock $40,000
    Retained Earnings $57,250

    Total Liabilities and Equity $105,250

    (b) Sales Forecast (units)

    January, 09 4,500
    February, 09 5,500
    March, 09 7,000
    April, 09 7,600
    May, 09 9,000

    Selling Price per Unit $10

    (c) Production Requirements and Manufacturing Cost Information

    Unit manufacturing cost of product $7.00 per unit of product

    Direct material requirement 2 lbs per unit of product cost per lbs $1.50
    Direct labor requirement 0.5 hrs per unit of product cost per DLH $6.00
    Variable Overhead's POR $1.40 per direct labor hour

    Fixed Overhead per Month
    Salaries $680
    Utilities $350
    Insurance $200
    Depreciation $700
    Total $1,930

    (d) Desired Ending Inventories
    Finished goods 50% of Unit sales in next month
    Raw material 60% of Quantity (pounds) to be used in production next month

    (e) Estimated S & A (selling and administrative) Expenses

    Sales Commissions (Variable) $0.80 per Unit sold
    Salaries (Fixed) $2,000 per Month
    Advertising (Fixed) $600 per ...

    Solution Summary

    The solution explains how to prepare the various components of the master budget.

    $2.19

    ADVERTISEMENT