1. Modern Goods Corporation has the following budgeted sales forthe selected six month period:

January 15,000 units

February 20,000 units

March 35,000 units

April 25,000 units

May 30,000 units

June 20,000 units

There were 7,500 units of finished goods in inventory at thebeginning of January. Plans are to have an inventory offinished product equal to 20% of the unit sales for the nextmonth. 3 pounds of material are required for each unitproduced. Each pound of material costs $20. Inventorylevels for materials equal 30% of the needs for the nextmonth. Materials inventory on Janaury 1 was 5,000pounds.

In Excel, calculate the following. Clearly labelyour work, then copy and paste your answers backhere. Your answer may certainly contain more monthsthan February and March, but those are the two months that will begraded.

Calculate:

a production budget in units for February andMarch

a direct materials budget in pounds and dollars forFebruary and March

2.

The City of Charleston had the following sales of water for theselected months of 2016:

February $50,000

March $45,000

April $60,000

May $42,500

June $70,000

July $120,000

All sales are on credit. Historically 50% is collected inthe month of the sale, 35% in the month following the sale, and 15%in the month following that.

Cost of water averages 75% of sales. Water is purchased inthe month of the sale. All purchases are paid in the monthfollowing the purchase. Other operating costs of $10,000 areincurred and paid each month.

The April 1st cash balance is expected to be the minimum balanceof $5,000. Money can be borrowed from a local bank inincrements of $1,000. Ignore possible interest charges fornow.

In Excel, prepare a cash budget for April AND May. Label your work clearly. You may include more months thanrequired, but April and May are the ones that will be graded. Copy and paste your excel work back intohere.