1. Business
2. Finance
3. Working Capital Management
4. Cash Budgeting
5. 331300

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

Hello, I need help filling out a budget for class. I have attached an Excel file with the fields needing to be filled out. Thank you.

See *ATTACHED* file for complete details!

-----------
Spreadsheet Format

Each sheet of your workbook should include your name, course and section number, and the date. You will have two sheets in your spreadsheet. I've named the first sheet Baseline Case. You name the second sheet What-Ifs.

Baseline Case

The Gulf Corporation manufactures and sells two products?Product A and Product B. In November 2010 Gulf's budget department gathered the following data to project sales and budget requirements for 2011.

Sales forecast for 2011:

Product Units Sales Price
Product A 60,000 $70
Product B 40,000 $100

Finished goods inventory:

Product Expected January 1, 2011 Desired December 31, 2011
Product A 20,000 25,000
Product B 8,000 9,000

Direct materials usage for one unit:

Amount Used Per Unit
Direct Materials Unit of Measure Product A Product B

X lb 4 5
Y lb 2 3
Z each 0 1

Direct materials prices and inventory levels:

Direct
Materials Anticipated Purchase Price Expected January 1, 2011 Desired December 31, 2011
X $8 32,000 lb 36,000 lb
Y $5 29,000 lb 32,000 lb
Z $3 6,000 each 7,000 each

Direct labor requirements and rates:

Product Direct labor hours per unit Direct labor rate per hour
Product A 2 $3
Product B 3 $4

Additional information needed for the cash budget:

 Cash balance on January 1, 2011 is estimated to be $750,000.
 Direct materials are paid for in the year they are ordered.
 Total factory overhead (variable and fixed combined) is estimated to be $950,000 and includes $100,000 of depreciation. Factory overhead costs are paid in the year incurred.
 All sales are for cash.
 Marketing and administrative expenses are estimated at 10% of sales for the period and are paid when incurred.
 Income taxes for the year are estimated at $500,000 and will be paid in 2011.

Based on the above projections and budget requirements for 2011 for Product A and Product B, prepare the following budgets for 2011 for the baseline case. Format your budgets similar to those in your textbook starting on page 619.

a. Sales budget (in dollars)
b. Production budget (in units)
c. Direct materials budget (in units and dollars)
d. Direct labor budget (in dollars)
e. Cash budget

What-If Case

After preparing the budgets for the baseline case, prepare the same budgets with the following "what-ifs." Make all the changes at one time:

What if the cost of direct material Z increases to $4 per unit?
The selling price of Product B falls to $90?
Product A requires 3lbs of direct material Y?

The easiest way to do this is to copy the sheet for the baseline case to a new sheet and then replace the original data parameters. To copy a sheet, right click on the sheet name. Select "Move or Copy." Click on the "Create a copy" box. Click OK. Rename your new sheet to "What-If Case" To rename a sheet, right click on the sheet name. Select Rename. Key in the new name.