Case Study 1 MBA 6150 Fall B-1 2020 Master Budgeting and Pro-Forma Financial Statements You have...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Case Study 1 MBA 6150 Fall B-1 2020 Master Budgeting and Pro-Forma Financial Statements You have just been assigned to a new manager who believes you have exceptional budgeting skills. Since you began your job last summer, you have been showing management your latest spreadsheets and how you use your new-found knowledge of Managerial Accounting to make sound business decisions. Your new manager is responsible for the nationwide distribution of the newly developed Self Protection Device (SPD), using an ultrasonic sound emission, DNA material capture via retractable claw, and an amazing 25,000 lumen LED flashing light. Through. multiple franchise agreements, sales have grown very rapidly, and the timing is right for you to join her team and to show your skills. You have just been given responsibility for all planning and budgeting of the entire division. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2020. You accept this responsibility with enthusiasm, and you are anxious to impress your new manager and the president of the parent company (Allteq Solutions), who has a very high regard for you. To commence your new role, you have assembled the following pertinent information: Note: The company desires a minimum ending cash balance each month on $12,000. The SPD's are sold to retailers for $22 each and they are flying off the shelves. Recent forecasted sales in units are provided below: January (actual) February (actual) March (actual) April May 20,000 24,000 28,000 38,000 65,000 June July August September 50,000 40,000 36,000 32,000 The increased sales volume before and during May is due to Mother's Day with SPD being a favorite. Ending inventories are supposed to be equal to 90% of the next month's sales in units. The cost of each SPD is $9.00. Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the month following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The SPD division has determined that only 25% of a month's sales are collected by the end of the month in which the sale occurred. An additional 50% is collected in the month following the sale, and the remaining 25% is collected in the second month following the sale. Bad debts have been negligible, supporting the credit terms as favorable. Below is a display of the SPD division monthly selling and administrative expenses: Variable: Below is a display of the SPD division monthly selling and administrative expenses: Variable: Sales Commissions Fixed: Wages and Salaries Utilities Insurance Depreciation Miscellaneous $1 per SPD $62,000 $41,000 $1,800 $3,500 $14,000 Selling and administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course) and insurance is pre-paid for the duration of the policy. Due a violation of an existing copyright, lawyers for Allteq Solutions have negotiated a one-time infringement penalty to be be paid during May for $275,000 cash. The newly formed SPD division contributes to the corporate dividend at a rate of $125,000 each quarter, payable in the first month of the following quarter. SPD's balance sheet at the end of the first quarter is shown below: Balance Sheet as of March 31, 2020 31-Mar Cash Accounts receivable Inventory (34,200 units) Prepaid insurance Fixed assets, net of depreciation Total Assets Liabilities and Stockholders Equity Accounts payable Dividends payable Capital Stock Retained earnings Assets Total Liabilities and Stockholders Equity $44,000 594,000 307,800 16,200 472,700 $1,434,700 $166,500 125,000 750,000 393,200 $1,434,700 bujar An agreement with East Rivers Bank allows to borrow in increments of $1,000 at the beginning of each month, up to a total loan amount of $350,000. The interest rate on these loans is 7% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only. At quarter end, SPD would pay East Rivers Bank of the all of the accumulated interest on the loan and as much of the balance of the loan as possible (in $1,000 increments) while retaining the minimum $12,000 cash balance. (Hint: use this excel formula to determine the amount needed to borrow) +IF(excess/deficit<12,000, ROUNDUP(- excess/deficit+12,000,-3).0) where 'excess/deficit' = the cell address of where you compute each month's cash deficit or excess. Required: Prepare a master budget for the three- months ending June 30, 2019. Include the following budget schedules and financial statements: 1) Sales Budget by month and total for the quarter 2) Schedule of expected cash collections from sales, by month and total. 3) Merchandise purchases budget in units and in dollars. Show the budget by month and total. 4) Schedule of expected cash disbursements for merchandise purchases, by month and total. 5) Cash Budget. Show the cash budget by month and in total. 6) Prepare a budgeted Income Statement for the three months ending June 30, 2020. Use the contribution approach. 7) Prepare a budgeted Balance Sheet as of June 30, 2020. 8) Your new manager has asked for your explanation about your experience in preparing this budget. She specifically wants to know your thoughts on the value of all this work and whether the benefits from the work are worth the effort. Prepare a memo (with a heading), no longer than 2 pages, describing your experience from this exercise, and your thoughts on the value of having a Master Budget. Case Study 1 MBA 6150 Fall B-1 2020 Master Budgeting and Pro-Forma Financial Statements You have just been assigned to a new manager who believes you have exceptional budgeting skills. Since you began your job last summer, you have been showing management your latest spreadsheets and how you use your new-found knowledge of Managerial Accounting to make sound business decisions. Your new manager is responsible for the nationwide distribution of the newly developed Self Protection Device (SPD), using an ultrasonic sound emission, DNA material capture via retractable claw, and an amazing 25,000 lumen LED flashing light. Through. multiple franchise agreements, sales have grown very rapidly, and the timing is right for you to join her team and to show your skills. You have just been given responsibility for all planning and budgeting of the entire division. Your first assignment is to prepare a master budget for the next three months, starting April 1, 2020. You accept this responsibility with enthusiasm, and you are anxious to impress your new manager and the president of the parent company (Allteq Solutions), who has a very high regard for you. To commence your new role, you have assembled the following pertinent information: Note: The company desires a minimum ending cash balance each month on $12,000. The SPD's are sold to retailers for $22 each and they are flying off the shelves. Recent forecasted sales in units are provided below: January (actual) February (actual) March (actual) April May 20,000 24,000 28,000 38,000 65,000 June July August September 50,000 40,000 36,000 32,000 The increased sales volume before and during May is due to Mother's Day with SPD being a favorite. Ending inventories are supposed to be equal to 90% of the next month's sales in units. The cost of each SPD is $9.00. Purchases are paid for in the following manner: 50% in the month of the purchase and the remaining 50% paid in the month following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The SPD division has determined that only 25% of a month's sales are collected by the end of the month in which the sale occurred. An additional 50% is collected in the month following the sale, and the remaining 25% is collected in the second month following the sale. Bad debts have been negligible, supporting the credit terms as favorable. Below is a display of the SPD division monthly selling and administrative expenses: Variable: Below is a display of the SPD division monthly selling and administrative expenses: Variable: Sales Commissions Fixed: Wages and Salaries Utilities Insurance Depreciation Miscellaneous $1 per SPD $62,000 $41,000 $1,800 $3,500 $14,000 Selling and administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course) and insurance is pre-paid for the duration of the policy. Due a violation of an existing copyright, lawyers for Allteq Solutions have negotiated a one-time infringement penalty to be be paid during May for $275,000 cash. The newly formed SPD division contributes to the corporate dividend at a rate of $125,000 each quarter, payable in the first month of the following quarter. SPD's balance sheet at the end of the first quarter is shown below: Balance Sheet as of March 31, 2020 31-Mar Cash Accounts receivable Inventory (34,200 units) Prepaid insurance Fixed assets, net of depreciation Total Assets Liabilities and Stockholders Equity Accounts payable Dividends payable Capital Stock Retained earnings Assets Total Liabilities and Stockholders Equity $44,000 594,000 307,800 16,200 472,700 $1,434,700 $166,500 125,000 750,000 393,200 $1,434,700 bujar An agreement with East Rivers Bank allows to borrow in increments of $1,000 at the beginning of each month, up to a total loan amount of $350,000. The interest rate on these loans is 7% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only. At quarter end, SPD would pay East Rivers Bank of the all of the accumulated interest on the loan and as much of the balance of the loan as possible (in $1,000 increments) while retaining the minimum $12,000 cash balance. (Hint: use this excel formula to determine the amount needed to borrow) +IF(excess/deficit<12,000, ROUNDUP(- excess/deficit+12,000,-3).0) where 'excess/deficit' = the cell address of where you compute each month's cash deficit or excess. Required: Prepare a master budget for the three- months ending June 30, 2019. Include the following budget schedules and financial statements: 1) Sales Budget by month and total for the quarter 2) Schedule of expected cash collections from sales, by month and total. 3) Merchandise purchases budget in units and in dollars. Show the budget by month and total. 4) Schedule of expected cash disbursements for merchandise purchases, by month and total. 5) Cash Budget. Show the cash budget by month and in total. 6) Prepare a budgeted Income Statement for the three months ending June 30, 2020. Use the contribution approach. 7) Prepare a budgeted Balance Sheet as of June 30, 2020. 8) Your new manager has asked for your explanation about your experience in preparing this budget. She specifically wants to know your thoughts on the value of all this work and whether the benefits from the work are worth the effort. Prepare a memo (with a heading), no longer than 2 pages, describing your experience from this exercise, and your thoughts on the value of having a Master Budget.
Expert Answer:
Related Book For
Principles of Auditing and Other Assurance Services
ISBN: 978-0078025617
19th edition
Authors: Ray Whittington, Kurt Pany
Posted Date:
Students also viewed these marketing questions
-
You have just been assigned as in-charge accountant on HipStar, Inc. a new audit client in the recording industry. HipStar is an emerging growth company that finds new recording artists, records...
-
You have just been hired by EduRom Company, which was organized on January 2 of the current year. The company manufactures and sells a variety of educational DVDs for personal computers. It is your...
-
A spreadsheet containing completed pro forma financial statements for Ottawa Corporation is available for download from McGraw-Hill's Connect or your course instructor (see the Preface for more...
-
Bingl is one the provinces in Turkey with a very high earthquake risk, since the city center is located on the fault and is very close to the East Anatolian Fault Zone, which is also one of the most...
-
Repeat Problem 32 with the change that each obligation accrues interest at the rate of 9% compounded monthly from a date nine months ago when the obligations were incurred.
-
Activity-based budgeting, Balanced Scorecard, and strategy Sippican Corporation (B)12 Refer to Case 5-36, the Sippican Corporation (A) case, which required time-driven ABC analysis. Sippican's senior...
-
An effective way to learn how companies respond to the competing pressures to be globally integrated and locally responsive is to study them in action. Referring back to Exhibit 6.3, search online...
-
Lenni Clothing Co. manufactures clothing in a small manufacturing facility. Manufacturing has 25 employees. Each employee presently provides 40 hours of productive labor per week. Information about a...
-
Gradient of the Rayleigh Quotient For a symmetric matrix A and a nonzero vector , the Rayleigh Quotient is defined by xT Ax xTx In the case where is an eigenvector, then the Rayleigh Quotient will...
-
? Dean and Ellen Price are married and have a manufacturing business. They bought a piece of business equipment (7-year personal property) on 4/1/2017 for $50,000. Use half-year convention to...
-
Use induction to prove that L = M by showing that LCM and M C L on the length of w. Let L be the language of strings w over (0,1) such that every non-empty even-length prefix of w ends with a 1. Let...
-
Figure P2.26 shows an exploded view of an airplane engine, reduction gear, and propeller. The engine and propeller rotate clockwise, viewed from the propeller end. The reduction gear housing is...
-
The slider D is moving to the right with the constant velocity VC= 3.6 ft/s. For the position shown where link AB of 5 in, link BC 5 in, and 8-34". Determine the velocity in ft/s of joint B. C Vc 0 D...
-
3. Solve for the currents through R, R, and R6 using the mesh current method. ww ww R3 R6 300 100 Q 12: = 14 16 = ww R4 400 mA mA mA R2 200 Q R5 100 2 + V1 10 V ww R1 500 Q
-
Q3. A car is traveling at 80 mi/h and has a braking efficiency of 80%. The brakes are applied to miss an object that is 150 ft from the point of brake application, and the coefficient of road...
-
Q: Calculate the net fores on each wire, in both cases if I= 8.0A, = 30cm [0.30m] 2.) Square I l I b.) Pentagon l I t I O S e e e I e I I I I e I I
-
Offer two ethical alternative principles that could help first seeds employees apply the Code rules. Briefly outline each principle. for one of the principles, give an example of an action that. 1....
-
Write a declaration for each of the following: a. A line that extends from point (60, 100) to point (30, 90) b. A rectangle that is 20 pixels wide, 100 pixels high, and has its upper-left corner at...
-
Why is audit work usually organized around balance sheet accounts rather than income statement accounts?
-
As indicated on the control risk assessment working paper on page 594, the auditors identified two weaknesses in internal control over the acquisition cycle of KCN. Describe the implications of each...
-
What are the purposes of the consideration of internal control required by generally accepted auditing standards?
-
Megan Murphy and Joseph McGoldrick began dating in July 2010 and eventually decided to marry. In late 2016, Megan and Joseph decided to purchase a home for \($205,000.\) Because Joseph had the...
-
In June 2014, Penn State Hershey Medical Center and PinnacleHealth System signed a letter of intent for a proposed merger. Hershey is located in Hershey, in Dauphin County in Pennsylvania and...
-
On October 20, 2020, the Department of Justice filed antitrust claims against Google, one of the biggest companies in the world. The DOJ alleged that Google unlawfully maintain[s] monopolies through...
Study smarter with the SolutionInn App