Tiny Teapots is a small, local manufacturing company who produces and sells one style of teapot. Their product is sold to independent distribution centers and superstores.

All necessary information for Tiny Teapots budgeting process is provided in the Inputs tab in your Excel project template. Please read through all information provided in the inputs tab before beginning the budgeting process. A few pieces of additional information follow.

Tiny Teapots is in need of an open line of credit from a local bank to meet their cash needs, as well as to help improve their business functions. They provided the bank with their 2016 financial statements. After glancing through the financial statements, the banker realized that although Tiny Teapots had been a profitable company, their sales numbers are very volatile. The banker was concerned about the ability of Tiny Teapots to generate sufficient cash flow to pay off any debt that was extended. To alleviate the banks concern, Tiny Teapots has been asked to prepare a quarterly master budget for the upcoming year (2017), including financial statements.

The company must maintain a minimum cash balance of $1,000 each quarter. The cash balance at the beginning of January (2017) was $2,500. If approved, the local bank will allow the company to borrow up to $ 20,000 per quarter. (When preparing a budget, the minimum cash balance must ALWAYS be met even if it forecasts cash needs beyond the credit line available. If the available line of credit is insufficient, then alternative arrangements for financing must be made.)

All borrowing is done at the beginning of a quarter, and all repayments are made at the end of a quarter. Interest is paid for each quarter at the beginning of the next quarter, thus each quarters interest paid is based on the outstanding borrowings the prior quarter. The interest rate is 14% per year [Note that there are 4 quarters in a year]. If there are excess funds above the minimum $1,000 cash at the end of the quarter, these are used to repay the outstanding loans. When computing interest and repayment watch the number of quarters the money was borrowed.

The Company uses variable costing in its budgeted income statement and its balance sheet.

It is your responsibility to:

A. Start with the template that can be found in the Blackboard folder titled Excel Project. Save this file with your name in the title. Example) Excel Project Kayla Sander

B. The tab of the template labeled Input should serve as your input tab. This tab has already been filled in for you with all information necessary for budgeting. In the following ten tabs, you will need to fill in each cell with a light grey shading. Every cell you fill should be ENTIRELY FORMULA DRIVEN. This means that on the following tabs of your workbook, EVERY light grey cell should either reference another cell from the file or should be calculated using a formula which also references the necessary cells. *NO numbers can be typed into cells with the exception of what has already been filled in for you on the input tab!*

*Note: you must manually type in your new sales price value in the Goal Seek tab once you have run this process successfully. It is ok to type the numbers here! When grading, we will run the goal seek process to ensure it works with your file rather than just looking to see if you have the correct value listed on the tab.

C. When you have finished the project, you will need to UPLOAD YOUR COMPLETED FILE IN THE BLACKBOARD DROPBOX provided in the Excel Project folder. Please ensure that you have your name in the file that is uploaded.

HINTS:

The trickiest part of the assignment is computing the borrowings and repayments.You are expected to create logical formulas here. Remember, the borrowings are at the beginning of the quarter and repayments at the end. If you borrow in quarter 1 and repay in quarter 2, there will be 6 months of interest to be paid (beginning of January to end of June). Interest is paid the first of the next quarter.

Interest Expense on your Income Statement needs to not only include the interest paid during the year, but also the interest owed for the 4^th quarter borrowings.

Use goal seek to find the selling price that would be needed to eliminate the need for any borrowings (make total borrowings for the year $0). Running this function will change many cells in your spreadsheet. In this case, you want to change the total annual borrowings on the Cash Budget to zero by changing the selling price on the input tab. Once you have successfully found your new selling price, manually enter the price into the GOAL SEEK worksheet, and change the selling price on the input tab back to the original $15.

To find goal seek: click on the Data tab and under Data Tools click on the What-If Analysis

If you begin to see ##### at any time when you are working, it is ok. These just show up when the column is not wide enough. If you simply expand the width of your column these will turn back into numbers.

As you work, check your file against the check figures provided below. This will cut down on wasted time trying to go back and find where you went wrong after completing the entire spreadsheet. If you are meeting the check figures, you should be on the right track!Check figures:

Total Sales: $423,300

Total Collections: $407,675

Total Production in Units: 28,652

4^th Quarter Raw Materials Purchases Cost: $39,461

2^nd Quarter Direct Labor Cost: $34,150

3^rd Quarter Overhead Disbursements: $13,530

Total S&A Disbursements: $67,495

Accumulated Borrowings at End of 3^rd Quarter: $11,842

4^th Quarter Ending Cash Balance: $7,199

Net Income: $37,883

Total Assets: $168,467

Goal Seek Selling Price: $16.85

Here is what i have:

Sales Budget
Year	Quarter	Unit Sales	Unit Selling Price	Total Sales
2017	1st	3,520	$15	$52,800
2017	2nd	6,500	$15	$97,500
2017	3rd	8,700	$15	$130,500
2017	4th	9,500	$15	$142,500
		28,220	$15	$423,300

2) Cash Collection Budget

Cash Collection Budget
	1st Qtr	2nd Qtr	3rd Qtr	4th Qtr	Year
Current Quarter	$39,600 (52,800*75%)	73125 (97,500*75%)	$97,875 (130,500*75%)	$106,875 (142,500*75%)
Prior Quarter	$20,000	13200 (52,800*25%)	$24,375 (97,500*25%)	$32,625 (130,500*25%)
Total	$59,600	$86,325	$122,250	$139,500	$407,675

3) Production Budget

Production Budget
	2017					2018
	1st Qtr	2nd Qtr	3rd Qtr	4th Qtr	Year	1st Qtr	2nd Qtr
