Question

In: Accounting

please use excel to do it. it also need to come with formula. formula is very...

please use excel to do it. it also need to come with formula. formula is very important. many many thanks.

  1. Prepare a sales budget for January through March and for the Quarter in total. The selling price per unit is $50.00.

December of the previous year           20,000

January                                     100,000

February                                   80,000

March                                       60,000

April                                         40,000

                         

2. Prepare a purchases budget and the schedule for Disbursements for Purchases for January through March and for the first quarter in total. Assume that the company only sells one product that can be purchased at $35.00 per unit. The market for this product is very competitive and customers highly value quality and on time delivery of the product. Also assume that currently it is company policy that ending inventory should equal 50% of next month’s projected sales.

3. Prepare a cash budget for January through March and for the first quarter in total. The company maintains a minimum cash balance of $70,000.00, and this was the balance in the cash account on January 1. Past experience shows that 40% of sales are collected in the month of the sale, and 60% in the month following the sale. Selling cost is $12 per unit sold. Other expenses include $35,000 per month for rent, $104,000 for advertising, and $76,000 per month for depreciation. All costs are paid in the current month except inventory purchases, which are paid in the month following the purchase (i.e. January purchases of inventory are paid in February). The company has an open line of credit with a bank and can borrow at an annual rate of 12%. For simplification assume that all loans are made at the beginning of the month when a borrowing need is identified and repayments are made at the end of a month if there is enough cash to make the payment. Also, interest associated with a loan is only paid at the time when that loan is paid (i.e. a loan is only paid if there is enough cash to pay off the whole loan, any interest associated with it and still have enough cash left over for the minimum cash balance.)

Solutions

Expert Solution

1.sales budget

sales budget
particulars january february march total
estimated sales in units(a) 100000 80000 60000 240000
selling price per unit(b) $50 $50 $50 $50
total sales(c=axb) $5,000,000 $4,000,000 $3,000,000

2. purchase budget

purchase budget
particulars january february march total
estimated material required 100000 80000 60000 240000
add:desired closing stock(50%) 40000 30000 20000 90000
total required 140000 110000 80000 330000
less:opening stock available 50000 40000 30000 120000
material to be purchased 90000 70000 50000 210000
purchase cost per unit $35 $35 $35 $35
total material purchase cost $3,150,000 $2,450,000 $1,750,000 $7,350,000

schedule for payment for purchase

schedule for payment of purchase costs
particulars decemebr january february march total
estimated material required 20000 100000 80000 60000 240000
add:desired closing stock(50%) 50000 40000 30000 20000 90000
total required 70000 140000 110000 80000 330000
less:opening stock available 10000 50000 40000 30000 120000
material to be purchased 60000 90000 70000 50000 210000
purchase cost per unit $35 $35 $35 $35 $35
total material purchase cost $2,100,000 $3,150,000 $2,450,000 $1,750,000 $7,350,000
payment(1 month in arrear) $2,100,000 $3,150,000 $2,450,000 $7,700,000

3.cash budget

cash budget(in $)
particulars january february march total
cash balance at the beginning 70000 70000 421000
cash inflows:-
40% in the month of sale 2000000 1600000 1200000
60% in following month 600000 3000000 2400000
total cash inflows 2600000 4600000 3600000
cash outflows:-
selling expenses 1200000 960000 720000
rent 35000 35000 35000
advertising 104000 104000 104000
material purchase 2100000 3150000 2450000
cash balance at the end 70000 70000 70000
total cash required 3509000 4319000 3379000
cash surplus/(deficit) -839000 351000 642000
loan borrowed/(repaid) 839000

Related Solutions

