NCSU Accounting Production Ability and Marketing Strategies Questions

Operating Budget
Enter your name and student ID at
the top of the spreadsheet.
Sky High Parachute Company manufactures and sells parachutes to
adventure companies. The company’s sales forecast for the coming year
follows:
Quarter 1 Quarter 2 Quarter 3 Quarter 4
Budgeted sales (units) XX,000 YY,000 ZZ,000 AA,000
Where XX = the last two digits of your student ID, YY = digits 6 & 7of
Where:
your student ID, ZZ = digits 4 & 5 and AA = digits 2 & 3. (Note: if XX,
• YY,
XXZZ
is 34
81
ZZ
is 40
AA is 50
or AA isYY
00,issubstitute
99).
Other budgeted information follows:
• The budgeted sales price for each parachute is $1,200
• The company budgets production so that ending finished goods
inventory equals 12% of the next quarter’s budgeted sales
• Each parachute requires 22 square yards of a specialty material that
costs $15 per square yard
• The company purchases raw materials so that 15% of each quarter’s
production needs are left over at the end of the quarter to be used as
beginning inventory in the next quarter. At the beginning of the first
quarter, 70,000 square yards of material were on hand.
• Each parachute requires 14 hours of direct labor at a rate of $16 per
hour.
• Manufacturing overhead costs are budgeted at $1 million per quarter
plus $35 per unit.
• Selling and administrative expenses are budgeted at $400,000 per
quarter plus 12% of total sales revenue.
Required:
Prepare an Excel spreadsheet using formulas to prepare the following
budgets. Entering numbers without formulas will result in the loss of
points in each cell that is affected. Tie each budget to the previous budget through the use
of formulas. If one cell changes, other cells should also change. Submit the spreadsheet on Blackboard. Do
NOT use separate tabs or separate worksheets (-10 points). Show all your work on ONE page of the
spreadsheet. Use the examples in your book or the handouts in class to set up
the spreadsheet.
Prepare the following operating budgets for quarters 1-3. (You do not
have enough information to prepare all of the budgets for quarter 4, but
you must use all the information you have to start it.)
1. Sales budget
2. Production budget
3. Raw materials purchases budget
4. Direct labor budget
5. Manufacturing overhead budget
6. Selling and administrative expense budget
You will be graded on: formulas, correct answers, format
Sales budget: 15 points
Production budget: 20 points
Raw materials purchases: 20 points
Labor budget: 15 points
Manufacturing overhead budget: 15 points
Selling and administrative expense budget: 15 points