Master Budgeting Project

This is a group project. The rational for group work is team learning. When we work with other team members, everybody learns from every other member, and we will see synergy effects in learning.

This project provides you with an opportunity to construct a master budget. Making budgets is one of the most important functions of managers. In previous semesters, the majority of the students appreciated the value of the project. They said the project was helpful in performing their jobs or would help them in their career.

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, lets 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,

14. 2 lbs of direct materials are needed (1 lbs of the material costs $1.50).
15. 0.5 hour of direct labor is needed (1 hour of labor costs $6).
16. 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)
17. 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 (7) 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 181, 193, and 212. 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) }

Grading Policy:

Correctness: 80%

Formatting/Appearance: 20%