Telco limited sells Local developed networks or LAN across Canada which are comprised of hardware costs of Fiber, Routers and Circuits per LAN Site. The LAN network are assembled by the network technicians and sales engineers of the company. The accountant at Telco limited has always prepared a budget that is calculated using only one driver, 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 on network sales 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.

Direct Materials LAN Network:
Fiber	$12,000.00
Routers	$7,000.00
Circuit	$5,000.00
Direct Labour:
Hours	Assembly	60
	Networking	40
Cost Per Hour	Assembly	$60.00
	Networking	$100.00
Cost Per LAN	Assembly	$3,600
	Networking	$4,000
Inventory Information:
	Beginning	Target Ending
Fiber	$500,000	$750,000
Router	$300,000	$320,000
Circuit	$200,000	$275,000
Finished Goods (units)	2,000	3,000
Finished Goods (cost)	$125,000,000
Revenue Assumptions:
Selling Price per LAN Network	$50,000
Volume of LAN Sales	10,000

Instructions:

1. 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. (5 marks)

2. Prepare a revenue budget. ( 2 marks)

3. Prepare a production budget in units. ( 3 marks)

4. Prepare the direct materials usage budget and a direct- materials purchases budget. (8 marks)

5. Prepare a direct labour budget (in hours and cost). ( 6 marks)

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 cell phones produced:

Estimated variable manufacturing overhead costs:
Supplies	$10,750,000
Indirect labour	10,225,000
Maintenance	3,250,000
Miscellaneous	1,250,000
Total	25,475,000
Estimated fixed manufacturing overhead costs:
Amortization	$7,500,000
Property Taxes	1,250,000
Insurance	2,500,000
Plant Management	2,750,000
Fringe Benefits	2,000,000
Miscellaneous	1,250,000
Total	17,450,000

6. 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. ( 5 marks)

7. Prepare a schedule that calculates the unit costs of ending inventory in finished goods, and then prepare the ending inventories budget. (8 marks)

8. Prepare a cost of goods sold budget. (8 marks)

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	$15,000,000
Marketing	10,250,000
Distribution	5,000,000
Customer Service	10,000,000
	$40,250,000

9. Prepare a support department cost budget ( 2 marks)

10. Prepare a budgeting income statement (Assume a tax rate of 25%) (8 marks)

PART 4: CASH BUDGET WITH BAD DEBTS AND BORROWING

Refer to the information for Parts 1,2,3. The companys managers budget cash flows on a quarterly basis so that they can plan short-term investments and borrowings. Cellular phone sales are highest during the spring and summer.

Sales are fairly even within each quarter, but sales vary across quarters as follows:

January-March	20%
April-June	30%
July-September	20%
October-December	30%

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

Pay during the month goods are received	60%
Pay the next month	40%
Bad Debts	0%

The company pays its vendors 10 days after raw materials are received, so approximately 80% of purchases are paid in the month of production and 20% are paid the following month. Accounts payable at the end of the prior year totalled $1,115,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 the quarter , based on 25% of total estimated taxes for the year. In addition to customer receipts, the company expects to receive $2,500,000 in proceeds from the sale of equipment during January. The company also plans to purchase and pay for new equipment costing $5,000,000 in January. The company finances its short-term operations with a line of credit from the bank , which had a balance of $500,000 at the end of the prior year. The line of credit requires quarterly interest rates payments at an annual rate of 3.65%. (For simplicity, assume that al borrowing and repayments occur on the last day of each quarter)

11. Prepare quarterly budgets for cash receipts, cash disbursements and short-term financing. (35 marks,)

The budgets are to be prepared for the following year from January to December. The master budgets, cost of goods sold and budgeted income statements to be presented yearly. The cash budget presented in quarters.

Please clearly label schedules, calculations and assumptions as marks will also be awarded based on audit trails and presentation. (10 marks)