Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Kindly requesting a creation of excel friendly Monthly Master Budget for year ended 30 Sep 2024 with the following 7 schedules. Thank you very much

image text in transcribedimage text in transcribedimage text in transcribed

Kindly requesting a creation of excel friendly Monthly Master Budget for year ended 30 Sep 2024 with the following 7 schedules.

Thank you very much for your time in advance :)

image text in transcribed

Kindly requesting a creation of excel friendly Monthly Master Budget for year ended 30 Sep 2024 with the following 7 schedule. Thank you very much for your time in advance :)

- Fast Ltd. is a company that manufactures and sells a single product called Furious. For planning and control purposes they utilize a monthly master budget, which is developed in advance of the budget year. Their fiscal year end is September 30. The sales forecast consisted of these few lines: - For the year ended September 30, 2023: 540,000 units at $21.00 each* - For the year ended September 30, 2024: 560,000 units at $22.00 each - For the year ended September 30, 2025: 580,000 units at $22.50 each *Sales for the year ended September 30, 2023 are based on actual sales to date and budgeted sales for the duration of the year. Your investigations of the company's records have revealed the following information: - 1. Peak months for sales correspond with gift-giving holidays. History shows that January, March, May and June are the slowest months with only 6% of sales for each month. Sales pick up over the summer with July, August and September each contributing 8% to the total. Valentine's Day in February and Easter in April account for 9% of total sales. As Christmas shopping picks up momentum, winter sales start at 10% in October, move to 11% in November and then peak at 13% in December. This pattern of sales is not expected to change in the next two years. - 2. Sales are on a cash and credit basis, with 40% collected during the month of the sale, 45% the following month, and 13% the month thereafter, with 2% of total sales considered uncollectible (bad debt expense). 3.From previous experience, management has determined that an ending inventory equal to 30% of the next month's sales is required to fit the buyer's demands. - 4. Because sales are seasonal, Fast must rent an additional warehouse from October to December to house the additional inventory on hand. The only related cost is a flat $15,000 per month, payable at the beginning of the month. - 5.There is only one type of raw material used in the production of Furious: Furious Acrylic (HA) is a very compact material that is purchased in powder form. Each Furious requires 0.5 kilogram of HA, at a cost of $15.00 per kilogram. The supplier of HA tends to be somewhat erratic so Fast finds it necessary to maintain an inventory balance equal to 40% of the following month's production needs as a precaution against stock-outs. 6. Fast pays for 40% of a month's purchases in the month of purchase, 35% in the following month and the remaining 25% two months after the month of purchase. There is no early payment discount. 7. Beginning accounts payable will consist of $314,712 arising from the following estimated direct material purchases for August and September of 2023: HA purchases in August, 2023$335,520 HA purchases in September, 2023 $384,720 8. Fast's manufacturing process is highly automated, so their direct labour cost is low. Employees are paid at an average rate of $20.00 per hour. This rate already includes the employer's portion of employee benefits. All payroll costs are paid in the period in which they are incurred. Each unit spends a total of 6 minutes in production. However, due to the fluctuations in actual sales, the company malntains a workforce of 25 full-time employees who are guaranteed a minimum of 160 hours each. During months in which production is low, these workers perform other tasks such as equipment maintenance. In months when additional workers are needed, Fast hires temporary workers at $20 per hour. For simplicity, include all labour costs in the labour budget. 9. Variable manufacturing overhead is allocated based on units produced. The unit variable overhead manufacturing rate is $4.00 10. The fixed manufacturing overhead costs for the entire year is estimated to be $960,000, including depreciation of manufacturing equipment, $264,000. Fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Fast uses the straight-line method of depreciation. 11. Selling and administrative expenses are largely fixed, and are estimated to be $912,000 for the year. These costs are paid in the month in which they occur, with the exception of the only non-cash item: a monthly depreciation of office equipment in the amount of $4,500. Starting in fiscal 2024, Fast intends to pay salespeople a commission of $0.75 per unit sold. This cost is not included in the estimate above, nor are bad debt expense (see point 2) or warehouse rental (see point 4). 12.Sales in August and September 2023 are expected to be $907,200 each month. Based on the above collection pattern this will result in Accounts Receivable of $644,112 at September 30,2023 , which will be collected in October and November 2023. 13.During the fiscal year ended September 30, 2024 Fast will be required to make monthly income tax installment payments of $50,000. Outstanding income taxes from the year ended September 30, 2023 must be paid in December 2023. Income taxes for the year ended September 30, 2024, in excess of installment payments, will be paid in December 2024. 14.Prior to the busy season, Fast is planning to purchase new manufacturing equipment for which they will need to pay cash. The bid that was accepted totalled $1,000,000, of which $300,000 will be paid in October 2023 , and the remaining balance in 7 equal monthly instalments of $100,000 beginning in November 2023. Manufacturing overhead costs above already include the depreciation on this equipment. 15.An arrangement has been made with the local bank that if Fast maintains a minimum balance of $40,000 in their bank account, they will be given a line of credit at a preferred rate of 7.2% per annum (l.e., 0.6% per month). All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000 and interest must be paid at the end of each month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month (I.e., any borrowing for a month must occur at the beginning of that month). Fast has a loan balance of $226,000 at September 30, 2023. 15-2.Fast has a policy of paying dividends at the end of each month. The President tells you that the board of directors plans to continue their policy of declaring dividends of $25,000 per month. Required: Prepare a monthly master budget for Fast for the year ended September 30, 2024, including the following schedules: 1. Sales Budget \& Schedule of CashReceipts 2. ProductionBudget 3. Direct Materials Budget \& Schedule of Cash Disbursements 4. Direct Labour Budget 5. ManufacturingOverheadBudget 6. Selling and Administrative Expense Budget

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

IT Auditing An Adaptive Process

Authors: Robert E. Davis

1st Edition

0557220513, 978-0557220519

More Books

Students also viewed these Accounting questions