The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods.

Current Assets Cash $8,000

Accounts Receivable $20,000

Inventory $36,000

Buildings and equipment, net --------- $120,000

Accounts Payable ---------------------- $21,750

Common shares -------------------------- $150,000

Retained earnings ----------------------- $12,250

a. The gross margin is 25% of sales

b. Actual and budgeted sales data are as follows:

March (Actual) -------------------------------------- $50,000

April -------------------------------------------------- $60,000

May -------------------------------------------------- $72,000

June -------------------------------------------------- $90,000

July ------------------------------------------------- $48,000

c. Each month ending inventory should equal 80% of the following months budgeted cost of goods sold.

d. Monthly expenses are follows: Commissions 12% of sales; rent $2,500; other expenses (excluding depreciation), 6% of sales; Depreciation is $900 per month.

Required: Prepare the following budgets as a Microsoft Excel financial planning model to determine the operating income for the three months. Do not prepare the cash budget or the budgeted balance sheet.

IMPORTANT: The data cells in the Excel spreadsheet should be linked so that senior management are able to carry out what-if or sensitivity analysis.

(Part A) Prepare the following Operating Budgets:

1.Monthly Sales Budget (April, May, June) and in Total

2. Monthly Merchandise Purchase Budget (April, May, June) and in Total

3. Monthly Selling and Administration Budget (April, May, June) and in Total

4. Budgeted Income Statement in Total

(Part B) Sensitivity Analysis: (What-if-Analysis)

Change the gross margin percentage to 40% of sales? What happens to the Operating Income? Comment. You can provide your comments on the spreadsheet it self.

Hint:

(1) For Part B, you do not need to re-do all the budgets. Just changing the number in the Excel Cell where you have the gross margin percentage should be the proper way to design the spread sheet.

(2) Keep all the data in one area on the spreadsheet, then link the cells from there.