Given the following information I have to make budget that is cell referenced in Excel. The first tab must have all the information given in it and the rest of the budgets must cell reference. Each tab is another budget or financial statement.

Betty's Beautiful Baskets

Betty's Beautiful Baskets, a manufacturing business that sells baskets, wants a master budget prepared for the first three months of this year (January, February and March).

The managers of the different departments have provided the following information:

The Sales Manager has projected the following sales:

oJanuary5,000 units

oFebruary4,000 units

oMarch6,000 units

oApril5,000 units

oMay11,250 units

oProjected selling price is $35.00/unit

Your Production Manager gave the following information:

oEnding Inventory is to be 20% of next month's production needs

oApril's Projected Sales 5,000 units

oDecember 20X5 Ending Inventory was 1,000 units and December unit cost was $23.50.

The Manufacturing Manager has estimated the following:

oEach unit will require 4 grams of material

oMaterial in Ending Inventory is 20% of next month's needs

oDecember's Ending Material Inventory was 4,800 g

oProjected cost of material:$2.50/gram

The Personnel Manager has estimated that Direct Labor will be projected at:

o0.75 hours of Direct Labor per unit

oDirect Labor Cost:$8.50/hour

The Facilities Manager has estimated that the Manufacturing Overhead will be projected at:

oVariable Overhead Rate to be $8 per Direct Labor hours

oFixed Overhead Rate to be $3,000 per month

The Accounting Department Manager has provided the following information:

Selling and Administrative Expenses are projected to be a monthly cost of:

oSalaries$6,000

oRent$1,500

oAdvertising$1,100

oTelephone$300

oOther$500

Cash Receivable:

oDecember's Sales were $150,000

o80% of sales is collected in the month in which they were made

o20% of sales collected in the following month in which they were made

oBad Debts is negligible

Accounts Payable:

o80% of Payables is paid for in the current month

o20% of Payables is paid for in the following month

oDecember's purchases were $50,000

Federal Income Tax is estimated at 22% average.

Betty's Beautiful Baskets

ohas a $20,000 cash balance for the beginning of January

opays Dividends of $8,000 to be paid in March

opays projected Federal Income tax in March

odepreciation on the building is $150 per month

odoes not carry any WIP inventory

ouses FIFO inventory costing

From the beginning Balance Sheet:

oLand = $150,000

oBuilding = $45,000

oDepreciation (Building) = $11,250

oRetained Earnings = $58,780

oCapital Stock = $200,470

For the Master Budget, you are expected to prepare the following:

Sales budget plus schedule of accounts receivable collections

Production budget

Direct materials budget and schedule of cash payments for purchases

Direct labor budget

Manufacturing overhead budget

Cost of Goods Sold Budget

Selling & Administrative Expenses Budget

Budgeted income statements

Cash budget

Budgeted balance sheet for each month plus a beginning balance sheet

When you prepare the cost of goods sold budget, you must calculate a unit cost for each month.You must also calculate cost of goods manufactured.Remember, there is no Work in Process inventory but you must calculate direct materials used.

Betty's check figures

Total March sales, $210,000

Total February cash collections, $147,000

Total February units to produce, 4,400

Total March direct materials purchase, $58,900

Total February cash disbursements for raw materials, $46,400

Total January direct labor, $30,600

Total March overhead, $37,800

Total January selling & admin, $9,400

Total February cost of goods sold, $92,182

Total March cost per unit, $22.89

Total March cost of goods manufactured, $132,775

Total January 1 Assets, $269,250

Ending cash, March 31, $120,209

Net income, February, $29,849

Total Assets, March, $380,901

ACC 320, Cost Accounting I

Betty's Beautiful Baskets

After you have completed building the budgets for Betty's, you will change certain amounts on your data page one at a time and answer questions after each change.Do not go back and change those values back to the original; leave them at the new amount as you continue on.You may answer directly on this word document, then save, scan or take a picture, and upload.I will check to see if your spreadsheet actually gives you the results that you put on this page, so please make sure the answers are coming from your spreadsheet!All is due by midnight Thursday, March 22, 2018.

Change your selling price to $50 per unit.

1.What is January net income?

2.What is February month end accounts receivable?

3.What is March ending Retained earnings?

4.What are March total assets?

Change your cost per gram of direct material to $3.00.

5.What is your cash paid for direct materials in January?

6.What is your cost per finished good unit in February?

7.What is cost of goods sold in March?

8.What is ending direct materials inventory in January?

9.What is retained earnings at the end of March?

Change your income tax rate to 40 percent.

10.What is income tax expense in February?

11.What is income tax payable at the end of February?

12.What is ending cash in March?

13.What are total assets at the end of March now?

Change your receivables collections percentages to 60% collected in the month of sale and 40% collected in the month following sale.

14.What is January cash collected from customers?

15.What is accounts receivable at the end of January?

16.What is retained earnings at the end of March?

17.What is the cash balance at the end of February?

Change your fixed overhead amount to $5,000.

18.What is cost per unit in February?

19.What is cost of goods sold in March?

20.What are total cash disbursements in January?

21.What is ending liabilities plus owners' equity at the end of March?

Change your Salaries expense to $10,000.

1. What is net income in January?

1. What is the cash balance at the end of March?

1. What is ending retained earnings in February?

1. What are total assets and total liabilities and equity at the end of March?