Objective: Demonstrate understanding and application of budgeting and spreadsheet programming skills Deliverables: 1. Prepare a master budget for the four quarters ending December 31, 2020. The master...

Objective:
Demonstrate understanding and application of budgeting and spreadsheet programming skills

Deliverables:

1. Prepare a master budget for the four quarters ending December 31, 2020. The master budget includes: Sales Budget, Schedule of Expected Cash Collections, Production Budget, Material Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget, Cost of Goods Manufactured Budget, Selling & Administrative Budget, Cash Budget, Income Statement, and Balance Sheet.

2. You must utilize the template provided with this mini-case assignment.

a. All direct input data
must
be limited to the "Input" tab of the spreadsheet template. Other than on this tab, all other data fields must be entirely formula-driven. Please note that you can and probably should program references to calculations within and between budget sub-schedule tabs, but no direct input data should be used except in the "Input" tab.

b. Do not manipulate the structure of the template without express permission from Dr. Cannon. You may, however, use "open space" on the tabs for any calculations you wish to perform.

c. All template fields should have data for the mini-case to be completed

3. Discuss the following:

a. The Chief Financial Officer (CFO) has asked you to evaluate the budgeting process. Currently, Big Blue Banners uses a
participative budgeting
process, in which line-level managers collect and submit operations-level budget assumptions for senior management approval. Discuss the strengths and weaknesses of utilizing such a process.

b. CFO is also concerned about the potential for inflated budgets due to the existence of budgetary slack. What might motivate line-level managers to add budgetary slack to their budget submissions. Assuming that the CFO is considering the best interest of Big Blue Banner's shareholders, should the CFO be concerned?

Case Data:

Company Background & Input Data

Big Blue Banners Company is a small manufacturing company that produces banners for the best university sports teams in the nation (the teams happen to be all affiliated with a single University). Big Blue's banners are sold to independent distribution centers and retailers. They have seasonal sales patterns based on demand for each type of sport. As of December 31, 2019, Big Blue Banners has the following balance sheet:

Big Blue Banners is in the process of preparing a master budget for the year 2020, and has created the following assumptions for the upcoming year:

• Budgeted Banner Sales (in units) are as follows:

• Each Blue banner sells for $15, each Multi-color banner sells for $7, and each White banner sells for $10, respectively.


• All sales are on credit (the customer purchases a banner and then pays at a later time... "I will gladly pay you Tuesday for a hamburger today", Wimpy from the show, Popeye). The company's expected cash collection (from customers) pattern is: 75% of all sales are collected during the quarter in which the sale takes place, 25% of all sales are collected during the quarter following the quarter in which the sales take place.


• The company desires to have finished goods inventory on hand at the end of each quarter equal to 15% of the following quarters' budgeted
  sales
  in units. This requirement is expected to be met on December 31, 2019.


• One yard of material costing $4.50 is required per banner produced. The company desires to have sufficient material on hand at the end of each quarter equal to 12% of the following quarter's budgeted
  production
  needs. This requirement is expected to be met on December 31, 2019.


• Materials are purchased on account with the suppliers. That is, the Big Blue Banners purchases the material with an understanding that payment will be made some time in the future. Big Blue expects the following materials purchases cash disbursement pattern: 85% of each quarter's material purchases are paid for in the same quarter in which the purchase takes place, 15% of each quarter's material purchases are paid for in the quarter following the quarter in which the purchase takes place. No discount terms are available.


• Each banner requires 0.25 of an hour of direct labor (15 minutes) to manufacture. The forecasted hourly rate for direct labor is $10.


• Variable manufacturing overhead is forecasted to be $1.50 per direct labor hour.


• Fixed manufacturing overhead is forecasted to be $1,000 per quarter. $700 of manufacturing depreciation is included in the $1,000 budget. Note: Depreciation cost is not a cash outflow.


• All cash disbursements for manufacturing overhead are expected to be paid in the quarter the overhead cost is incurred.


• Variable selling and administrative costs are expected to be $2.25 per banner sold.


• Fixed selling and administrative costs are expected to be $1,500 per quarter. $500 of administrative depreciation is included in the $1,500 budget. Note: Depreciation cost is not a cash outflow.


• All cash disbursements for selling and administrative costs are expected to be paid in the quarter the cost is incurred.


• Equipment purchases and payments of $2,000 are scheduled for Q2, 2020. Equipment purchases and payments of $1,200 are scheduled for Q3, 2020. Assume that no incremental depreciation cost is recorded in 2020 for these equipment purchases.


• Big Blue Banner management wishes to maintain a minimum cash balance of $1,000 at the end of each quarter. An open line of credit is available, which allows Big Blue Banners to borrow up to $30,000 per quarter. The 14%
  annual
  interest rate is simple (equals 3.5% per quarter).


• To be conservative, assume that all borrowing takes place on the first day of each quarter, and all repayments are made on the last day of each quarter. Consequently, assume that interest expense is accrued for
  an entire quarter
  on any borrowing that is carried forward from previous quarters and on any additional borrowing that is expected to take place within a quarter. If there is expected to be excess cash at the end of any quarter (above the minimum cash balance), that cash should be used to repay any outstanding loan balance.


• During the 4th quarter, 2020, Big Blue Banners plans to declare a $1,000 dividend. Big Blue plans to pay cash for that dividend in Q1, 2021. Note: Big Blue also declared a dividend in 2019, which must be paid for (in cash) during Q1, 2020.


• Big Blue Banners uses the
  variable costing method
  of accounting (
  all fixed costs are expensed
  during the period in which they occur).