Accounting Master Budget Project

Master Budget Project


Congratulations! You have just been hired as controller for a growing company that manufactures green products. It is October 1 and time
for creation of the budget for the next year. The budget will be created in quarters using each month as a period of sales/production.

Your first task is to create the budget for the first quarter of 2016. This involves completing budgets for January, February, March and the
first quarter in total for 2016. You seek out last year’s data, recent costing information and follow up on important assumptions.

The CFO wants to see the first quarter budget before you complete the rest of the year, as adjustments may be needed. You will need to
create the following sub-budgets as part of the overall master budget:
st
Sales Budget for the 1st quarter
st
Production Budget for the 1st quarter
st
Materials Purchases Budget for the 1st quarter
st
Direct Labor Budget for the 1st quarter
st
Overhead Budget for the 1st quarter
st
Cash Receipts of Sales Revenue for the 1st quarter
st
Cash Payments for Material Purchases for the 1st quarter
st
Overall Cash Budget for the 1st quarter
Income Statement for the quarter ended March 31, 2016
Statement of Cash Flows for the quarter ended March 31, 2016

Several assumptions are needed to work into your models for computation.
They can be found on the Assumptions tab.

Unit Sales
Sales Price
Desired Ending Inventory for Finished Goods
Desired Ending Inventory for Material Units
Cost of one lb.of materials (1 lb =1 material unit)
Units of Direct Materials to make one unit
Minutes of Direct Labor to make one unit
Direct Labor Costs per Hour
Overhead Costs percentage of Direct Labor
Minimum cash balance (Assume you start with this)
Cash collections in month of sale
Cash collections in first month after sale
Cash collections in second month after sale
Cash Payments in month of purchase
Cash Payments in the first month after purch
Selling Costs per month (for cash budget)
Administrative Costs per month (for cash budget)
Annual Interest Rate for Letter of Credit
Annual Interest Rate Earned on Excess Cash
Sales Revenue for November
Sales Revenue for December
Material Purchases for November
Material Purchases for December
Depreciation Expense for the quarter (Operating Exp)
Income Tax Rate

January
February
March
April
May
30,000
28,000
27,000
28,500
29,000
$10
20% of next month's sales in units
10% of next month's materials needed for production
$0.25
4
15
$16
150%
$10,000
70%
25%
5%
70%
30%
$12,000
$14,000
12%
3%
$300,000
$300,000
$24,000
$24,000
$5,000
20%