$1$

$2$ of $3$

II$.$ Computer Project $($Data and Requirements$)$:

The Excel template has four tabs$.$

TAB $1.$ DATA

TAB $1$ provides the given information for preparing budgets. The balance sheet from the last accounting period $($prior to preparing the budgeted financial statements$)$ is given in TAB$1$A$.$ All the data given below are included in TAB$1$B$.$

DATA:

a$.$ Actual sales for March and budgeted sales for April$-$July are as follows:

March $($actual$)$

$$80,000$ $$87,000$

April

May

June

July

$$96,000$

$$90,000$

$$76,000$

b$.$ Sales are $30\%$ for cash and $70\%$ on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at March $31$ are a result of March credit sales.

c$.$ The company's Cost of Goods Sold is $65\%$ of sales.

d$.$ The company's selling and administrative $($S&A$)$ expenses are as follows:

Monthly Expenses:

Variable:

Shipping $,4\%$ of sales

Other expenses $,6\%$ of sales

Fixed:

Salaries and wages $$10,000$ per month

Advertising $$8,000$ per month

Quarterly Expenses:

Depreciation: Depreciation for the entire quarter, including new assets acquired during the quarter will be $$6,500.$

$($Note: Depreciation expenses are noncash expenses. The company's shipping, salaries and wages, advertising, and other S&A expenses are cash S&A expenses.$)$

e$.$ Each month's ending inventory should equal $20\%$ of the following month's cost of goods sold.

f$.$ Inventory purchases are paid as follows: $50\%$ of a month's inventory purchases is paid for in the month of purchase; the other $50\%$ is paid in the following month.

g$.$ Equipment purchases during the quarter will be as follows $($both purchases paid in cash$)$: April: $$12,500,$

May: $$2,500.$

h$.$ Dividends totaling $$4,500$ will be declared and paid in June.

i$.$ The company desires a minimum ending cash balance each month of $$5,000.$ The company has an agreement with a bank that allows it to borrow in increments if $$1,000$ at the beginning of each month, up to a total loan balance of $$20,000.$ The interest rate on

$2$

these loans is $1\%$ per month, and for simplicity, we will assume that interest is not compounded. At the end of the quarter, the company would repay all of the accumulated interest on the loan and as much of the loan as possible $($in increments of $$1,000),$ while still retaining at least $$5,000$ in cash.

j$.$ The company's balance sheet at March $31$ is given on TAB $1$A$.$

Your required tasks are as follows:

Please complete the areas shaded in blue and orange in the templates provided in TAB$2,$ TAB$3,$ and TAB$4.$

For cells in blue, use Excel formulas and calculate the answer based on information found in other cells.

For cells in $,$ you may calculate the answer based on information found in other cells and directly enter the correct number OR you may use Excel formulas to calculate the answer.

TAB $2.$ BUDGET

Prepare a master budget for the three$-$month period ending June $30,2024.$ Cells in your budget should be linked to the data from TAB $1$ or completed data from TAB $2.$ Complete the template provided in this tab. Include the following detailed budgets:

A$.$ A schedule of expected cash collections from sales, for each month and the quarter. B$.$ A merchandise purchases budget in dollars. Show the budget for each month and the quarter.

II$.$ Computer Project $($Data and Requirements$)$:

The Excel template has four tabs$.$

TAB $1.$ DATA

TAB $1$ provides the given information for preparing budgets. The balance sheet from the last accounting period $($prior to preparing the budgeted financial statements$)$ is given in TAB$1$A$.$ All the data given below are included in TAB$1$B$.$

DATA:

a$.$ Actual sales for March and budgeted sales for April$-$July are as follows: March $($actual$)$

April

May

June

July

$$80,000$

$$87,000$

$$96,000$

$$90,000$

$$76,000$

b$.$ Sales are $30\%$ for cash and $70\%$ on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at March $31$ are a result of March credit sales.

c$.$ The company's Cost of Goods Sold is $65\%$ of sales.

d$.$ The company's selling and administrative $($S&A$)$ expenses are as follows:

Monthly Expense