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.

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.

a)      Prepare a revenue budget

b)     Prepare a production budget in units

c)      Prepare the direct materials usage budget and a direct materials purchases budget

d)     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 produced.

 

Estimates variable manufacturing overhead costs:           Estimated fixed manufacturing overhead costs:

            Supplies                                   $160,250                      Depreciation                 $211,728

            Indirect Labor                             200,650                      Property Taxes                  28,872

            Maintenance                                 80,200                      Insurance                         67,368

            Miscellaneous                               40,100                      Plant management          240,600

                 Total Variable OH                $481,200                      Benefits                          336,840

                                                                                                Miscellaneous                   76,992

                                                                                                     Total Fixed OH            $962,400

 

PART 2:

a)      Prepare a manufacturing overhead budget and determine variable and fixed overhead allocation rates using labor hours for fixed and units for variable.

b)     Prepare a schedule that calculates the unit costs of ending inventory in finished goods and then prepare the ending inventory budget.

c)      Prepare a cost of goods sold budget.

 

Adding to the information above, the following data includes costs collected in regards to support departments:

                              Support Department:                       Fixed Cost                   

                        Administration                         $1,034,580

                        Marketing                                      620,748

                        Distribution                                   310,374

                        Customer Service                           103,458

                             Total SD Cost                      $2,069,160

PART 3:

a)      Prepare the support department costs budget

b)     Prepare a budgeted income statement (assume tax rate of 25%)

c)      Calculate break even in revenue and units

 

Lastly, the Company’s managers budget cash flows on a quarterly basis so they can plan short-term investments & borrowings. Kite sales are highest during the spring and summer. Sales are fairly even within each quarter, but vary across quarters as follows:

 

                        January – March           10%                 April – June                  50%

                        July – September          30%                 October – December      10%

 

Accounts receivable at the end of the prior year, consisting of sales made during December, totaled $90,000. Payments from customers are usually received as follows:

 

Pay during the month goods are received            50%

Pay the next month                                           47%

Bad Debt                                                            3%

 

The managers plan to maintain beginning inventory quantities during January and February, but plan to increase inventories to targeted levels by the end of March and maintain those levels throughout the rest of the year. The company pays its vendors 10 days after raw materials are received, therefore approximately two-thirds of all purchases are paid in the month of production and one-third paid the following month. Accounts Payable at the end of the prior year totaled $13,000. Employee wages and other production costs are paid during the month incurred. Property taxes are paid in two equal installments on March 31 and September 30, and insurance is paid annually on June 30. Support costs are paid evenly throughout the year. Estimated income tax payments are made at the end of each quarter based on 25% of total estimated taxes for the year.

 

In addition to customer receipts, the company expects to receive $10,000 in proceeds from the sale of equipment in January. The company also plans to purchase and pay for new equipment costing $50,000 during January.

 

The Company finances its short-term operations with a line of credit from the bank, which had a balance of $150,000 at the end of the prior year. The line of credit agreement requires the company maintain a minimum cash balance of $100,000. The company’s line of credit requires quarterly interest payments at an annual rate of 5.5% and is paid on the last day of the quarter.

 

PART 4:

a)      Prepare quarterly budgets for cash receipts, cash disbursements and short term financing.

b)     Was the company able to increase its cash reserves?

c)      What is the best use of cash for this firm?