Question
GROUP COMPUTER PROJECT ACCT 6060/Bazaz/ Winter intersection 2021 (Due : Nov 15th 2021 Professor Review) Shiraz, Inc. is a small producer and distributor of a
GROUP COMPUTER PROJECT
ACCT 6060/Bazaz/ Winter intersection 2021 (Due : Nov 15th 2021 Professor Review)
Shiraz, Inc. is a small producer and distributor of a product called Alpha. Shiraz Company has become interested in the preparation of operating and financial budgets. The budgets are vitally needed for operational planning and cost control purposes. You have been asked to assist Sara, the accountant of the company, in the preparation of budgets for the first three months of year 20X2.
Sales Forecasting:
Sara knows that the first step for the preparation of budgets is to forecast sales. Sara has been able to identify two possible variables that drive/influence the sales of the company. They are: the level of distribution of Alpha (number of dealers) and the amount of money spent on advertising (advertising expenditures). Sara wants to use the Regression method for the development of a regression equation/model for forecasting the sales of the company. She has accumulated 12 months of recent data, 20X1, on sales units, advertising expenditures, and the number of dealers as shown in Table I.
TABLE I
Sales
Advertising
Number of
Units
Expenditures
Dealers
249,000
19080
310
256,000
19440
337
264,000
19800
375
304,000
27000
312
320,000
30600
318
272,000
21600
303
296,000
28800
315
216,000
19800
225
232,000
19440
247
240,000
19620
270
268,000
23400
306
280,000
25200
309
Shiraz expects the following for the first five months of year 20X2:
January
February
March
April
May
Advertising Expenditures
$28,000
$25,000
$27,000
$26,500
$25,800
No. Of Dealers
285
300
318
310
265
Budgeting:
Management of Shiraz Company is interested in a number of budgets. The preferred formats for budgets are displayed in Exhibit I. As shown in Exhibit I, Sara's Excel file has two sections. Section One contains the required data for the preparation of budgets. Section Two contains the budgets. Since management of Shiraz wants to simulate the budgets for various possible conditions, the content of budgets will be stated all in formulas. The only area where data should be entered manually is in the data section, under the January column only! (Hint: this will require the use of excel functions including: "IF", "AND", "SUM", "ROUNDUP", "ROUNDDOWN", "ROUND" etc.). Sara has prepared the following data for the preparation of budgets:
1. Sales
- Selling price per unit of Alpha is expected to be $12 in January through February. Five percent increase in selling price is expected in March. Aril and May are expected to have the same selling price as in March.
- Seventy-five (75) percent of each month's sales are collected in the month of sale. The remaining is collected in the following month.
2. Manufacturing Expenses
Production of each Alpha requires:
- Direct materials: 5 pounds of direct material @ $0.80 per pound (expected to increase by 10 cents each month)
- Direct Labor: hour of direct labor @ $10 per hour
- Variable manufacturing overhead (each unit): $0.50 per unit
- Fixed manufacturing overhead: Total of $25,000 per month of which $3,000 is depreciation expense
3. Operating (Selling & Administrative) Expenses
- Sales Commission: $0.80 per unit
- Shipping and Handling: $0.60 per unit
- Fixed Operating Expenses: Total $12,000 per month of which $1,500 is depreciation expense.
4. Payment of Expenses
- Other than purchase of materials, all expenses are expected to be paid in the month incurred. Sixty (60) percent of purchases are paid in the month of purchase and the remaining is paid in the following month
5. Capital Expenditures
- Shiraz is in the process of expansion of its operations by adding new equipment. The expansion requires $500,000 cash outflow in the month of January and another $300,000 in the month of February.
6. Loan Repayments & Interest Expense
- The Company can borrow from its bank as needed to bolster the Cash account. Borrowings and repayments of principle must be in multiples of $1,000, unless you are paying off the entire balance. All borrowings and repayments take place at the end of a month. The annual interest rate is 12%. Interest is compounded every month and added to the principle. Compute interest on whole month (1/12, 2/12, and so on).
7. Inventory Policy
- It is the company's policy to maintain an inventory of Alpha at the end of each month equal to 20% of next month's anticipated sales.
- Company also maintains an inventory of raw materials equal to 25% of next month's production needs.
8. Other Information
- Sales on December of previous year were $4,350,000.
- Direct material purchases for December of the previous year were $1,800,000.
- The balance of cash on December 31 of the previous year was $21,000.
- The Company desires to maintain a minimum balance of $20,000 cash on hand at all times.
- Applicable income tax rate is 30%.
Requirements:
Write a one to two page single-spaced memo to the management of Shiraz Company and include/attach your findings (use 1" margin along with Arial Font with font size of 10 for your report). Specifically:
- Using the data provided by Sara in Table I calculate all possible regression equations that could be used for forecasting sales of Shiraz Company. The following step will help you to access "regression" in your excel file:
- To use regression, follow the following steps:
- Click of "file" on Excel
- Click on "Option"
- Click on "add-ins
- Click on "Go" under Manage: Excel Add-ins
- Select "analysis toolpack" , OK
- Click on "Data" in the tool bar
- Click on "data Analysis
- Select regression
- Now, you are ready to enter your data and run the regression.
- In your memo, discuss these various equations and state which of the equations you would recommend to be used and why. Provide a complete and detailed justification for your choice. Also, discuss the meaning of the regression coefficients of the equation that you choose and explain whether these coefficients are reliable by referring each to its t-value. Attach your Excel work for this section to your memo. In your regression equation, carry two decimal points for coefficient of X variables and none for the intercept. (Hint: Your selection should be based on highest R2 and t-values or P-values.)
- Using the chosen equation in Item 1, forecast sales of Shiraz Company for January to May and state the results in a Table similar to the following in the memo. You may not be able to fill the cells for all 5 months. However, you should be able to have complete information for the first quarter (January, February, and March).
Month
January
February
March
April
May
Sales Units
- Using the format in Exhibit I, summarize the requisite data for the preparation of budgets in the January column of Section One of your Excel file. Then, write the necessary formulas for all other cells to calculate the budgets. Format nicely the content of budgets (allow two decimal points for "per unit" items and "round" to whole numbers for total items). Attach a print copy of (a) the budgets containing numbers, and (b) the budgets displaying the formulas in the cells (don't worry about displaying the whole formula in order to save paper!)
5. There are rumors that the price of direct materials and direct labor for Alpha to be increased by 15% and 5%, respectively effective January 1, 20X2. Management of Shiraz wants to know the impact of this increase on its financial position. Recalculate the budgets by incorporating the expected increases in the price of direct materials and direct labor. Compute and discuss the percentages of change in the profit and cash balance of the company due to the increase in the price.
Hint: To save time in typing, you could copy the necessary data from this Word file to your Excel file.
Exhibit I
SECTION ONE: DATA FOR BUDGETS
Sales Data:
January January
February
March March
April
May
Sales Units
Selling price per unit
Percentage of sales collected in the month of sales
Percentage of sales collected in the following month
Variable Expenses:
Pounds of material needed per unit of Alpha
Price of material per pound
Direct labor hours needed per unit of Alpha
Direct labor rate per hour
Variable manufacturing overhead per unit of Alpha
Variable operating expenses per unit of Alpha
Fixed Expenses:
Fixed manufacturing overhead
Depreciation portion of fixed overhead
Fixed operating expenses
Depreciation portion of fixed operating expenses
Inventory Policy:
Desired ending inventory of Alpha (% of next month sales)
Desired ending inventory of DM (% of next month's production needs)
Other Information:
Capital Expenditures
Loan Repayments & Interest Expense
Income tax rate
Minimum cash retained at the end of month
December 20X1 Sales dollars
Cash balance as of January 1, 20X2
SECTION TWO: BUDGETS
Sales Budget
January January
February
March March
April
May May
Sales in Units
Unit Selling Price
Sales in Dollars
Production Budget
January January
February
March March
April
May
Sales Units
Add: Desired Ending Inventory
Total Required Units
Less: Beginning Inventory
Required Production Units
Direct Materials Budget (Purchases Budget)
January January
February
March March
April
May
Units to be Produced
Direct Material Qty Required Per Unit of Alpha (pounds)
Total Direct Materials Needed for Production (pounds)
Add: Desired Ending Inventory Direct Materials
Total Direct Materials Needed
Less: Beginning Inventory of Direct Materials
Direct Material Purchases (pounds)
Cost Per Pound
Total Cost of DM Purchases
Cost of Production Budget (Usage Budget)
January January
February
March March
April
May
Units to be Produced
Direct Material Costs
Direct Labor Cost
Variable Manufacturing Cost
Fixed Manufacturing Cost
Total Production Costs
Cost of Production Per Unit
Operating Expense Budget
January January
February
March March
April
May
Variable
Fixed
Total Operating Expenses
Budgeted Income Statement
January January
February
March March
April
May
Sales ($)
Cost of Goods Sold
Gross Profit
Operating Expenses
Income from Operations
Interest Expense
Income Taxes
Net Income
Cash Budget
January January
February
March March
April
May
Beginning Cash Balance
Add: Receipts
Current Month Sales
Prior Period Month Sales
Total Receipts
Total Cash Available
Less: Disbursements
Direct Materials Purchases
Direct Labor
Fixed Manufacturing Overhead
Variable Manufacturing Overhead
Fixed Operating Expenses
Variable Operating Expenses
Income Taxes
Capital Expenditures
Loan Repayment & Interest Expense
Total Disbursements
Excess (deficiency) of available cash over disbursements
Financing/Borrowing
Ending Cash Balance
Notes Payable
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started