ZYX is a wholesale distributor. The company's balance sheet as of November 30 is given below:

ZYX Balance Sheet November 30, 2015
Assets
Cash	$91,000
Accounts receivable (net)*	305,800
Inventory	132,000
Buildings and equipment, net of depreciation	207,000
Total assets	$735,800
Liabilities and Stockholders' Equity
Accounts payable	$125,000
Note payable*	109,000
Common stock	250,000
Retained earnings	251,800
Total liabilities and stockholders' equity	$735,800

*The allowance for doubtful accounts was $3,000.

*The 6% 6-months note payable will be paid on February 1, 2016.

The company is in the process of preparing a budget through January 31, 2016, and has assembled the following data:

a. All sales are on account. Sales terms are 3/10 with the balance due by the end of the month after sale. Experience has shown that 50% of the billings will be collected within the discount period, 25% by the end of the month after sale, and the remaining in the following month. The average selling price of the company's products is $190 per unit. ZYX estimates that 1% of its credit sales will end up uncollected, and reviews the aging accounts receivable at the end of a year.

b. All purchases are payable within 15 days. Approximately 60% of the purchases in a month are paid that month, and the rest the following month. The average unit purchase cost is $150. Target ending inventories are 590 units plus 10% of the next month's unit sales.

c. Actual and projected sales are as follows:

October actual	$289,000
November actual	741,000
December estimated	665,000
January estimated	722,000
February estimated	399,000
Total estimated for year ending June, 2016	$4,200,000

d. Total budgeted marketing, distribution, and customer-service costs for the year are $924,000. Of this amount, $168,000 are considered fixed (and include an annual straight-line depreciation of $80,000). The remainder varies with sales. Both fixed and variable marketing, distribution, and customer-service costs are paid as incurred.

e. New equipment costing $6,500 will be purchased for cash during December.

f. During December, the company will pay $5,000 cash dividends to its shareholders.

g. ZYX reviews its accounts receivable and estimates $3,500 will be uncollectible.

Part 1

Required: (PLEASE PROVIDE AN FUNCTIONAL EXCEL DOC WITH YOUR ANSWER)

1. Analyze the potential effects of the budgeted activities by preparing journal entries on the INPUT worksheet.

2. Set up T-accounts on the PROCESS worksheet, and create functions to post automatically the journal entries made in requirement (1).

3. Prepare a cash budget for December 2015 and January 2016. Supply supporting schedules for collections of receivables; payments for merchandise; and marketing, distribution, and customer-service costs.

4. Based on the account balances, prepare the following budgeted financial statements for December and January on the OUTPUT worksheet. 1) Budgeted Income Statements using the absorption costing income statement format. 2) Budgeted balance sheet. 3) Budgeted Statement of Cash Flows.

5. Prepare an analysis of cash conversion cycle by including the following ratios: a) Accounts receivable turnover; b) Average collection period (in days); c) Inventory turnover; d) Average sale period (in days); e) Accounts payable turnover; f) Average payment period (in days); g) Cash conversion cycle.

Part 2

At the end of December 2015, ZYX has the following actual results:

Units sold	4000
Revenue (sales)	$790,400
Variable costs	$735,072
Fixed costs	$14,200
Operating Income	$41,128

Required:

1) Calculate the Flexible-Budget Variances and the Sales-Volume Variances.

2) Discuss possible causes and potential problems.