BUS 503 Accounting Information for Decision-Making
Computer Assignment #2 – Budgeting
The objectives of this assignment are to: a) enhance your understanding of the budgeting process; b) develop your skills in performing a sensitivity analysis utilizing computer spreadsheets; and c) improve your ability to interpret the results and communicate them in writing and verbally.
This is an individual assignment and must be your own work. You are to complete problem #9-42 in your textbook, perform a sensitivity analysis using data provided in this handout, and prepare a business memorandum on budgeting.
Included below are the detail requirements for: 1) preparing the master budget for the original data case (as presented in the textbook problem), 2) performing a sensitivity analysis, 3) developing the spreadsheets,
4) writing the business memorandum, and 5) organizing your work.
The information given in the problem is the original data case. You are to prepare the various components of the master budget including the pro-forma income statement, statement of retained earnings, and the balance sheet. Template to be used and solution for the respective homework problem is provided to you in Blackboard.
You will perform a sensitivity analysis by changing assumptions and then observing the impact on your master budget schedules and financial statements. In each of the three independent cases that follow, you are to make the required changes in the data, observe the results, and explain them in your business memorandum.
Case 1: What would happen if credit sales were 60% of total sales, rather than 75% and inventory at end of each month equal to 20% of next month’s projected cost of goods sold, and cash payments during the same month are 20% of total purchases?
Case 2: What would happen if 15%, rather than 10%, of the credit sales are collected during the month of sale AND 50%, rather than 40%, of each month’s purchases are paid during the month of purchase?
Case 3: What would happen if the equipment purchases required a $100,000 investment, rather than $250,000?
Your spreadsheets for this budget assignment will be parameter-driven only to the extent needed to accommodate the three cases above. For example, make sure that you include in you parameter section the credit sales percent, the percent of sales collected in the month of sale, the percent of sales collected during the following month, etc. For all other data NOT affected by the three cases above, you will enter them directly into the various schedules, rather than being included in the parameters section.
Also, where a schedule utilizes data from another schedule, you will need to reference those data fields, rather than entering them directly. For example, the summary cash budget will reference the cash receipts data from the cash receipts schedule, rather than entering the data directly. Your spreadsheet printouts must include grid lines and row/column labels. Include all of your spreadsheets in one file by setting up separate sheets for the original data case in the normal view, original data case in the formula view, and the three cases in the normal view.
Prepare a business memorandum for the company’s President. Your memorandum will be 1 page, single-spaced, font size 12 and must address the following:
- Scope and objective of the budget you prepared. One paragraph.
- Significance of the sensitivity analysis (the three Cases 1, 2, and 3) and your interpretation of the results. Refer to discussion about the Summary Sheet below. One paragraph.
- What is your recommendation to management in order to achieve better financial results for the quarter? You may include an additional sensitivity analysis (Case 4) and change some input data to support your recommendation. You may also add ratio analysis to your case and recommendation to support your answer. One paragraph.
Your completed assignment will be organized as follows:
- Business memorandum
- Spreadsheet in normal view based on the original case information
- Three cases in normal view based on each case assumptions
- A Summary Sheet comparing selected financial information from the Original case and the other three cases. This Summary Sheet will form the basis of your write-up on the significance of your sensitivity analysis and your recommendations.
Please note that you will not hand in paper copies of your spreadsheets or memo. You will only upload onto Blackboard’s Dropbox section the business memo and excel worksheet. Your memo should make references to your schedules that can be included as appendices if you wish (more instructions to be given in class).
This assignment is worth 15% of your course grade.
Your business memorandum and spreadsheets are due in Blackboard on the due date per the syllabus. No paper copies to be brought to class. No late work will be accepted.