I am very lost on this. If there is an Excel formula I could use, that...
I am very lost on this. If there is an Excel formula I could use, that would help tremendously. Thank you... Create confidence intervals related to the interval and ratio-level data you collected. 1. What is the best estimate of the population mean 2. Develop a 95% confidence interval for the population mean. Develop a 90% confidence interval for the population mean. Develop a 98% confidence interval for the population mean. 3. Interpret the confidence interval. Create an individual Excel...
Please do not use Excel to answer this. I need the computations as we are not...
Please do not use Excel to answer this. I need the computations as we are not allowed to use excel. Sequential Method Jasmine Company manufactures both pesticide and liquid fertilizer, with each product manufactured in separate departments. Three support departments support the production departments: Power, General Factory, and Purchasing. Budgeted data on the five departments are as follows: Support Departments Producing Departments Power General Factory Purchasing Pesticide Liquid Fertilizer Overhead $80,000 $312,000    $165,000    $78,500 $107,400 Square feet 1,500...
PLEASE USE FORMULA AND NOT EXCEL SO I CAN UNDERSTAND HOW TO USE FORMULA THE INITIAL...
PLEASE USE FORMULA AND NOT EXCEL SO I CAN UNDERSTAND HOW TO USE FORMULA THE INITIAL QUESTION: Gerald has taken out a loan of $100,000 today to start a business. He has agreed to repay the loan on the following terms: Repayments will be made on a monthly basis. The first repayment will be made exactly one month from today. The repayments for the first 5 years will cover interest only to help reduce the financial burden for Gerald’s business...
PLEASE SHOW WORKING AND FORMULA STEP BY STEP. DO NOT USE EXCEL Blitz Industries has a...
PLEASE SHOW WORKING AND FORMULA STEP BY STEP. DO NOT USE EXCEL Blitz Industries has a debt-equity ratio of .6. Its WACC is 9.2 percent, and its cost of debt is 6.5 percent. The corporate tax rate is 23 percent.    a. What is the company’s cost of equity capital? b. What is the company’s unlevered cost of equity capital? c-1. What would the cost of equity be if the debt-equity ratio were 2? c-2. What would the cost of...
Please compute the additional payment. also please include the formula used in excel. Thanks! Auto Loan...
Please compute the additional payment. also please include the formula used in excel. Thanks! Auto Loan Payment Calculator Inputs Auto Loan Amount $20,000 Annual Interest Rate 8.50% Term of Loan in Years 3 First Payment Date 2/1/18 Last Payment Date 1/1/21 Frequency of Payment Monthly Summary Number of Payments 36 Rate (per period) 0.708% Payment (per period) $631.35 Total Interest $2,728.63 Total Payments $22,728.63 No. Due Date Payment Due Additional Payment Interest Principal Balance $20,000.00 1 2/1/18 631.35 0.00 141.67...
**** DO NOT SOLVE IN EXCEL!!!!**** I need the actual formula & steps!! Consider the following...
**** DO NOT SOLVE IN EXCEL!!!!**** I need the actual formula & steps!! Consider the following hypotheses and sample​ data, and then complete parts a and b below using α=0.05 H0​:μ≤16 H1​:μ>16 2121      1818      1313      1717      2323      1919      1616      1919      1818      1616   I am getting the p-value wrong on all of these... I need to know the steps to solve p-value, please. I get all of the other stuff right, just not the p-value :(
Please explain your answers and use Excel to show the excel formula you used to get...
Please explain your answers and use Excel to show the excel formula you used to get your solution. 6. A manufacturing process produces connecting rods whose diameter is normally distributed with mean 1.495 cm and standard deviation .05 cm. In what range will the “middle 80%” of the diameters lie? What about the “middle 98%”?
PLEASE DO NO USE EXCEL, as we are not able to use excel on our test...
PLEASE DO NO USE EXCEL, as we are not able to use excel on our test XYZ company is considering a new machine that costs $300,000 and would reduce pre-tax manufacturing costs by $108,000 annually. XYZ would use the 3-year MACRS method to depreciate the machine, and management thinks the machine would have a value of $23,000 at the end of its 5-year operating life. The applicable depreciation rates are 33%, 45%, 15%, and 7%. The net operating working capital...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to...
I need this in an excel file with formulas please: 1) Use an Excel spreadsheet to solve problem #31 (the PUTZ, Inc. project) for Chapter 10 in the textbook. 2) Conduct a sensitivity analysis that focuses on the sales price by increasing the price by 10% above the best estimate, and then by decreasing the price by 10% below the best estimate. 3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case,...
-please answer this written out. Do not use excel I cannot access it. i need to...
-please answer this written out. Do not use excel I cannot access it. i need to know how to do it on paper- Deluxe River Cruises operates a fleet of river vessels. The fleet has two types of vessels: A type A vessel has 60 deluxe cabins and 160 standard cabins, whereas a type B vessel has 80 deluxe cabins and 120 standard cabins. Under a charter agreement with the Odyssey Travel Agency, Deluxe River Cruise sis to provide Odyssey...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT