CANYON KITE MANUFACTURING CO.
The accountant at Canyon Kites has always prepared a budget that is calculated using only one estimated volume of sales. He has asked you to help him set up a spreadsheet that can be used for sensitivity analysis in the budgeting process. This year it appears that the company may not meet expectations, which could result in a loss. He is concerned that the company will incur a loss again next year and wants to develop a budget that will easily reflect changes in the assumptions. After gathering information about next year’s operations, he will provide information using a what-if sensitivity analysis. The following assumptions will be used to begin your analysis:
Direct materials per kite: Direct Labor: Hours Hrly Rate Cost/kite
Nylon $10 Assembly 0.5 $30 $15.00
Ribs $ 5 Packing 0.1 $15 $ 1.50
String $ 2

Inventory information: Beginning Target Ending
Direct Mat: Nylon $5,000 $7,000
Ribs 3,000 3,200
String 1,000 1,200
Finished Goods (units) 2,000 Kites 2,200 Kites
Finished Goods (cost) $97,850
Revenue assumptions: Selling Price Volume
$75.00 80,000
PART 1:
Create a spreadsheet with a data input box at the top that includes all relevant data. (Put a border around this data to separate). Set up each schedule with cell references to information in the data input box. Any changes made to information in this box should reflect through all of the schedules you create. As you proceed, more information will be given that you will need to add to the data input box, so leave space to add additional data.
Prepare a revenue budget
Prepare a production budget in units
Prepare the direct materials usage budget and a direct materials purchases budget
Prepare a direct labor budget (in hrs and cost)
In addition to the information given above, the following are estimated manufacturing overhead costs. Both fixed and variable overhead will be allocated based on the number of kites…