This assignment requires you to prepare a Master Budget (consisting of individual budgets) for Manning, Inc.,

BUDGET ASSIGNMENT

Overview

This assignment requires you to prepare a Master Budget (consisting of individual budgets) for Manning, Inc., a new retailer of a variety of hockey sticks (e.g., wood, fibreglass, two-piece, one-piece, etc.). Management realized that it needs to pay more attention to planning and has hired you as a consultant to help with one aspect of its planning process—budgeting.

Your first task involved collecting data that will be required to develop a budget. Upon reviewing the data, and given the nature of the assignment, you believe that you need to prepare several budgets that form the Master Budget (these are listed later on in this document). In addition, you believe it is important to carry out a sensitivity analysis and conduct additional analyses that will help management.

Data

It is the end of September 2014; your data collection process has revealed the following:

1. Actual sales for August and September 2014 were 5,500 and 5,800 units (hockey sticks) respectively.

1. Sales for October, November and December 2014 are estimated to be 6,200, 6,300 and 6,550 units respectively; whereas sales for January 2015 are estimated to be 4,900 units.

1. The selling price per hockey stick is $22.

1. In August 2014, it cost $15 to buy one hockey stick from the supplier; this amount increased by ½% (0.5%) in September. This ½% cost increase per month is expected to continue until December 2014.

1. Beginning in October 2014, Manning plans on maintaining an ending inventory (in units) equal to 10% of the following month’s estimated sales (in units).

1. Manning’s cash collections (from customers) are as follows:

                  Month of Sale:                                                60%

                  1^st Month Following the Sale:             30%

                  2^nd Month Following the Sale:            10%

1. Manning’s cash payments (to suppliers) are as follows:

                  Month of Purchase:                             65%

                  1^st Month Following Purchase:           35%

1. Manning’s estimated variable selling costs are as follows:

                  Sales Commissions:                             1.0% of sales revenues

                  Other Selling Costs:                            1.5% of sales revenues

1. Its estimated fixed selling costs in September were:

                        Advertising:                                        $  3,000

                        Office Expenses:                                 $  5,500

                        Miscellaneous:                         $  2,200

            These monthly amounts were expected to remain the same until December. Both the variable and fixed selling costs are paid for during the month in which they are incurred.

1. Its general administrative expenses in September were:

                  Compensation:                                    $20,000

                  Insurance:                                            $  2,500

                  Amortization:                                      $  2,000

                  Property Taxes:                                   $  2,500

                  Miscellaneous:                         $  2,700

Compensation expenses are expected to increase by 0.25% each month starting October, whereas other expenses are expected to remain steady. All general and administrative expenses, except property taxes, are paid for during the month in which they are incurred. Property taxes are paid in quarterly instalments at the end of each quarter.

The amortization expense is related to a building costing $50,000 which is used specifically by administration. The amortization should be credited to the Accumulated Amortization account. Given that the construction of the administrative offices has just been completed (at the beginning of October) there had been no amortization expense prior to October.

1. The company declares and pays dividends of $2,500 per month.

1. The company expects its income-tax rate to be 35% during the 4^th quarter of 2014. Income tax payable for the quarter is paid at the beginning of the next quarter.

1. Manning uses the First-In First-Out (FIFO) method to value its inventory.

1. Company policy requires that Manning maintain a minimum cash balance of $5,000 at the end of each month. In the event that Manning’s ending monthly cash balance was to fall below $5,000 management has arranged a floating line of credit with the company’s bank. The terms of the line of credit are as follows:

·         amounts borrowed are in multiples of $5,000 (i.e., $5,000, $10,000, etc.)

·         all borrowing is assumed to be done at the beginning of the month

·         repayments are made in multiples of $1,000 (i.e., $1,000, $2,000, etc.)

·         repayments are assumed to be made at the end of the month (on a FIFO basis)

·         the annual percentage rate (APR) of interest for all monies borrowed is 10% (simple interest)

·         interest on the monthly balance in the line of credit is due at the end of each month

Manning Inc.’s Balance Sheet as of September 30, 2014 is as follows:

Manning Incorporated

Balance Sheet

September 30, 2014

