Project Suppose you are allocated as a general manager at Big Red Bicycle Pty Ltd. As per the company policy, you are responsible for monitoring and recording expenditure each month. The finance department has provided you with a Master Budget for year 2018/2019 Master budget with profit projections Big Red Bicycle Pty Ltd Master Budget/Financial plan FY 2018/2019 101 102 103 104 REVENUE Commissions (2.5477,500 17,500 25,000 sales) 17,500 17,500 Direct wages fixed 200,000 50,000 50,000 50,000 50,000 Sales 3.100.000 200.000 1.000.000 700,000 700.000 Cost of Goods Sold 400.000 100.000 100.000 100.000 Gross profit 2.422.500 532,500 325,000 532,500 532.500 EXPENSES General Administrative Expenses Accounting fees 20,000 5,000 15.000 15.000 15.000 Legal fees 15,000 1,250 1,250 1,250 1,250 Bank charges 1600 150 150 150 150 Orice supplies 15,000 1,250 1,250 1,250 1,250 Postage & printing 100 100 100 100 pues & subscriptions 1500 125 125 125 125 Telephone 10,000 2.500 2.500 2.500 2.500 Repair & maintenance 50.000 45.000 1.000 1.000 1.000 Payroll tax 25,000 16,250 6.250 6.250 6.250 Marketing Expenses Advertising 200,000 50.000 50.000 50.000 50.000 Employment Expenses Superannuation 47,500 11.875 111,875 11,875 111.875 Wages & salaries 500,000 125,000 125,000 125,000 125,000 Staff amenities 120,000 5,000 5.000 5.000 .000 Occupancy Costs Electricity 40,000 10,000 10,000 10,000 10,000 100.000 125.000 5.000 5.000 125.000 100,000 25000 .000 125,000 125.000 200,000 50.000 50.000 50.000 50.000 Petty cash 11.000 000 000 200 200 Water 130.000 2.500 b.500 17.500 7.500 150,000 12.500 12.500 12.500 12.500 TOTAL EXPENSES 1.405,000 383,800 340,800 340,700 339,700 NET PROFIT (BEFORE 1,017,500 148,700|484.200 191,800 192,800 INTEREST & TAX) Income Tax Expense 305,250 $4,610 145,260 30 Net) 57,540 57,840 NET PROFIT AFTER 712,250 104,090 338,940 134,260134,960 ITAX Sales cost centre expense budget Sales Centre Asales Centre B Sales Centre Commissions $30,000 $15,000 $15,000 Wages $150,000 $75,000 $75,000 Telephone 54,500 $2,250 $2,250 Office supplies $1,500 $750 $750 Note that the company deems a standard variance in budgets of up to 5% acceptable. The company faced the following issues during the first quarter of the financial budget: Poor sales due to an economic downturn. The sales have declined by 20% for the first quarter. Cost of goods sold remained the same. The government has changed the minimum wages by 3%. This has led to an increase in wage expense. (All the wages after June 2018 are to be calculated 3% extra). To tackle these issues, the board of directors of the company arranged a meeting and have decided to implement the following negotiations: Increase advertisement budget by 15% and invest this amount in promoting the company products on social media. They think that this will increase the sales of the company and they will be able to achieve the budgeted targets. The advertisement expense will occur from the second quarter onwards. Reduce repair and maintenance cost by 10%. However, the repair and maintenance cost for the first quarter cannot be changed. Task: Based on the above-given negotiations, the board of directors have given you the task to revise the budget considering the negotiations (sales to remain the same). The management wants to know what consequences they will face if they implement the budget considering these negotiations. You are required to: Part A: Follow template 1 and complete the following using MS Excel: Revise the budget and document the outcomes that are to be achieved after each quarter. Check expenditures for each quarter and provide appropriate responses to control costs. (Use appropriate processes to monitor actual expenditure and to control costs) Identify the cost variations and expenditure overruns for each quarter. Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Template 1: Revised Master budget with profit projections Big Red Bicycle Pty Ltd Master Budget FY 2018/2019 Var and exp FYQ: Overrun 92 Var and exp V ar and exp overrun 93 overn 4 Var and exp overrun REVENUE Commissions (2.5% sales) Direct wages foed Cost of Goods Sold Gross profit EXPENSES General Administrative Expenses Accounting fees Legal fees Bank charges Office supplies Postage & printing Dues Subscriptions Dues & subscriptions Telephone Repair & maintenance Payroll tax Marketing Expenses Advertising Employment Expenses Superannuation Wages & salaries Staff amenities Occupancy Costs Electricity Insurance Rates Rent Petty cash Water Waste removal TOTAL EXPENSES NET PROFIT (BEFORE INTEREST & TAX) Income Tax Expense (25% Net) NET PROFIT AFTER TAX Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Part B: Clarify with the Finance Manager (your trainer/assessor), if the budget/financial plan is: o achievable? o accurate? o comprehensive? NET PROFIT AFTER TAX Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Part B: Clarify with the Finance Manager (your trainer/assessor), if the budget/financial plan is: o achievable? o accurate? o comprehensive? (Document in template 2) INTEREST & TAX) Income Tax Expense (25% Net) NET PROFIT AFTER TAX Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Part B: Clarify with the Finance Manager (your trainer/assessor), if the budget/financial plan is: o achievable? o accurate? o comprehensive? (Document in template 2) Template 2: If the budget plan isi Yes/NoIT No, provide a reason achievable? Part C: Based on the revised budget: Identify the risks based on the negotiations and variations in the revised master budget (financial plan/budget). /*Negotiations are given in the case study*/ Complete the Contingency plan template. (template 3) Template 3: Contingency Plan Temnlate Contingency Plan Template Contingency plan Company name: Person Developing the plant Position: Risk identified: Strategies/activities to minimise the risky wheney whom Part D: Assume that for the first two-quarters of the financial budget, there were no further variations in the implementation in the budget. However, there are two customers that have bought products from the company, and their payments are still due. As per the company policy and procedures if the debts are not paid for a period of 60 days, then they are to be written as bad debts. . On 21 October 2018, Damsons Itd purchased bicycles and have not made any payments. The total amount on the invoice due is $4,800. . On 29 October 2018, Ping Itd purchased spare parts of the bicycles for $3,200 and have not cleared the invoice. Based on the above-given information, prepare the following financial statements using MS Excel. Ageing summaries, Profit and loss statements When preparing the financial statements using MS Based on the above-given information, prepare the following financial statements using MS Excel. Ageing summaries, Profit and loss statements When preparing the financial statements using MS Excel, you must follow the templates provided below. Ageing summary template: Customer Name Total A/R 0-30 Days|31-60 Days|61-90 Day|90+ Dave Damson Rd Pingid Profit and loss template: Quarter 1 Quarter 2 Cash flow REVENUE Commissions (2.5% sales) Direct wages fixed Cost of Goods Sold Gross pront EXPENSES General & Administrative Expenses Accounting fees Legal fees Bank charges IT e suales Postage & printing Dues & subscriptions Telephone Repair & maintenance Payroll tax Marketing Expenses Advertising Employment Expenses IIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIII Wages & salaries Staff amenities Occupancy Costs Rates Petty cash Waste removal Rad debt expense TOTAL EXPENSES NET PROFIT BEFORE INTEREST TAX) Project Suppose you are allocated as a general manager at Big Red Bicycle Pty Ltd. As per the company policy, you are responsible for monitoring and recording expenditure each month. The finance department has provided you with a Master Budget for year 2018/2019 Master budget with profit projections Big Red Bicycle Pty Ltd Master Budget/Financial plan FY 2018/2019 101 102 103 104 REVENUE Commissions (2.5477,500 17,500 25,000 sales) 17,500 17,500 Direct wages fixed 200,000 50,000 50,000 50,000 50,000 Sales 3.100.000 200.000 1.000.000 700,000 700.000 Cost of Goods Sold 400.000 100.000 100.000 100.000 Gross profit 2.422.500 532,500 325,000 532,500 532.500 EXPENSES General Administrative Expenses Accounting fees 20,000 5,000 15.000 15.000 15.000 Legal fees 15,000 1,250 1,250 1,250 1,250 Bank charges 1600 150 150 150 150 Orice supplies 15,000 1,250 1,250 1,250 1,250 Postage & printing 100 100 100 100 pues & subscriptions 1500 125 125 125 125 Telephone 10,000 2.500 2.500 2.500 2.500 Repair & maintenance 50.000 45.000 1.000 1.000 1.000 Payroll tax 25,000 16,250 6.250 6.250 6.250 Marketing Expenses Advertising 200,000 50.000 50.000 50.000 50.000 Employment Expenses Superannuation 47,500 11.875 111,875 11,875 111.875 Wages & salaries 500,000 125,000 125,000 125,000 125,000 Staff amenities 120,000 5,000 5.000 5.000 .000 Occupancy Costs Electricity 40,000 10,000 10,000 10,000 10,000 100.000 125.000 5.000 5.000 125.000 100,000 25000 .000 125,000 125.000 200,000 50.000 50.000 50.000 50.000 Petty cash 11.000 000 000 200 200 Water 130.000 2.500 b.500 17.500 7.500 150,000 12.500 12.500 12.500 12.500 TOTAL EXPENSES 1.405,000 383,800 340,800 340,700 339,700 NET PROFIT (BEFORE 1,017,500 148,700|484.200 191,800 192,800 INTEREST & TAX) Income Tax Expense 305,250 $4,610 145,260 30 Net) 57,540 57,840 NET PROFIT AFTER 712,250 104,090 338,940 134,260134,960 ITAX Sales cost centre expense budget Sales Centre Asales Centre B Sales Centre Commissions $30,000 $15,000 $15,000 Wages $150,000 $75,000 $75,000 Telephone 54,500 $2,250 $2,250 Office supplies $1,500 $750 $750 Note that the company deems a standard variance in budgets of up to 5% acceptable. The company faced the following issues during the first quarter of the financial budget: Poor sales due to an economic downturn. The sales have declined by 20% for the first quarter. Cost of goods sold remained the same. The government has changed the minimum wages by 3%. This has led to an increase in wage expense. (All the wages after June 2018 are to be calculated 3% extra). To tackle these issues, the board of directors of the company arranged a meeting and have decided to implement the following negotiations: Increase advertisement budget by 15% and invest this amount in promoting the company products on social media. They think that this will increase the sales of the company and they will be able to achieve the budgeted targets. The advertisement expense will occur from the second quarter onwards. Reduce repair and maintenance cost by 10%. However, the repair and maintenance cost for the first quarter cannot be changed. Task: Based on the above-given negotiations, the board of directors have given you the task to revise the budget considering the negotiations (sales to remain the same). The management wants to know what consequences they will face if they implement the budget considering these negotiations. You are required to: Part A: Follow template 1 and complete the following using MS Excel: Revise the budget and document the outcomes that are to be achieved after each quarter. Check expenditures for each quarter and provide appropriate responses to control costs. (Use appropriate processes to monitor actual expenditure and to control costs) Identify the cost variations and expenditure overruns for each quarter. Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Template 1: Revised Master budget with profit projections Big Red Bicycle Pty Ltd Master Budget FY 2018/2019 Var and exp FYQ: Overrun 92 Var and exp V ar and exp overrun 93 overn 4 Var and exp overrun REVENUE Commissions (2.5% sales) Direct wages foed Cost of Goods Sold Gross profit EXPENSES General Administrative Expenses Accounting fees Legal fees Bank charges Office supplies Postage & printing Dues Subscriptions Dues & subscriptions Telephone Repair & maintenance Payroll tax Marketing Expenses Advertising Employment Expenses Superannuation Wages & salaries Staff amenities Occupancy Costs Electricity Insurance Rates Rent Petty cash Water Waste removal TOTAL EXPENSES NET PROFIT (BEFORE INTEREST & TAX) Income Tax Expense (25% Net) NET PROFIT AFTER TAX Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Part B: Clarify with the Finance Manager (your trainer/assessor), if the budget/financial plan is: o achievable? o accurate? o comprehensive? NET PROFIT AFTER TAX Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Part B: Clarify with the Finance Manager (your trainer/assessor), if the budget/financial plan is: o achievable? o accurate? o comprehensive? (Document in template 2) INTEREST & TAX) Income Tax Expense (25% Net) NET PROFIT AFTER TAX Unit Code: Version Number: 10 (Review date: 01/06/2022) FNSORG501 Page 39 of 70 Part B: Clarify with the Finance Manager (your trainer/assessor), if the budget/financial plan is: o achievable? o accurate? o comprehensive? (Document in template 2) Template 2: If the budget plan isi Yes/NoIT No, provide a reason achievable? Part C: Based on the revised budget: Identify the risks based on the negotiations and variations in the revised master budget (financial plan/budget). /*Negotiations are given in the case study*/ Complete the Contingency plan template. (template 3) Template 3: Contingency Plan Temnlate Contingency Plan Template Contingency plan Company name: Person Developing the plant Position: Risk identified: Strategies/activities to minimise the risky wheney whom Part D: Assume that for the first two-quarters of the financial budget, there were no further variations in the implementation in the budget. However, there are two customers that have bought products from the company, and their payments are still due. As per the company policy and procedures if the debts are not paid for a period of 60 days, then they are to be written as bad debts. . On 21 October 2018, Damsons Itd purchased bicycles and have not made any payments. The total amount on the invoice due is $4,800. . On 29 October 2018, Ping Itd purchased spare parts of the bicycles for $3,200 and have not cleared the invoice. Based on the above-given information, prepare the following financial statements using MS Excel. Ageing summaries, Profit and loss statements When preparing the financial statements using MS Based on the above-given information, prepare the following financial statements using MS Excel. Ageing summaries, Profit and loss statements When preparing the financial statements using MS Excel, you must follow the templates provided below. Ageing summary template: Customer Name Total A/R 0-30 Days|31-60 Days|61-90 Day|90+ Dave Damson Rd Pingid Profit and loss template: Quarter 1 Quarter 2 Cash flow REVENUE Commissions (2.5% sales) Direct wages fixed Cost of Goods Sold Gross pront EXPENSES General & Administrative Expenses Accounting fees Legal fees Bank charges IT e suales Postage & printing Dues & subscriptions Telephone Repair & maintenance Payroll tax Marketing Expenses Advertising Employment Expenses IIIIIIIIIIIIIIIIIIIIIIIIIII IIIIIIIIIIIIIIIIIIIIIIIII Wages & salaries Staff amenities Occupancy Costs Rates Petty cash Waste removal Rad debt expense TOTAL EXPENSES NET PROFIT BEFORE INTEREST TAX)