You have just been promoted to the position of budget analyst for Lansdale Corporation, a retailer of various gadgets

Exercise 6

 

You have just been promoted to the position of budget analyst for Lansdale Corporation, a retailer of various gadgets.  Your first responsibility as budget analyst is to develop the 2014 budget for the first quarter for Lansdale Corporation’s Colmar Division.

 

On the next page are the actual first quarter results from 2013 for Colmar.  When you discussed the first quarter budget for 2014 with the Controller, she asked that the budget be prepared using the following assumptions:

 

·         Sales revenue for each month of 2014 will be twenty percent more than it was for the same month of 2013.

·         Cost of goods sold will be budgeted as 30 percent of sales in 2014.

·         The selling price of the gadget that Colmar Division produces is $50. (Cost of goods sold will therefore be budgeted as $15 per unit.)

·         Payroll expense is expected to increase by 5 percent in 2014 compared to 2013.  That is, each month of 2014 is expected to have payroll expense expense that is 5 percent more than the same month of 2013. (January, 2014’s payroll expense will be 5 percent greater than January, 2013’s payroll expense; February, 2014’s payroll expense will be 5 percent greater than February, 2013’s payroll expense, and so on.)

·         Payroll expense is a fixed cost for Colmar.

·         Depreciation expense will be the same in 2014 as it was in 2013.  Depreciation expense is a fixed cost.

·         The Marketing Vice President has budgeted $15,000 in advertising expenses for each month of 2014.  The amount spent on advertising is independent of sales revenue.

·         Shipping expenses will be 3 percent of sales.

·         Other expenses for each month of 2014 will be fifteen percent of sales revenue.

 


The Controller has asked you to prepare the first quarter budget and submit it to her for her review.  Your budget should be in the same format as the income statement.  Prepare your 2014 budget using an Excel Spreadsheet.

 

Lansdale Corporation

Quarterly Income Statement

1st Quarter, 2013

 

 

January

February

March

Quarter

 

 

 

 

 

Sales

1,000,000

1,200,000

1,250,000

3,450,000

 

 

 

 

 

Cost of Goods Sold

320,000

350,000

360,000

1,030,000

 

 

 

 

 

Gross Margin

680,000

850,000

890,000

2,420,000

 

 

 

 

 

Operating Expenses:

 

 

 

 

 

 

 

 

 

Payroll

250,000

250,000

300,000

800,000

Depreciation

50,000

50,000

50,000

150,000

Advertising

10,000

10,000

10,000

30,000

Shipping

36,000

40,000

40,000

116,000

Other Expenses

150,000

155,000

160,000

465,000

 

 

 

 

 

Total Operating Expenses

496,000

505,000

560,000

1,561,000

 

 

 

 

 

Net Operating Income

184,000

345,000

330,000

859,000

 


 

 Exercise 7

 

The Controller has also asked you to prepare a cash budget for the Colmar Division for the first quarter of 2014.  Your cash budget should summarize all cash receipts and disbursements for Colmar, using the following assumptions in addition to the assumptions you used in exercise 6:

 

·         Colmar Division will have an opening cash balance of $20,000 on January 1, 2014.

·         Sales are collected entirely in the month of sale.

·         Colmar estimates that December, 2013 sales will be $1,600,000, and that cost of goods sold in the month of December will be 30 percent of sales revenue.

·         Colmar expects to pay for inventory (cost of goods sold) during the month after the inventory is sold. (Cost of goods sold for December, 2013 will be paid in January, 2014.)

·         Lansdale Corporation pays its employees once a month.  Payroll expenses are paid in the month after they are earned.  Colmar expects to have $300,000 in unpaid payroll expenses at December 31, 2013.  Colmar will pay its employees the $300,000 in January, 2014.

·         Advertising expenses are paid once each quarter.  They are paid in the third month of the quarter in which they are incurred.

·         Shipping expenses are paid entirely in the month incurred.

·         50 percent of “other” operating expenses are paid in the month in which they are incurred, and 50 percent are paid in the following month.

·         Colmar Division expects to have $120,000 in unpaid “other” operating expenses at December 31, 2013.  You should assume that Colmar will pay the $120,000 that is unpaid at December 31, 2013 during January, 2014.

 

You do not need to do a financing section on your cash budget.  However, Lansdale wants to maintain a minimum cash balance of $500 and intends to invest any surplus cash.  Please indicate how much investable cash the Colmar Division will have at the end of each month of the first quarter. (On January 1, they will have $19,500 of investable cash - $20,000 cash with a maintained minimum balance of $500.)

 


 

Exercise 8

 

At the end of the first quarter, the Colmar Division reported the following results:

 

Sales

4,050,000

 

 

Cost of Goods Sold

1,260,000

 

 

Gross Margin

2,790,000

 

 

Operating Expenses:

 

 

 

Payroll

870,500

Depreciation

150,000

Advertising

42,000

Shipping

128,000

Other Expenses

643,000

 

 

Total Operating Expenses

496,000

 

 

Net Operating Income

184,000

 

The Colmar Division sold 80,500 gadgets during the first quarter.  Given this information and the information provided with Exercises 6 and 7, present a Flexible Budget Performance Report, including all activity, revenue, and spending variances.

    • Posted: 6 years ago
    Lansdale corporation complete tutorial in excel

    Purchase the answer to view it

    blurred-text
    Save time and money!
    Our teachers already did such homework, use it as a reference!