Assets:

      Cash                                        $    6,500

      Accounts Receivable                  63,140

      Inventory                                      9,347

      Building                                     50,000

      Total Assets                            $128,987

Liabilities:

      Accounts Payable                   $  30,813

      Income taxes Payable                   4,800

Owners’ Equity:

      Common Shares                      $  70,000

      Retained Earnings                       23,374

Total Liabilities and

      Shareholders’ Equity              $128,987

ASSIGNMENT REQUIREMENTS

General Information

·         Please submit both an electronic and hard copy of your report. Make sure to title your electronic report

·         Term projects must be submitted in hard-copy and electronically . As in practice, the evaluation of your analyses will be based on both the content (quantitative and qualitative) and written (grammar and style) presentation of your reports.

·         All term projects must be completed using Microsoft Excel and Word. Wherever possible electronic (i.e., Excel) working sections must be linked. Your entire Excel portion of the project should be done using three separate worksheets (titled Base Case for Part I, Best Case for Part II(i) and Worst Case for Part II(ii)) in one Excel file. Your budgets must be completed by using an Excel spreadsheet and must be fully programmed (meaning cells should be linked using appropriate formulae). Be sure to clearly label each schedule or budget.

·         All term project hard copies must be word processed, and done using: 8 x 11.5 inch paper, 12 point Times-New Roman font size, 1 inch margins, and double spacing. The report should include: a title page, a cover letter, the report, and with any references done using  APA style. Do not use end notes or footnotes. The report (introduction, body, and summary) should be

5 pages.

·         The title page must contain the title of the report. The first page of the report should contain a table of contents listing the various part of the report. The next page should contain a cover letter written to the manager of Manning, Inc. (you can make up a name and address). This cover letter should provide an overview of the report describing in general terms both the work you have done and what your main findings and recommendations are.

·         The body of the report should have suitable headings to make the paper readable.

·         The specific requirements of the project are listed below.

Specific Requirements      (Parts I – VIII)

You have been asked to prepare the following:

I)                   A master budget (using the estimates presented earlier) for the fourth quarter of 2014 including the following:

i)                    A Balance Sheet as of the end of the third quarter of 2014

ii)                  Monthly Sales Budgets

iii)                Monthly Schedules of Cash Collections

iv)                Monthly Purchases Budgets

v)                  Monthly Schedules of Cash Disbursements for Purchases

vi)                Monthly Selling Expense Budgets

vii)              Monthly General and Administrative Expense Budgets

viii)            Monthly Cash Budgets

ix)                A Budgeted Income Statement for the Last Quarter of 2014

x)                  A Budgeted Balance Sheet as of the end of the fourth quarter of 2014

For all the monthly budgets (and schedules) that you are required to prepare, please also include a column for the quarter.

II)                A sensitivity analysis for the cash budget under each of the following two scenarios:

i)                    Sales from October to December exceed the initial estimates by 10%, and you accelerate your cash collections budget to collect 70% in the month of the sale and the remaining 30% in the first month following the sale. Moreover, starting December 1, 2014 Manning raises the selling price by $0.75 per hockey stick.

ii)                  Sales from October to December drop from the initial estimates by 10%, and in order to entice customers Manning relaxes its credit terms to collecting 50% in the month of the sale, 30% in the first month following the sale, and 20% in the second month following the sale. Moreover, due to aggressive competition, Manning is forced to reduce the sales price by $1 starting November 1, 2014.

Note:  For both scenarios, assume that the beginning inventory consisted of 620 units. Also assume that estimated sales for January 2015 remain unchanged (i.e., 4,900 units).

III)             Using data from Part I above, compute the fourth quarter’s break-even sales both in terms of units and dollars.

IV)             Using data from Part I above, compute the level of sales (in units and dollars) that Manning must achieve in order to earn a target after-tax profit of $12,000.

V)                Discuss the overall usefulness of a budget for Manning Inc.

VI)             Discuss some of the difficulties Manning Inc. might face related to preparing the budget. As part of this discussion, be sure to discuss some of the “games” that managers play with budgets.

VII)          What recommendations, if any, would you make to this company with respect to managing its cash budget?

VIII)       What additional recommendations, if any, might you make to this company in order to improve its current situation?  Do not necessarily constrain your answer to include only those areas dealing with budgets.