[Solved]The project must be completed using Excel. Your cell formulas must be in the cells. The goal is to prepare the budget an

  100% Pass and No Plagiarism Guaranteed

[Solved]The project must be completed using Excel. Your cell formulas must be in the cells. The goal is to prepare the budget and then reference certain totals and line items to the flexible budget and the performance report.

ACC 2071                          Food Services Project

According to the course syllabus, this project will count as 150 points.  The project is to be completed as follows:

1)      The project must be completed using Excel.  Your cell formulas must be in the cells.  The goal is to prepare the budget and then reference certain totals and line items to the flexible budget and the performance report.  

2)      Good form and readability is important.  You are the accounting manager of the Company so your work should be professional and complete as well as accurate.

3)      Remember that several Generally Accepted Accounting Principles (GAAP) may apply to this project.  Ignore income taxes, use simple interest, and assume that there are 4 weeks in a month.

4)      Timeliness is crucial.  The project is due no later than midnight on October 22.   Work that is turned in late will not be accepted.  Do not email your assignment if you miss the due date.  I will not accept it.

5)      Points for each component of the project are indicated in the instructions.

ACC 2071                                              

                                  FOOD SERVICE COMPANY, INC.

Food Service Company, Inc. sells packaged meals to the airlines.  Even though the company has been in business for several years, because of continued problems experienced by the airlines, the company has lost several major customers in recent years and finds it very difficult to plan operations and cash requirements on an annual basis.  As a result, the company has asked you to prepare a budget for the remaining quarter of 2015 in the hopes of better controlling sales and costs.  

You are the accounting manager.  The charge to you is to:

1. Prepare a master budget.
2. Prepare a flexible budget.
3. Prepare a performance report.

Using the information and instructions provided, complete your assignment.

I.  Prepare the master budget.   (100 points)

The master budget consists of:

1)      a sales budget for the quarter.

2)      a purchases budget for the quarter.

3)      a selling and administrative budget for the quarter.

4)      a cash budget for the quarter.

5)      a proforma income statement for the quarter ending Dec. 31, 2015.

6)      a proforma balance sheet as of December 31, 2015.

The following is the information you will need:

Sales Forecast:

October  -  23,000 meals

November - 29,000 meals

December -  31,000 meals

January -   22,000 meals

The airlines will be charged $5.00 for each meal; they are required to pay 20% of the cost in the month of delivery, 60% the following month and the remaining 20% in the next month;  no bad debts are expected. (September sales were 21,000 meals).

Purchases and inventory levels:

The company buys the meals wholesale from a local restaurant and pays an average of $2.50 for each meal.  Because the meals can be frozen and, in order to keep up with unexpected demand, especially during the Thanksgiving and Christmas holidays, the company keeps a minimum inventory of 15% of the next month`s expected sales.  

The company must pay 65% of the cost of the meals in the month they are purchased and 35% in the following month.

Selling and Administrative expenses:

Four salesmen work for the company and sell to the major airlines. They have a base salary of $1,500 each per month and also earn a combined commission of 5% of sales.  The salaries are paid in the month the sales are made and the commission is paid the following month.

In addition, a trucking company delivers the meals from either the restaurant or company freezer to the airline.  The charge is $.12 per meal and is paid in the month of delivery.

Other costs of the company are estimated to be:

Telephone                                           $   450 mo. *

Rent                                                      2,000 mo.

Office salaries                                         4,000 mo.

Insurance                                             12,000 per year

Utilities                                                     600 mo. *

Supplies                                                   200 mo. *

Advertising (paid the lst day of the qtr.)      600 per quarter                        

* indicates they are paid in the month following usage.  All other expenses are paid in the month incurred unless otherwise noted.

The company has office equipment costing $35,000 which is expected to last 5 years and office furniture costing $20,000 which is expected to last 10 years.  There is no salvage value.

The company had to borrow $30,000 in January of 2015; the terms of the loan was 6% per year interest, with interest paid yearly and the principal due December 31, 2017.  The minimum cash on hand that the company feels it must keep is $50,000.  Should the cash budget indicate an ending cash balance below $50,000, a line of credit for the company can be accessed?  Any principal borrowed is due on the last day of the quarter; however the interest must be paid in the following month.

The balance sheet as of September 30, 2015 is as follows:

Current assets:

  Cash                                       $       7,500

  Accounts receivable                          106,000

  Inventory                                            8,625

  Prepaid expenses                                 3,000

  Supplies                                                 200

    Total current assets                          125,325 

Property, plant and equipment:

  Office equipment                             35,000

  Office furniture                                   20,000

  Less accumulated depreciation           (27,500)

    Net property and equipment              27,500  

                                                $       152,825

                             Liabilities and Stockholders` Equity

Current liabilities:

  Accounts payable                     $       19,888

  Commissions payable                          5,250

  Interest payable                                 1,350

    Total current liabilities                     26,488

Long-term liabilities:

  Note payable                                    30,000

Stockholders` equity:

  Common stock                                   50,000

  Retained earnings                              46,337

                                                  $    152,825

1. Prepare a flexible budget.    (20 points)

In anticipation of changing sales in 2016, the management team has decided to also prepare a flexible budget for the quarter.  Meal sales ranges are from 70,000 to 90,000 in increments of 5,000`s. In preparing this budget, do not consider beginning and ending inventory amounts, but assume that purchases are exactly the amount that will be sold. 

1. Prepare a performance report.   (20 points)

Actual results for the last quarter of 2015 are as follows:

Meals sold:   85,000

During December, the salesmen made a special deal with the airlines which dropped the quarterly sales price from $5.00 per meal to an average of $4.75 each.

Unfortunately, the restaurant had a price increase which brought the average cost Food Service Company, Inc. had to pay for the meal from $2.50 to $2.55 each.  Plus, because of the Christmas rush, the delivery cmpany also increased its price to an average cost of $.15 per meal.

The company decided to pay Christmas bonuses of $500 to each salesman and a total of $2,000 to the salaried employees.  Other actual costs for the quarter were as follows:

Telephone             $ 2,150

Rent                        6,000

Office salaries         12,000 (excluding bonuses)

Utilities                    2,100

Supplies                      400

Advertising                  600

The company paid the bank any interest due as of December 31.

Your report should compare actual quarterly activity to both the flexible budget and the master budget.  As part of your report, comment on how effective you felt your efforts were in preparing the quarterly budget and point out any items you feel management should address or look into as a result of the performance report.

IV.      Report to management with recommendations.   Put in a word document, not the Excel file.   (10 points) 

How do you feel about the future of the company?

What changes would you recommend to management and why?  Be as specific as possible.