Dallas Company is preparing a master budget for the 1^st quarter of 2016. Given the following information, you are to prepare in Excel using formulas the following: (A) Production Budget, (B) Direct Materials Budget, and (C) Cash Payments for Direct Materials Purchases Budget for the 1^st quarter of 2016. For the budgets, you need to make them adaptable so that if I change the sales budget and/or materials cost per unit, the budgets will automatically update for these changes.

Facts:

Projected

Sales: Jan 1,100 units at $10 per unit

Feb 1,150 units at $10 per unit

March 1,200 units at $10 per unit

April 1,150 units at $10 per unit

May 1,100 units at $10 per unit

Finished Goods Inventory:

Policy requires ending inventory to be 10% of next months projected sales. Beginning Inventory as of Q1 was 100 units.

Direct Materials Inventory:

Each unit produced requires 3 pounds of Direct materials. Direct Materials costs $3 per pound. Policy requires ending Direct materials to be 30% of next months production needs. They currently have 1,000 pounds of Direct materials on hand as of the Beginning of Q1.

Materials Purchases:

All Direct Materials all purchased on account, with terms of n/15. Because inventory is purchased evenly throughout the month, of the purchases in a month are paid in the month of purchase and the other are paid in the following month. There are currently $600 owed as of the beginning of the Q1 (all of that will be paid in Jan.).

Worksheet Hints:

Hint

You will want to use reference cells when you are constructing your budgets. If you use reference cells for the first month, then you can copy and paste for the remaining months. There are relative and absolute reference cells that need to be used. Here is a link for a web description of these types http://web.pdx.edu/~stipakb/CellRefs.htm

Grading (out of 10): To receive a 7 or better, you need to have all 3 budgets constructed to represent the policies outlined and have the correct answers for the budgeted information. To receive an 8, you have to also use relative reference cells in your budget (these would best be utilized when you copy and paste the different months in the different budgets). To receive a 10, you need to use both relative and absolute reference cells. Also, when I physically change the sales budget (or materials cost) in your worksheet, the other budgets should change (appropriately) for the different level of sales. Absolute reference cells will allow you to do that (put your sales budget first (and also the materials cost), and then you can absolute reference the sales budget in the production budget and the materials cost in the direct materials purchases budget).

So start with sales budget at the top, and also list the materials cost at the top. Remember, I want to be able to change these numbers and have the impact be reflected in the rest of the budgetary numbers. So the production budget needs to be linked to the sales budget, the DM purchases needs to be linked to the production budget, and the cash payments budget needs to be linked to the DM purchases budget.

Sales

Then Production

Then DM Purchases

Then Cash Payments