You are the budget manager in your company. The board of directors has asked you to prepare a budget for the next six months. You are required to prepare this assignment in excel. A template is provided to get you started. Your are free to emblish your speadsheet in any way. Make sure your formatting choices enhance your final presentation and are professional looking. This should be something you would be proud to present to the board of directors. Please make sure that your final product is print ready. The end user should not have to re-format your spreadsheet to print properly. Include your name in the header of each worksheet. The following facts have been given:

The Alpha Company manufactures sunglasses sold throughout the United States. The sunglasses are forecast to sell for $25 each. Recent and forecast sales in units for 20x1 are:

January                      10,000                         July                 40,000

February                    12,000                         August                36,000                     

March                        15,000                         September       32,000

April                           17,000                         October           22,000

May                            20,000                         November       12,000

June                          30,000                         December       10,000

All sales are ‘on account' (meaning credit) and collected as follows: 20% in the month of sale and the remaining 80% in the following month. The following entry is made each time a credit sale is made:

Debit: Account Receivable
Credit: Sales

The beginning (1/1/x1) Account Receivable balance is $100,000 (remaining prior year Dec sales to be collected).

The sunglasses are expected to cost the company $10 each. Ending inventories are supposed to equal 75% of the next month's sales in units. Beginning Inventory in January 20x1 is 8000 units. Sales for 20x2 are expected to be the same as above units with an increase of 20% (use this to determine December's 20x2 ending units).

All purchases are ‘on account' (meaning credit). Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. The following entry is made each time a credit purchase is made:

Debit: Inventory
Credit: Account Payable

The beginning (1/1/x1) Account Payable balance is $50,000 (remaining prior year Dec purchases to be paid).

The company's monthly operating expenses are given below:

Sales commissions                             $3 per pair of sunglasses

Wages and salaries                               $30,000

Utilities           & Rent                            $25,000

Advertising                                           $5,000

Other Expenses                                    $3,000

All operating expenses are paid during the month, in cash. In addition the company has property taxes that will be paid in February of $30,000. They anticipate an income tax payment in April of $150,000.

Requirement 1: Prepare the following budgets for the first six months of the upcoming year:

Sales Budget (expected units sold x sales price)

Cash Collection Budget

Production budget (in units) (sale units + expect End. Inv = Total needs - Beg. Inv = Production needed)

Purchase budget (production units x cost per unit = $ purchases

Cash Payment budget (include inventory purchases & other expenes)

Cash Flow Analysis (Total Cash collections less total cash payments)

Requirement 1: Prepare a budgeted income statement for each month for the next six months. Include a common size income statement for the budgeted numbers (divided each number to sales).