Please show the formula's used.
Problem 1: Dodge Industries incurs the following costs during the 2019: Depreciation of machinery........... Direct labor....... Direct materials.. Executive salaries... Insurance... Rent on building Factory supplies. Vehicle lease cost. $15,000 200,000 60,000 100,000 2,000 50,000 20,000 5,000 The company sells one product for $10. During 2019, total sales revenue was $800,000. Dodge determined that only the direct production costs and factory supplies are to be classified as variable costs; all other costs are classified as fixed costs. Required: Using Excel, prepare a spreadsheet that addresses the following: a) Determine the unit contribution margin. b) Determine the contribution margin ratio. . c) The company is considering an expansion that will increase sales volume by 20%. The following changes would occur of the plan is implemented: An additional machine would add $5,000 of annual depreciation. Rent and insurance would each increase by 25%, as additional factory space would be needed. Due to bulk purchasing and economies of scale, the per unit cost of direct materials would decline by 20%. Nothing else would change. Prepare a contribution margin income statement, with two columns that compare the current situation without expansion and the situation if the expansion plan is implemented. d) The CEO is hoping this plan will increase pretax income by 20% so she can increase her own salary. Clearly state at the end of it whether the CEO's expectations will be achieved if the company expands. Will pretax income increase by 20%? Show the calculation. Problem 2: Todd Enterprises is preparing a cash budget for the second quarter of the coming year. The following data have been forecasted: April $150,000 107,000 May $157,500 112,400 Sales Merchandise purchases Operating expenses: Payroll Advertising Rent .... Depreciation End of April balances: Cash ...... Bank loan payable 13,600 5,400 2,500 7,500 14,280 5,700 2,500 7,500 30,000 26,000 Additional data: (1) Sales are 40% cash and 60% credit. The collection pattern for credit sales is 50% in the month following the sale and 50% in the month thereafter. Total sales in March were $125,000. (2) Purchases are all on credit, with 40% paid in the month of purchase and 60% paid in the following month. (3) Operating expenses are paid in the month they are incurred. (4) A minimum cash balance of $25,000 is required at the end of each month. (5) Loans are used to maintain the minimum cash balance. At the end of each month, interest of 1% per month is paid on the outstanding loan balance as of the beginning of the month. Repayments are made at the end of the month if the cash balance exceeds $25,000. Prepare the company's cash budget for May. Show the ending loan balance at May 31. Show all calculations
