The accountant at Fighting Kites has always prepared a budget that is calculated using only one estimated

Question:

The accountant at Fighting 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. The accountant 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.

PART 1: SPREADSHEET WITH INPUT BOX, REVENUE AND PRODUCTION BUDGETS

Following are the assumptions regarding revenues, direct materials and labour costs, and inventory levels:

The accountant at Fighting Kites has always prepared a budget

REQUIRED
A. Create a spreadsheet with a data input box at the top. Into this box put all the relevant assumption data. This box should be formatted with a border to separate the input data from the cell-referenced data. Set up each schedule with cell references to information in the data input box. Any changes made to information in this box should be reflected through all the schedules that you set up. As you proceed through Parts 2 and 3 of this problem, more information will be given that needs to be located in the assumptions box, such as next year's estimated variable and fixed manufacturing overhead, as well as support department costs. You will need to leave space in the data input box for this information or add more rows as you develop the spreadsheet.
B. Prepare a revenue budget.
C. Prepare a production budget in units.
D. Prepare the direct materials usage budget and a direct materials purchases budget.
E. Prepare a direct labour budget (in hours and cost).
PART 2: OVERHEAD, ENDING INVENTORY, AND COST OF GOODS SOLD BUDGETS
Refer to the information for Part 1. Following are estimated manufacturing overhead costs. Both fixed and variable overhead will be allocated based on the number of kites produced:
Estimated variable manufacturing overhead costs:
Supplies .....................................................$160,250
Indirect labour ..............................................200,650
Maintenance ..................................................80,200
Miscellaneous ................................................40,100
Total variable overhead costs .........................$481,200
Estimated fixed manufacturing overhead costs:
Amortization ..............................................$211,728
Property taxes ................................................28,872
Insurance .....................................................67,368
Plant management .........................................240,600
Fringe benefits .............................................336,840
Miscellaneous ...............................................76,992
Total fixed overhead costs ...........................$962,400
REQUIRED
F. Prepare a manufacturing overhead budget and determine variable and fixed overhead allocation rates by dividing the budgeted overhead by budgeted labour hours for the fixed overhead, and by units for the variable overhead.
G. Prepare a schedule that calculates the unit costs of ENDING INVENTORY in finished goods, and then prepare the ending inventories budget.
H. Prepare a cost of goods sold budget.
PART 3: BUDGETED INCOME STATEMENT
Refer to the information for Parts 1 and 2. Following is the information that the accountant collected about support department costs:
Support department:............................. Fixed Costs
Administration.................................... $1,034,580
Marketing ..............................................620,748
Distribution ............................................310,374
Customer service .....................................103,458
Total support department costs ..................$2,069,160
REQUIRED
I. Prepare a support department costs budget.
J. Prepare a budgeted income statement. Assume an income tax rate of 25%.
PART 4: CASH BUDGET WITH BAD DEBTS AND BORROWING
Refer to the information for Parts 1, 2, and 3. The company's managers budget cash flows on a quarterly basis so that they can plan short-term investments and borrowings. Kite sales are highest during the spring and summer. Sales are fairly even within each quarter, but sales 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, totalled $90,000.
Payments from customers are usually received as follows:
Pay during the month goods are received ...........................50%
Pay the next month ......................................................47%
Bad debts ..................................................................3%
The managers plan to maintain beginning inventory quantities during January and February but to increase inventories to the 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, so approximately two-thirds of all purchases are paid in the month of production and one-third are paid the following month. Accounts payable at the end of the prior year totalled $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 during 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 to maintain a minimum cash balance of $100,000 (non-interest-bearing). The company's line of credit requires quarterly interest payments at an annual rate of 5.5%. (For simplicity, assume that all borrowings and repayments occur on the last day of each quarter.)
REQUIRED
K. Prepare quarterly budgets for cash receipts, cash disbursements, and short-term financing.

Ending Inventory
The ending inventory is the amount of inventory that a business is required to present on its balance sheet. It can be calculated using the ending inventory formula Ending Inventory Formula =... Accounts Payable
Accounts payable (AP) are bills to be paid as part of the normal course of business.This is a standard accounting term, one of the most common liabilities, which normally appears in the balance sheet listing of liabilities. Businesses receive... Cash Budget
A cash budget is an estimation of the cash flows for a business over a specific period of time. These cash inflows and outflows include revenues collected, expenses paid, and loans receipts and payment. Its primary purpose is to provide the... Line of Credit
A line of credit (LOC) is a preset borrowing limit that can be used at any time. The borrower can take money out as needed until the limit is reached, and as money is repaid, it can be borrowed again in the case of an open line of credit. A LOC is...