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 248,000 19080 300 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 290 300 208 285 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 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 Expansion requires $400,000 cash outflow in the month January and another $200,000 in the month 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 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 1 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 a To use regression, follow the following steps b Click of file on Excel c Click on Option d Click on add ins e Click on Go under Manage Excel Add ins f Select analysis toolpack , OK g Click on Data in the tool bar h Click on data Analysis i Select regression j Now, you are ready to enter your data and run the regression Russell 2 In your memo, discuss these various equations and state which of the equations you would recommend to be used and why 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 R 2 and t values or P values ) 3 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 4 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

Question

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 248,000 19080 300 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 290 300 208 285 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 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  Expansion requires $400,000 cash outflow in the month January and another $200,000 in the month 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 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  1 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  a To use regression, follow the following steps  b Click of  file  on Excel c Click on  Option  d Click on  add ins e Click on  Go  under Manage  Excel Add ins f Select  analysis toolpack , OK g Click on  Data  in the tool bar h Click on  data Analysis i Select regression j Now, you are ready to enter your data and run the regression  Russell 2 In your memo, discuss these various equations and state which of the equations you would recommend to be used and why  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 R 2 and t values or P values ) 3 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 4  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

Accepted Answer

The Answer is in the image, click to view ...

Question

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

Step by Step Solution

Step: 1

Get Instant Access with AI-Powered Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Contemporary Human Resource Management Text And Cases

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question