Reference no: EM131064723
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).
|
Thinking of building a chemical processing plant
: GDebi Enterprises is thinking of building a chemical processing plant to produce 4-hydroxy-3-methoxybenzaldehyde. The firm estimates that the initial cost of the project will be $13.2 million, and the plant will produce cash inflows of $6 million for..
|
|
What is the expected value of this stock ten years
: SRS, Inc. just paid an annual dividend of $1.78 last month. The required return is 12 percent and the dividend growth rate is expected to be constant at 2.3 percent. What is the expected value of this stock ten years from now?
|
|
Two bond issues outstanding
: Dinklage Corp. has 5 million shares of common stock outstanding. The current share price is $84, and the book value per share is $7. The company also has two bond issues outstanding. What are the company’s capital structure weights on a market value ..
|
|
Next two annual dividends will be in the amount
: Last week, Onboard Co. has announced that the next two annual dividends will be in the amount of $2.3 and $4.19, respectively. After that, the dividends will increase by 3.6 percent annually. The required return on this stock is 12.98 percent. What i..
|
|
Prepare a budgeted income statement for each month
: 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).
|
|
Face value bond outstanding-what is the coupon rate
: The Boston Clothing Co. has $1,000 face value bond outstanding with a market price of $953. The bond pays interest annually, matures in 15 years, and has a yield to maturity of 7.8 percent. What is the coupon rate?
|
|
Internal cash flow-public equity market-strategic alliances
: How might the size of a company affect obtaining R&D funding from the following sources: venture capital, internal cash flow, public equity market, strategic alliances? Are company stability and assets more important, and if so, for which types of so..
|
|
Mathematics and statistics
: Your answers must be presented in task number order and be clearly labelled with the appropriate task number. Answers to each task must start on a new page.
|
|
How much would you be willing to pay for this bond
: HexChat, Inc. has issued 17 year bonds 5 years ago. The bonds pay semiannual coupons, with a coupon rate of 5.8 percent, and $1000 face value. If your required return on this investment is 9.8 percent APR, how much would you be willing to pay for thi..
|