Macy's Inc. is a leading multinational fragrance, skincare, and cosmetics brands company. It is a legitimate perfumery business that upholds a high aesthetic value, conces excellence, and exudes exclusivity, and provides a product range of natural, alcohol-free, and skin-friendly perfumes, In addition to using organic lavender oil as its main raw material, other ingredients are used, such as flowers, fruits, plant materials, musk, and other natural ingredients. Recently, you have been hired by Macy's Inc. as a new management accountant to assist with creating the budget for the company's fragrance product. In the past, the company hasn't done much in the way of budgeting and at specific times of the year, there's been a cash shortfall. In order to prepare a Master Budget for the second quarter, you have been assembled data mentioned the following balances below as of March 31: (b) Actual sales units for the first quarter and budgeted sales units for the following four months are as follows: - Fach unit is being sold for $30. All sales are on credit. Macy's Inc, collects 60% of sales in the month of the sale and 30% in the month after the sale and remaining in the second following month's sales. Bad debts have been negligible. According to management, Macy's Inc.'s ending inventory should equal 20% of the anticipated unit sales for the upcoming month. It is assumed that 24000 units were available inventory as of March 31 . - Eight grams of organic lavender oil are required per unit of product. Management wants materials on hand at the end of each month equal to 20% of the following month's production. On March 1 31, 42000 grams of material in hand. Macy's Inc pays $0.60 as material cost per gram. The company pays for 40% of its merchandise purchases in the month of the purchase and the remaining 60% in the month that follows the purchase. First quarter accounts payable are fully paid in the month of April. Macy's requires an hour of direct labor for every unit of a product and the company pays $14 per hour. - At Macy's, manufacturing overhead is applied to units of product on the basis of direct labor hours. The variable manufacturing overhead rate is $5 per direct labor hour. Fixed manufacturing overhead is $50,000 per month, which includes $20,000 of noncash costs (primarily depreciation of plant assets). - The selling and administrative expense budget is divided into variable and fixed components. The variable selling and administrative expenses are $0.90 per unit sold. Fixed selling and administrative expenses are $70,000 per month. The fixed selling and administrative expenses include 510,000 in costs - primarily depreciation - that are not cash outflows of the current month. - Assume that as of April 1, Macy's has a balance of $380,000 in cash and the company need to maintain minimum $500,000 balance at month's end. In case company has shortage of cash, can borrow from the bank and current bank interest rate is 10% per annum. It is assumed that the beginning day of the month would be the day for borrowing, and the last day of the month would be the day for paying back loans. A cash dividend of 532,000 is distributed in April by Macy's Inc and new equipment purchases worth $140.660, paid cash in the month of June. Required: Using the data above, prepare the following budgets and statements for the second quarter: 1. Budget launch page (Content Page) 2. Beginning balance budget and Budgeting assumptions 3. Sales Budget 4. Schedule of expected cash collections. 5. Production Budget 6. Direct Materials Budget 7. Expected Cash Disbursement for Materials 8. Direct Labor Budget 9. Manufacturing Overhead Budget 10. Selling And Administrative Expense Budget 11. Cash Budget 12. Prepare a budgeted income statement for the second quarter ending June 30 13. Prepare a budgeted balance sheet as of June 30 Instructions: - Use Microsoft Excel to prepare the master budget. - Every requirement above needs to be prepared in a separate Microsoft Excel sheet ( 13 sheets in total) - Students should write the names of the group members and their student ID on each sheet (Header). - Include budget title and Hyperlink with each sheet. - Use Excel function/formula in each budget (link with budget assumption sheet)