Sales in units	3520	6500	8700	9500	28220	6400	9200
Plus: Desired Finished Goods Ending Inventory (15% of next quarter's sales)	975	1305	1425	960	4665	1380
Total Needs	4495	7805	10125	10460	32885	7780	9200
Less Beginning Finished Goods Inventory	528	975	1305	1425	4233	960	1380
Units to be produced	3967	6830	8820	9035	28652	6820

4) Raw materials budget

Raw materials budget
	2017					2018
	1st Qtr	2nd Qtr	3rd Qtr	4th Qtr	Year	1st Qtr	2nd Qtr
Budgeted Production Units	3,967	6,830	8,820	9,035	28,652	6,820
Raw material needed per unit	1	1	1	1	1	1
Total Raw material needs for production	3,967	6,830	8,820	9,035	28,652	6,820
Add: Budgeted ending direct material Inventory (12% of next month production need)	820	1,058	1,084	818	$3,781
Total Direct material needed	4,787	7,888	9,904	9,853	32,433
Less: Beginning Direct material Inventory	476	820	1,058	1,084	$3,438
Budgeted Direct material required purchases units	4,311	7,069	8,846	8,769	28,994
Cost per unit	$4.5	$4.5	$4.5	$4.5	$4.5
Budgeted Purchases Cost	$19,398	$31,810	$39,806	$39,461	$130,475

Factory overhead Budget
Direct labor cost D	$1,984	$3,415	$4,410	$4,518	$14,326
Variable overhead rate	3	3	3	3	3
Total variable cost `F	5951	10245	13230	13553	42978
Fixed overhead
Fixed cost	$1,000	$1,000	$1,000	$1,000	$4,000
Total fixed expenses	$1,000	$1,000	$1,000	$1,000	$4,000
Total manufacturing overhead F+Z	$6,951	$11,245	$14,230	$14,553	$46,978
Less: Non cash fixed	700	700	700	700	$2,800
Oveerhead disbusrement	$6,251	$10,545	$13,530	$13,853	$44,178
Selling and Administrative Expenses Budget
Units sold	3520	6500	8700	9500	28220
Variable rate	2.25	2.25	2.25	2.25	2.25
Total variable cost `F	7920	14625	19575	21375	63495
Fixed S & Ad exp
Fixed cost	$1,500	$1,500	$1,500	$1,500	$6,000
Fixed S & Ad exp	$1,500	$1,500	$1,500	$1,500	$6,000
Total manufacturing overhead F+Z	$9,420	$16,125	$21,075	$22,875	$69,495
Less: Non cash fixed	500	500	500	500	$2,000
S & A expenses disbursements	$8,920	$15,625	$20,575	$22,375	$67,495

1 Qtr	IiQtr	III rd Qtr	Ivth Qtr	Qtr
Beginning cash balance	2500	4407	1000	1000	2500
Cash receipts from customers (from schedule 2)	$59,600	$86,325	$122,250	$139,450	$407,625
Total cash available	$62,100	$90,732	$123,250	$140,450	$410,125
Cash disbursements:
Direct material purchased in prior qtr (15%) (FROM SCHEDULE below raw material budget)	$5,200	$2,910	$4,771	$5,971	$18,852
Direct material purchased in current qtr (85%)	$16,488	$27,038	$33,835	$33,542	$110,903
Paymentt of direct labor (from schedule above)	$19,835	$34,150	$44,100	$45,175	$143,260
Overhead (from overhead schedule)	$6,251	$10,545	$13,530	$13,853	$44,178
S & A expenses (from schedule above)	$8,920	$15,625	$20,575	$22,375	$67,495
Dividends	$1,000				$1,000
equipment purchases		$15,000	$1,200		$16,200
Interest (paid in next qtr) (15536*14%*3/12 in III qtr) (15536*14%*3/12 in IV qtr			$544	$544	$1,088
Total cash disbursements D	$57,693	$105,268	$118,555	$121,459	$402,976
Balance before financing C-D	$4,407	($14,536)	$4,695	$18,991	$7,149
Minimum balance	$1,000	$1,000	$1,000	$1,000	$1,000
Excess/(deficiency)	$4,407	($14,536)			$7,199
Borrowing (as II qtr has $14536 deficit and 1000 min bal req. So $15536 will be borrowed		$15,536			$15,536
Acc. Borrowings at the beg of qtr			$15,536	$11,842
Repayments			($3,695)	($11,842)	($15,536)
Ending cash balance	$4,407	$1,000	$1,000	$7,199	$7,199
Acc. Borrowings at the ending of qtr		$15,536	$11,842
Ending cash balance is beginning cash balance of next qtr

I need with an explanation on how to do it (formulas) the remaining:

Budget Case Input Section Balance Sheet As of December 31, 2016 Qtr 4th Qtr t Qtr Assets Liabilities and Equities Qtr 6,500 8,700 9,5006,400 9,200 Cash 500 Accounts payable Accounts Receivable Raw materials inventory Finished goods inventory Property, Plant and equipment, net $ 20,000 Notes Payable S 2,142 Interest payable S 5,808 Dividend payable $1,000 $10,000 100,000 Common Stock Retained earnings Accounts receivable, December 31, 201620,000 tal a 15% of next Raw materials Inventory: Raw materials needed per teapot tion needs Accounts payable December 31, 2016 $ 10.00 per hour $3.00 per direct labor hour $ 1,000 per quarter Noncash fixed (included in above) Selling & Admin Expenses: $ 1,500 per quarter Noncash fxed (included in above) $ 2,500 $ 1,000 14% per year 1st Quarter 1,000 Declared every December (Paid every Jan $ 15,000 in June (2'e Quarte Equipment purchase Quarter