*** Please show how to solve in excel using formulas *** The actual sales and pu
ID: 363778 • Letter: #
Question
*** Please show how to solve in excel using formulas ***
The actual sales and purchases for White Inc. for September and October 2006, along with its forecast sales and purchases for the November 2006 through April 2007, follow.
The firm makes 30 percent of all sales for cash and collects 35 percent of its sales in each of the two months following the sale. Other cash inflows are expected to be $22,000 in September and April, $25,000 in January and March, and $37,000 in February. The firm pays cash for 20 percent of its purchases. It pays for 40 percent of its purchases in the following month and for 40 percent of its purchases two months later. Wages and salaries amount to 15 percent of the preceding month’s sales. Lease expenses of $30,000 per month must be paid. Interest payments of $20,000 are due in January and April. A principal payment of $50,000 is also due in April. The firm pays cash dividend of $30,000 in January and April. Taxes of $120,000 are due in April. The firm also intends to make a $55,000 cash purchase of fixed assets in December. Assuming that the firm has a cash balance of $25,000 at the beginning of November and its desired minimum cash balance is $25,000, prepare a cash budget for November through April and determine the cash surplus/deficit for each month.
Year Month Sales Purchases 2006 Sep $310,000 $220,000 2006 Oct $350,000 $250,000 2006 Nov $270,000 $240,000 2006 Dec $260,000 $200,000 2007 Jan $240,000 $180,000 2007 Feb $280,000 $210,000 2007 Mar $300,00 $200,000 2007 Apr $350,000 $190,000Explanation / Answer
It is mentioned that the firm makes 30percent of all sales for cash and collects 35% of its sales in each of the two months following the sale, therefore sales are split into three parts and or each month has three entries, cash, previous month and prior to previous as follows:
Similarly, purchases have three components 20, 40, 40 percentages for current, next and next to next months.
Wages and salaries are 15% of previous months sales (assumed to be given sales data).
Lease expenses of $30,000 per month must be paid. Interest payments of $20,000 in Jan. and April. A principal of $50,000 in April and so on....
Total cash Inlows and outflows are as follows:
Year Month Sales Purchases Cash .3*S Previous.35*S-1 priorPrev.35*S-2 2006 Sep 3,10,000 2,20,000 93000 2006 Oct 3,50,000 2,50,000 105000 108500 2006 Nov 2,70,000 2,40,000 81000 122500 108500 2006 Dec 2,60,000 2,00,000 78000 94500 122500 2007 Jan 2,40,000 1,80,000 72000 91000 94500 2007 Feb 2,80,000 2,10,000 84000 84000 91000 2007 Mar 3,00,000 2,00,000 90000 98000 84000 2007 Apr 3,50,000 1,90,000 105000 105000 98000Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.