AF-350 Spreadsheet Assignment

AF-350 MANAGERIAL ACCOUNTING
FINAL COMPUTER PROJECT
PROFESSOR C. DONCHESS

The Rocky Horror Corporation will begin business operations on January 1, 1996. Below is
the anticipated SALES BUDGET (in units):

Jan. __0,000
Feb. __0,000
Mar. __0,000
Apr. __0,000
May __0,000

1) To complete the unit sales forecast, use your student identification number to fill
in the
blanks. If your number contains a "0", use "3" instead.
Example: Student ID number is 146073578. The sales forecast would be:

January 10,000
February 40,000
March 60,000
April 30,000
May 70,000

ADDITIONAL INFORMATION (note: underlined boldface numbers will change in REQUIREMENT 2.)

a. Ending finished goods inventory should be equal to 50% of next month's sales projection.

b. Each unit requires 3 pounds of material at a raw material cost
of $4 per pound.

c: Labor cost is $10 per unit produced.

d. Ending raw material inventory should be equal to 20% of next month's material requirement.

e. Factory overhead is $18,000 per month of which $3,000 is for depreciation.

f. All costs are paid in the month incurred except for purchases which is paid in the following month.

REQUIRED: Using MS-Excel or Lotus 1-2-3

1. * Prepare a PRODUCTION BUDGET for the first quarter (January through March).
* Prepare a Raw Materials PURCH
ASES BUDGET for the first quarter (Jan.through Mar.)
* Prepare a CASH DISBURSEMENTS budget for the first quarter (January through March).

2. Repeat requirement 1 for the following assumptions:

Ending Finished Goods Ending Raw Material Raw Material
Inventory Inventory Cost per Pound
A. 20% 80% $3
B. 40% 60% $5
C. 60% 40% $6
D. 80% 20% $4

< /html>