production budget, purchases budget and cash flow budget

Requirement 1:

Using Excel, develop the production budget, purchases budget and cash flow budget, roughly following the pdf formats provided.  You may change the format or add spaces for reference cells to make the spreadsheets more versatile/useful.

For requirement 1, use the sales projections provided in the Exhibit 1 of the case.  Use a cost of $8 per square yard for aluminum.  Maintain a minimum cash balance of $100,000 at the end of each month.  Assume a zero interest loan for now, and that the firm can borrow as much as needed.

Hints for building your excel sheet:  Construct your spreadsheet using reference cells so that you can easily modify it later if there are changes in sales projections, materials costs, and other factors that may change in the future (see requirement 2).  Use the “if” statement function to calculate the required borrowing and repayment each month.

Requirement 2:

Using the Excel spreadsheet template you developed in requirement 1 above, compute the budget using the more conservative projected sales and materials costs below:

Sales:

Nov                    1,439

Dec 2,                  131

Jan 2,                  200

Feb 3,                  600

Mar                     4,500

Apr                     3,700

May                    2,000

June                   1,000

Aluminum cost per square yard:

Nov, Dec, Jan, Feb = $8

March, April, May, June = $12

Obviously, aluminum materials volumes (and costs) per month will vary with production.

You can assume that all other costs are the same per month as in requirement 1 because the case doesn’t provide enough information for you to determine the flexible budget amount.  (We can discuss this limitation in class but you don’t have to address it in your solution.)  Assume the collection percentages also are the same as in requirement 1.

Requirement 3:

Write a 2-page memo explaining your findings in spreadsheet 1, spreadsheet 2.  Also briefly discuss other issues that you see in reading the case (personnel issues, competitive issues, reasonableness of sales projections).

Upload your spreadsheets and memo to the Laulima Assignments tab.

The post production budget, purchases budget and cash flow budget appeared first on Scholar Writers.