Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

The Norton Company produces a product that has the following sales expectations for 2007: Sales ($) May 150,000 June 150,000 July 300,000 August 450,000 September

The Norton Company produces a product that has the following sales expectations for 2007:

Sales ($)

May

150,000

June

150,000

July

300,000

August

450,000

September

600,000

October

300,000

November

300,000

December

75,000

January,

150,000

February

170,000

March

180,000

Of these sales, 5% are collected during the month, 70% are collected the next month, and 25% are collected in the third month.

The company is in the process of developing the cash budget for July through December. The company has the following monthly expenses:

Administrative cost $50,000
Lease Payment $10,000

The wage rate for labor is $7.50 per hour, and salespeople receive a commission of 8% of sales. It has been determined that each dollar of sales requires 2 minutes of labor. This labor is done for 10% of the sales three months away, for 80% of the sales two months away, and for 10% of sales one month away, and no labor is spent on the current month's sales. The commission is paid during the month the sale is made. Of the total wage bill for a month, 80% percent is paid during the month and 20% in the following month.

The company will receive $2 million in October from the sale of securities and will make a $2 million progress payment on a new plant in November. In addition, in September and December, the company will make a $50,000 installment payment on taxes. After the sale of securities, the company will be required to make monthly interest payments. The securities carry a 9.125% annual interest rate.
The inventory the company purchases represent 60% of gross sales. In order to have proper inventory, the company in any month purchases 65% of next month's sales, 25% of the sales 2 months hence, and 10% of the current month's sales. In addition, the company keeps a safety stock equal to 15% of the average sales for the next three months. Ten percent of the inventory purchases are paid for in the current month, with the remainder being paid in the following month.

The company has a policy that a minimum of $50,000 must be kept on hand at the end of the month. The excess is invested in 30-day marketable securities yielding 7% annually. Shortages are borrowed at 9% annually and repaid as soon as possible. At the end of May, the cash balance was $110,000, of which $60,000 was invested in 30-day security.

Required

  1. 1. Prepare a cash budget using Microsoft Excel. The final budget must be pasted into Microsoft Word.
  2. The company has always had very weak sales in the winter months. This time the sales manager is convinced that he can improve the sales in December, January, February, and March by 50%, 40%, 40%, and 40% respectively.
    1. 2. What impact does this have on the cash requirements of the company?
    2. 3. Rerun your model. Explain what happens.

Step by Step Solution

3.31 Rating (172 Votes )

There are 3 Steps involved in it

Step: 1

Cash budget refers to the type of budget which estimates all the desired cash coming and exiting out ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamental Managerial Accounting Concepts

Authors: Thomas Edmonds, Christopher Edmonds, Bor Yi Tsay, Philip Old

7th edition

978-0077632427, 77632427, 78025656, 978-0078025655

More Books

Students also viewed these Accounting questions