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).