Turner Bathworks Sales Budget Assignment

Spreadsheet attached!

In this phase of the budgeting project you will be linking, using Excel formulas, all eight budgets together.

Here are the steps:

  1. Make sure your budgeted numbers for each budget from Part 1 are accurate.
  2. Starting with the sales budget, you will need to enter formulas in the appropriate cells to multiply the sales information by quarter and to add the sales across to get the yearly column and row totals. When you insert the formulas, you should get the same accurate budget numbers from step 1.
  3. Taking the Sales in Units from the sales budget for each quarter, use formulas to transfer that information to the Sales in Unit’s row located in the first row of the production budget. Now, take the appropriate cells within the production budget and, using formulas, get the quarter and yearly totals. You should have formulas to get column and row totals.
  4. Once you have created the Excel formulas for the production budget, then the direct material budget, direct labor budget, manufacturing overhead budget, selling budget, and administrative budgets can be linked using a similar process from steps 2 and 3. Within each budget, you will have formulas to multiple totals and formulas adding information to get overall totals. Each of these budgets should have formulas to calculate column and row totals.
  5. Once you have inserted all the formulas in the first 6 budgets (steps 2, 3, 4), you must insert formulas into the cost of goods manufactured budget and the budgeted income statement. When you complete the linked formulas for the cost of goods manufactured budget and the budgeted income statement, then those budgets should be completed. Your goal is to link all the budgets together so if any number in the sales budget is changed, all seven budgets after the sales budget will also change; all the way to the budgeted income statement.

This is how the budget project will be assessed:

  • To make sure all eight budgets are linked together, the sales forecast and some cost information will be altered. The production budget, direct materials budget, direct labor budget, manufacturing overhead budget, selling and administrative budgets, budgeted cost of goods manufactured, and the budgeted income statement will change automatically using the formulas that you have created. When the sales forecast is changed, all eights budgets should change at the same time.
  • This part of the budget project will be graded on how well all formulas have been linked together.
  • You may can design the formulas however you see fit; just make sure the formulas work.