“Fall 2012 AC 325 Budgeting
Project

According to the course syllabus, this project will count as 30% of your grade. The project is to be completed as follows:

1) The project must be completed using Excel or
similar electronic spreadsheet software.
Your cell formulas must be turned in with the project itself. That is, you may print out the project but
you must also turn an excel file with your spreadsheets on it. (i.e. do not just type the project in Word). 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. Assume this company is an S Company, so you
can ignore income taxes, use simple interest, and assume that there are 4 weeks
in a month.

4) Timeliness is crucial. The excel files and complete written project
is due no later than midnight on Sunday, November 30th.

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

AC
325 Budgeting
Project

FOOD
SERVICE COMPANY, INC.

Food
Service Company, Inc. sells packaged meals to the airlines for first class
customers. 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 2012 in the
hopes of better controlling sales and costs.

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

I. Prepare a master budget.
II. Prepare a flexible budget.
III. Prepare a performance
report.

Using the information and
instructions provided, complete your assignment.

I. Prepare the master budget. (50 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, 2012.
6) a proforma balance sheet as of December 31, 2012.

The following is the information
you will need:

Sales Forecast:
October – 10,000
meals
November – 16,000 meals
December – 20,000 meals
January – 18,000 meals

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

Purchases and inventory levels:
The
company buys the meals wholesale from a local restaurant and pays an average of
$3.00 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 10% of the next month’s expected sales.

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

Selling and Administrative
expenses:
Two salesmen
work for the company and sell to the major airlines. They have a base salary of
$2,000 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 $.10 per meal and is paid in the month of delivery.

Other costs of the company are
estimated to be:
Telephone & Web site $ 350 mo. *
Rent 1,000 mo.
Office salaries 2,500 mo.
Insurance 9,000
per year
Utilities 200 mo. *
Supplies 100 mo. *
Advertising (paid the lst day of
the qtr.) $150 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 $25,000 which is expected to last 5 years and office furniture costing
$40,000 which is expected to last 10 years.
There is no salvage value.

The
company had to borrow $30,000 in January of 2012; the terms of the loan was 8%
per year, with interest paid monthly and the principal due December 31, 2014. The minimum cash on hand that the company
feels it must keep is $5,000. Should the
cash budget indicate an ending cash balance below $5,000, the company has
access to a line of credit. Any
principal borrowed is due on the last day of the quarter; however the interest
must be paid in the following month.

SEE
September 30, 2012 BALANCE SHEET on Separate excel file

II. Prepare a flexible budget. (15 points)

In anticipation of changing sales
in 2013, the management team has decided to also prepare a flexible budget for
the quarter. Meal sales ranges are from 30,000
to 50,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.

III. Prepare a performance report. (25 points)

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

Meals sold: 55,000

During December, the salesmen
made a special deal with the airlines which dropped the quarterly sales price
from $6.00 per meal to an average of $5.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 $3.00 to $3.25 each.
Plus, because of the Christmas rush, the delivery company also increased
its price to an average cost of $.12 per meal.

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

Telephone $ 1,500
Rent 3,000
Office salaries 8,000 (excluding bonuses)
Utilities 1,000
Supplies 200
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.

Format should be as follows:

Item Actual Flexible
Budget Variance Master Budget Variance

IV. Report to management with
recommendations. (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.Fall 2012 AC 325 Budgeting
ProjectAccording
to the course syllabus, this project will count as 30% of your grade. The project is to be completed as follows:1) The project must be completed using Excel or
similar electronic spreadsheet software.
Your cell formulas must be turned in with the project itself. That is, you may print out the project but
you must also turn an excel file with your spreadsheets on it. (i.e. do not just type the project in Word). 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. Assume this company is an S Company, so you
can ignore income taxes, use simple interest, and assume that there are 4 weeks
in a month.4) Timeliness is crucial. The excel files and complete written project
is due no later than midnight on Sunday, November 30th. 5) Points for each component of the project are
indicated.AC
325 Budgeting
Project FOOD
SERVICE COMPANY, INC.Food
Service Company, Inc. sells packaged meals to the airlines for first class
customers. 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 2012 in the
hopes of better controlling sales and costs.
You are the accounting manager. The charge to you is to:
I. Prepare a master budget. II. Prepare a flexible budget.III. Prepare a performance
report.Using the information and
instructions provided, complete your assignment.I. Prepare the master budget. (50 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, 2012.6) a proforma balance sheet as of December 31, 2012.The following is the information
you will need:Sales Forecast:October – 10,000
mealsNovember – 16,000 mealsDecember – 20,000 mealsJanuary – 18,000 mealsThe
airlines will be charged $6.00 for each meal; they are required to pay 30% of
the cost in the month of delivery, 55% the following month and the remaining
15% in the next month; no bad debts are
expected. (September sales were 9,000 meals).Purchases and inventory levels:The
company buys the meals wholesale from a local restaurant and pays an average of
$3.00 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 10% of the next month’s expected sales. The company must pay 60% of the
cost of the meals in the month they are purchased and 40% in the following
month.Selling and Administrative
expenses:Two salesmen
work for the company and sell to the major airlines. They have a base salary of
$2,000 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 $.10 per meal and is paid in the month of delivery.Other costs of the company are
estimated to be:Telephone & Web site $ 350 mo. *Rent 1,000 mo.Office salaries 2,500 mo.Insurance 9,000
per yearUtilities 200 mo. *Supplies 100 mo. *Advertising (paid the lst day of
the qtr.) $150 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 $25,000 which is expected to last 5 years and office furniture costing
$40,000 which is expected to last 10 years.
There is no salvage value.The
company had to borrow $30,000 in January of 2012; the terms of the loan was 8%
per year, with interest paid monthly and the principal due December 31, 2014. The minimum cash on hand that the company
feels it must keep is $5,000. Should the
cash budget indicate an ending cash balance below $5,000, the company has
access to a line of credit. Any
principal borrowed is due on the last day of the quarter; however the interest
must be paid in the following month.SEE
September 30, 2012 BALANCE SHEET on Separate excel fileII. Prepare a flexible budget. (15 points)In anticipation of changing sales
in 2013, the management team has decided to also prepare a flexible budget for
the quarter. Meal sales ranges are from 30,000
to 50,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. III. Prepare a performance report. (25 points)Actual results for the last
quarter of 2012 are as follows:Meals sold: 55,000During December, the salesmen
made a special deal with the airlines which dropped the quarterly sales price
from $6.00 per meal to an average of $5.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 $3.00 to $3.25 each.
Plus, because of the Christmas rush, the delivery company also increased
its price to an average cost of $.12 per meal.The company decided to pay
Christmas bonuses of $350 to each salesman and a total of $1,500 to the
salaried employees. Other actual costs
for the quarter were as follows:Telephone $ 1,500Rent 3,000Office salaries 8,000 (excluding bonuses)Utilities 1,000Supplies 200Advertising 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.Format should be as follows:Item Actual Flexible
Budget Variance Master Budget VarianceIV. Report to management with
recommendations. (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.”