Reference no: EM132405941
COMM 210 Introduction to Management Accounting Assignment - Budget Project
Edwards School of Business - University of Saskatchewan, Canada
Instructions - Aqua Wave Manufacturing (AWM) Ltd. has hired your team (group) of professional consultants to assist them in developing a Master Budget, and then to use the information from the Budget to prepare a report and advise them regarding a number of possible alternatives (including their ideas and any ideas, over-and-above theirs, that you may have).
You are required to use MS Word and Excel to complete this assignment. The Excel worksheets must contain a data entry section and at least four (possibly more dependent upon your analysis and recommendation(s)) subsequent worksheets. The worksheets should draw from your data entry section(s) using cell referencing (relative and absolute), formulas, functions, and linking. The worksheet schedules themselves should not contain any hard numbers, other than your borrowing and repayment cells located in your cash budget, which may be hard numbers.
REQUIRED -
1. Begin by preparing a Base Case Master Budget (and label this Excel worksheet Base Case) for AWM Manufacturing Ltd. for items a - j, for each quarter of 2019, and for the year in total. The following component budgets must be included:
a. Beginning balance sheet (classified).
b. Sales budget
c. Schedule of receipts
d. Production budget
e. Direct materials purchases budget (for all four Raw Materials (Polyurethane, Styrofoam, Paint, & Fiberglass Gel Coat))
f. Schedule of disbursements for materials (for all four Raw Materials (Polyurethane, Styrofoam, Paint, & Fiberglass Gel Coat))
g. Direct labour budget
h. Overhead budget (Variable & Fixed) (be sure to adjust for cash disbursements).
i. Selling and administrative budget (be sure to adjust for cash disbursements).
j. Cash budget
Prepare the following, h - k, (in good form) for the year (in total), 2019, (these do not need to be done quarterly, rather only for the year ended December 31, 2019).
h. Cost of goods manufactured budget
i. Cost of goods sold budget
j. Pro forma income statement (using absorption costing)
k. Pro forma classified balance sheet
2. The owners/managers of AWM Manufacturing are concerned about their cash flow, budgeting, profitability and liquidity (i.e., cash flow). They would like to plan for their reliance on the use of short-term debt while improving profitability. In order to improve cash flow and profitability, the owners/managers have asked you to assess and compare the base case (as described above) to each of the following alternatives/changes in isolation, and combined:
i. An increase in the sales price from $600 to $640 per unit starting January 1, 2019, coupled with a tightening of credit terms to ensure a quicker collection of accounts receivable. They are considering collecting their Accounts receivable as follows: 70% in the quarter of the sale, and 26% in the following quarter. The estimate of bad debts is still 4%. They also believe that the impact of increasing the sales price combined with the tightening of credit terms would result in selling 3% fewer units each quarter into the foreseeable future. Label this Excel worksheet "Price Increase & Cr Tightening."
ii. In an effort to reduce their inventory levels and related carrying costs the owner/managers are considering moving to a quasi Just-in-time (JIT) inventory system. Essentially AWM is considering changing their ending inventory policy as follows: ending raw material inventories (for polyurethane, Styrofoam, Paint, and Fiberglass Gel Coat) would be 3% of the estimated next quarter's production requirements and ending finished goods inventories would be 4% of next quarter's estimated sales. Label this Excel worksheet "Inventory Tightening."
As a result of reducing their raw material and finished goods inventory levels they believe that their Fixed overhead costs will be reduced, for each the Moulding and the Painting/Finishing Departments, from the amounts listed in items #4 & #6 above respectively (in the Project description section), to the following:
Fixed overhead - Moulding (applied on the basis of # of labour hrs)
Supervisor's salaries 170,000
Property tax 28,000
Insurance 12,000
Maintenance 38,000
Utilities 48,000
Engineering time 41,200
Depreciation 90,000
Total 427,200
Fixed overhead - Painting & Finishing (applied on the basis of # of machine hrs)
Supervisor's salaries 185,000
Property tax 28,000
Insurance 17,000
Maintenance 40,000
Utilities 48,000
Engineering time 62,000
Depreciation 110,000
Total 490,000
3. Management recognizes that without a Master Budget the company really will not know the impact of these changes. Management has therefore approached you to prepare a report discussing the importance of budgeting as well as the impact of the proposed changes (as identified in #2 above). They would also like your advice with respect to paying themselves a dividend of $160,000, in 2019. They have also asked for any additional advice and/or ideas that you might have with respect to achieving their goals (increased profitability and cash flow and reduced reliance on Short-term debt, JIT, break-even analysis, etc.).
Lastly, management has also heard rumblings about a performance management program called the "Balanced Scorecard (BSC)" and has asked you to briefly describe it and for your opinion if you feel it might be of value (or not) to them, and if so how (and if not why not). Information about the BSC information can be found: in Chapter 12 (pages 771 - 774) of your textbook; on the internet including a number of academic journals, and; lastly I have posted (on BlackBoard) a list of eight BSC articles (each of which is available on- line through the U of S Library - please note these are not links rather you must go online to the U of S Library and search for the articles).
General Report Instructions:
Your report can be no longer than six typed pages (1.5 spacing, Times Roman 12-point font, one inch margins) (not including title page, executive summary, table of contents, references, and appendices). At the very least your written report must include these sections:
Title page
Table of Contents
Executive Summary
Introduction
Body of the report
Conclusion/Summary
References
Appendices
Note - Need to get the excel part done with all the data cells and no solid numbers. Need to get the full budget assignment on excel with proper reference cells and then a written report.
Attachment:- Introduction to Management Accounting Assignment File.rar