Please follow the materials under Project and build a master budget on an Excel spreadsheet for requirement 1. Modify the master budget in another sheet for requirement 2. Prepare a 1-page report for requirement 3. You must use an Excel spreadsheet to complete this assignment. Your completed assignment must be submitted online. You must upload your Excel spreadsheet and an electronic copy of your report on Canvas through the inks available on Canvas Modules. Instructions for preparing Excel spreadsheets: 1. Develop your entire master budget in one worksheet; i.e., present your beginning balance sheet first, then present your sales budget below it and the next budget below, etc. All information should be presented at the top of the workbook or next to each budget. The master budget following the alternative scenario should be in another worksheet. 2. When you have finalized the master budget for the base case scenario, start working on Requirement 2. First, copy the master budget onto a new worksheet (i.e., a new tab). Then make the necessary changes on the copied worksheet. If you have fully programmed the first worksheet, your changes will be immediately reflected on the master budget. Label the worksheet tabs and the title of the worksheet. 3. Part of your mark will depend on how well you link the parts of your spreadsheets. 4. You must upload an electronic copy of your budget file (.xlsx) and report (.docx or .pdf) on Canvas 5. Do not round any numerical values; Instead, use appropriate display formats via Format Cells or shortcuts for display formats to indicate unit of each quantity or to determine the number of decimal places displayed. Report Instructions: Your report (i.e., Executive summary) should be one page (1-inch margins, 12 font size, single spacing, New Times Roman font). The report will be evaluated on content, writing, and presentation (grammar, spelling, wordiness, and persuasiveness). Your report should include only the executive summary. Any identification information (e.g., title, author) should be on the header of the document. The executive summary should reveal the problem, solution, and support for the solution. Presenting a context of the budget case without a solution or following support is not an executive summary. Your budgets will be regarded as an appendix of the report. You must refer to the content of your own excel spreadsheet when you need to discuss it in the report. Marking Rubric Possible Marks 1 Description Follow Instructions: 1) using one sheet for the base case scenario and the other for the alternative scenario, 2) title and the author identification information in the header and the executive summary in the body of one page, and 3) Following other instructions on the submission process and deadline Base Budget: 1) no redundant manual inputs of data, 2) consistency between the spreadsheet models and the written report, 3) numerical accuracy fully supported by calculations within an excel spreadsheet, 4) identifiable quarterly and annual line items and other components of each schedule, and 5) complete and accurate raw data from the case material Alternative Budget and the Estimation of Cost Behaviour 1) numerical accuracy, 2) use of Excel formulas to estimate the cost behaviour, 3) Discussion of problem, solution, and the logical support of the solution, and 4) writing clarity and style 5) Consistency between the Excel spreadsheet and the report 14 15 Total Mark 30 Sales Budget Particular Q2 Q3 Q4 Q1 Sales Unit 4500 Units 3500 Units 8500 Units 2500 Units Selling Price $ 45.00 $ 45.00 $ 45.00 $ 45.00 Sales Revenue $ 202,500.00 $ 157,500.00 $ 382,500.00 $ 112,500.00 Cash Collection Budget Particular Q2 Q3 Q4 Q1 Sales Revenue $ 202,500.00 $ 157,500.00 $ 382,500.00 $ 112,500.00 Collection In Six Month $ 202,500.00 $ 157,500.00 Production Budget Particular Q2 Q3 Q4 Q1 Sales 4500 Units 3500 Units 8500 Units 2500 Units 4500 Add: Closing Inventory 420 Units 1020 Units 300 Units 540 Units 420 4920 Units 4520 Units 8800 Units 3040 Units 4920 Units Less: Opening Inventory 540 Units 420 Units 1020 Units 300 Units 540 Production(Units) 4380 Units 4100 Units 7780 Units 2740 Units 4380 Units Direct Materials Purchase Budget Particular Q2 Q3 Q4 Q1 Production(Units) 4380 4100 7780 Production(Units) 4380 Units 4100 Units 7780 Units 2740 Units PCB Per Unit 1 Unit 1 Unit 1 Unit 1 Unit 3 Gms 3 Gms 3 Gms TPU 95A Per Unit Total Required Total Required 4380 Units 4100 Units 7780 Units 2740 Units 13140 Gms 12300 Gms 23340 Gms 410 Units 778 Units 274 Units 438 Units Add: Closing Inventory Add: Closing Inventory 1230 Gms 2334 Gms 822 Gms 4790 Units 4878 Units 8054 Units 3178 Units 14370 Gms 14634 Gms 24162 Gms 438 Units 410 Units 778 Units 274 Units Less: Opening Inventory Less: Opening Inventory 1314 Gms 1230 Gms 2334 Gms 4352 Units 4468 Units 7276 Units 2904 Units 13056 Gms 13404 Gms 21828 Gms Rate Rate $ $ 12.00 $ 12.00 $ 12.00 $ 12.00 $ 0.20 $ 0.20 0.20 O Purchase Cost Purchase Cost $ $ $ $ 52,224.00 53,616.00 87,312.00 34,848.00 in oc $ $ $ 2,611.20 2,680.80 4,365.60 Payment Schedule Particular Q2 Q3 Q4 Q1 Total Purchase $ 54,835.20 $ 56,296.80 $ 91,677.60 $ 36,590.40 Cash $ 32,901.12 $ 33,778.08 $ 55,006.56 $ 21,954.24 40% in Next Quarter $ 74,300.00 $ 21,934.08 $ 22,518.72 $ 36,671.04 Total payment $ 107,201.12 $ 55,712.16 $ 77,525.28 $ 58,625.28 Labour Budget Particular Q2 Q3 Q4 Q1 Production(Units) 4380 4100 7780 2740 Hour Per Unit 1 hour 1 hour 1 hour 1 hour Total Hour 2190 hour 2050 hour 3890 hour 1370 hour Hour Rate $ 10.00 $ 10.00 $ 10.00 $ 10.00 Tota labour Cost $ 21,900.00 $ 20,500.00 $ 38,900.00 $ 13,700.00 Please follow the materials under Project and build a master budget on an Excel spreadsheet for requirement 1. Modify the master budget in another sheet for requirement 2. Prepare a 1-page report for requirement 3. You must use an Excel spreadsheet to complete this assignment. Your completed assignment must be submitted online. You must upload your Excel spreadsheet and an electronic copy of your report on Canvas through the inks available on Canvas Modules. Instructions for preparing Excel spreadsheets: 1. Develop your entire master budget in one worksheet; i.e., present your beginning balance sheet first, then present your sales budget below it and the next budget below, etc. All information should be presented at the top of the workbook or next to each budget. The master budget following the alternative scenario should be in another worksheet. 2. When you have finalized the master budget for the base case scenario, start working on Requirement 2. First, copy the master budget onto a new worksheet (i.e., a new tab). Then make the necessary changes on the copied worksheet. If you have fully programmed the first worksheet, your changes will be immediately reflected on the master budget. Label the worksheet tabs and the title of the worksheet. 3. Part of your mark will depend on how well you link the parts of your spreadsheets. 4. You must upload an electronic copy of your budget file (.xlsx) and report (.docx or .pdf) on Canvas 5. Do not round any numerical values; Instead, use appropriate display formats via Format Cells or shortcuts for display formats to indicate unit of each quantity or to determine the number of decimal places displayed. Report Instructions: Your report (i.e., Executive summary) should be one page (1-inch margins, 12 font size, single spacing, New Times Roman font). The report will be evaluated on content, writing, and presentation (grammar, spelling, wordiness, and persuasiveness). Your report should include only the executive summary. Any identification information (e.g., title, author) should be on the header of the document. The executive summary should reveal the problem, solution, and support for the solution. Presenting a context of the budget case without a solution or following support is not an executive summary. Your budgets will be regarded as an appendix of the report. You must refer to the content of your own excel spreadsheet when you need to discuss it in the report. Marking Rubric Possible Marks 1 Description Follow Instructions: 1) using one sheet for the base case scenario and the other for the alternative scenario, 2) title and the author identification information in the header and the executive summary in the body of one page, and 3) Following other instructions on the submission process and deadline Base Budget: 1) no redundant manual inputs of data, 2) consistency between the spreadsheet models and the written report, 3) numerical accuracy fully supported by calculations within an excel spreadsheet, 4) identifiable quarterly and annual line items and other components of each schedule, and 5) complete and accurate raw data from the case material Alternative Budget and the Estimation of Cost Behaviour 1) numerical accuracy, 2) use of Excel formulas to estimate the cost behaviour, 3) Discussion of problem, solution, and the logical support of the solution, and 4) writing clarity and style 5) Consistency between the Excel spreadsheet and the report 14 15 Total Mark 30 Sales Budget Particular Q2 Q3 Q4 Q1 Sales Unit 4500 Units 3500 Units 8500 Units 2500 Units Selling Price $ 45.00 $ 45.00 $ 45.00 $ 45.00 Sales Revenue $ 202,500.00 $ 157,500.00 $ 382,500.00 $ 112,500.00 Cash Collection Budget Particular Q2 Q3 Q4 Q1 Sales Revenue $ 202,500.00 $ 157,500.00 $ 382,500.00 $ 112,500.00 Collection In Six Month $ 202,500.00 $ 157,500.00 Production Budget Particular Q2 Q3 Q4 Q1 Sales 4500 Units 3500 Units 8500 Units 2500 Units 4500 Add: Closing Inventory 420 Units 1020 Units 300 Units 540 Units 420 4920 Units 4520 Units 8800 Units 3040 Units 4920 Units Less: Opening Inventory 540 Units 420 Units 1020 Units 300 Units 540 Production(Units) 4380 Units 4100 Units 7780 Units 2740 Units 4380 Units Direct Materials Purchase Budget Particular Q2 Q3 Q4 Q1 Production(Units) 4380 4100 7780 Production(Units) 4380 Units 4100 Units 7780 Units 2740 Units PCB Per Unit 1 Unit 1 Unit 1 Unit 1 Unit 3 Gms 3 Gms 3 Gms TPU 95A Per Unit Total Required Total Required 4380 Units 4100 Units 7780 Units 2740 Units 13140 Gms 12300 Gms 23340 Gms 410 Units 778 Units 274 Units 438 Units Add: Closing Inventory Add: Closing Inventory 1230 Gms 2334 Gms 822 Gms 4790 Units 4878 Units 8054 Units 3178 Units 14370 Gms 14634 Gms 24162 Gms 438 Units 410 Units 778 Units 274 Units Less: Opening Inventory Less: Opening Inventory 1314 Gms 1230 Gms 2334 Gms 4352 Units 4468 Units 7276 Units 2904 Units 13056 Gms 13404 Gms 21828 Gms Rate Rate $ $ 12.00 $ 12.00 $ 12.00 $ 12.00 $ 0.20 $ 0.20 0.20 O Purchase Cost Purchase Cost $ $ $ $ 52,224.00 53,616.00 87,312.00 34,848.00 in oc $ $ $ 2,611.20 2,680.80 4,365.60 Payment Schedule Particular Q2 Q3 Q4 Q1 Total Purchase $ 54,835.20 $ 56,296.80 $ 91,677.60 $ 36,590.40 Cash $ 32,901.12 $ 33,778.08 $ 55,006.56 $ 21,954.24 40% in Next Quarter $ 74,300.00 $ 21,934.08 $ 22,518.72 $ 36,671.04 Total payment $ 107,201.12 $ 55,712.16 $ 77,525.28 $ 58,625.28 Labour Budget Particular Q2 Q3 Q4 Q1 Production(Units) 4380 4100 7780 2740 Hour Per Unit 1 hour 1 hour 1 hour 1 hour Total Hour 2190 hour 2050 hour 3890 hour 1370 hour Hour Rate $ 10.00 $ 10.00 $ 10.00 $ 10.00 Tota labour Cost $ 21,900.00 $ 20,500.00 $ 38,900.00 $ 13,700.00