Problem:

Only fill in the yellow and gold cells. Cells in white should be left as is$.$ Very little hard$-$coding should be entered. Formulas and cell referencing should be used whenever possible.

a$.$ Last January there were $7,000$ sales at $$45$ each. This year the company expects to sell the same amount in January, and then increase sales by $3\mathrm{.}5\%$ each month in the following year. The company typically has a $50/50$ ratio for credit and cash sales in any given month.

b$.$ Sales made on account are typically collected $30\%$ in the month of the sale and $70\%$ in the month following the sale.

c$.$ Budgeted COGS are expected to be $55\%$ of sales each month, and the company budgets for an ending inventory balance of $15\%$ of next month's COGS. All inventory purchases are made on account.

d$.$ The company's cash policy pertaining to inventory purchases is to pay all bills as they become due. An analysis of historic payables estimates that $40\%$ of invoices require immediate payment, and $60\%$ are due the following month.

e$.$ The company's budgeted selling and admin expenses are shown on the Supplementary Schedules tab. The company has a policy to pay employees' salary, rent on the office, and equipment maintenance contractors in the month the expenses are incurred. Insurance is pre$-$paid twice per year, on January and July $15$th$.$ All other expenses that require cash payment will be paid in the month following when the expense is incurred, including employee comissions.

f$.$ The payments for capital budget acquisitions can be found on the supplementary schedule tab.

g$.$ The company maintains a cash balance of at least $$50,000$ at the end of each month. The company can draw on a line of credit with a debt maximum of $$1,500,000,$ and draws on the account in increments of $$1,000.$ Payments are made in any increment. All borrowing and repayment is done on the last day of each month. The line of credit interest rate and balance are listed below.