Please post formulas too

Excel Project Budgetng Spreadsheet This assignment asks you to set up an Excel budget spreadsheet file that automatically prepares the master budget for a company, given sales projections and information on beginning balances, production requirements, desired endng nventories, etc. Information on developing the budgets appears in Chapter 8 of your text, and examples ofbudget worksheets appear in the schedules throughout the chapter Data Glamour Inc. produces and sells lady handbags. Below is information on its activities for the next few months months are as follows Estimated Sales (in units Sales projections for the coming April 25,000 Mav 55,000 June 65,000 Julv 60,000 Handbags Actual sales in February were 30,000 units; actual sales in March were 45,000 units. Each handbag's selling price is $65/unit. Desired ending inventory ofhandbags is 5,000 units plus 10% ofthe following month's projected sales. There are 7,500 units ofhandbags in nventory as of Apri 1 2. Estimated cash collections fom sales ofhandbags to customers are as follows: 40% collected n the month ofsale, 30% collected m the month following sale, 28% colected in the second month following sale, and 2% never collected 3 Three materials are used in the production ofhandbags: Rayon, Linen, and Leather. Materials requirements per unit ofhandbag are as follows: one unit of Rayon, 5 units ofLinen, and 3 units of Leather Costs of materials are Rayon- $1.6/unit, Linen $1.80/unit, Leather - S3.00/unit Desired ending inventory ofRayon is 20% ofthe following month's production need because t is sometimes in short supply Desired ending inventory ofLmen and Leather s 5% ofthe following month's production need because they are easy to get. Inventories ofmaterials as of April 1 are: 5,600 units of Rayon, 28,000 units of Linen, and 6,800 units ofLeather The company pays for materials purchases as follows: 60% in the month ofpurchase, 40% in the month following purchase. Total purchases ofmaterials for the month ofMarch were $350,000 4. 5.There are two types of direct labor costs to incur before a handbag is completed. Direct labor costs are paid in cash as incurred. Department Cuttng. Sewng. Direct labor hours per unit ofhandbag Cost per drect labor hour 0.12 hou 0.08 hour $20 $25 6 Total variable manufacturing overhead is estimated at $8/direct labor hour. Total fixed manufacturing overhead is estimated at $250,000/month, of which S80,000 is depreciation. Overhead costs are paid when ncurred. 7.Total variable selling and administrative costs are S1.50/unit ofhandbags sold. Total fixed selling and administrative costs are estimated at $350,000/month, ofwhich S180,000 is depreciation. Selling and administrative costs are paid as the costs are incurred. 8. The beginning balance ofcash account on April 1 is $88,000. The company desires to maintairn a minimum cash balance at the amount of $100,000 9. The company has an agreement with a local bank that would supply any amount of loan it needs to maintan the minimum cash balance. For simplicity, assume that interest is not compounded and that interest is paid each month but 1290 principal is paid at the year end. Annual inter est rate is 10 The company plans to purchase $5000 of equipment in April and $4,000 in May 11 Cash dividends is $1500 each month list total cash payments for each month Sheet 5 Include the folowing two budgets on the fifth worksheet, clearly labeled Manufacturing Overhead Budget: Prepare a manufacturing overhead budget for April and May. Show variable manufcturing overhead, fixed manufacturing overhead and total manufacturing overhead costs separately for each month Also, list total cash payments for each month Selling& Adminis trative Expense Budget: Prepare a selling and administrative expense budget for April and May. Show variable seling and admnistrative costs, fixed selling and admimistrative costs and total selling and administrative costs separately for each month List total cash payments for each month. Sheet 6: Inchude the following one budget on the sixth worksheet, clearly labeled: Cash Budget: Prepare a cash budget for April and May. List total amount of beginning cash balance, cash receipts, cash disbursements, cash excess or deficiency, borrowing at the beginning ofthe month, and ending cash balance separately Here are some key figures to help you: For Estimate #1, the cash payments for total direct materials purchase for April are S462416. For Estimate #2, the cash payments for total direct materials purchase for April are S589,136. Ifyour mumbers do not come out right, and you can't find your mistake, do not try to change the mumbers so the total comes out correctly. Each individual balance is graded, not the totalYou may be changingmumbers that are correct