Explore BrainMass

Master Budget


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.


? 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)" }


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.


(a) Balance Sheet, December 31, 2008

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

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.