The report aims to depict the changes in the income as per the various scenarios in Maxi’s Food Mart. The main changes have been considered with the variations in the net income as per the growth in sales, Mr. Rendall salary, hourly wage and hiring additional employees. The study has also compared the items for sales in 2017 by creating a pie chart compared to the different items held for sale in 2017.
Maxi’s Food Mart |
|||||
Pro Forma Income Statement |
|||||
|
|
Prepared on: |
February 1, 2018 |
|
|
|
|
2017 |
2018 |
2019 |
2020 |
Sales |
|
|
|
|
|
Deli |
|
$240,000.00 |
$257,400.00 |
$277,348.50 |
$300,229.75 |
Dairy |
|
$720,000.00 |
$772,200.00 |
$832,045.50 |
$900,689.25 |
Canned Goods |
|
$480,000.00 |
$514,800.00 |
$554,697.00 |
$600,459.50 |
Frozen Foods |
|
$800,000.00 |
$858,000.00 |
$924,495.00 |
$1,000,765.84 |
Meats |
|
$840,000.00 |
$900,900.00 |
$970,719.75 |
$1,050,804.13 |
Produce |
|
$500,000.00 |
$536,250.00 |
$577,809.38 |
$625,478.65 |
Dry Goods |
|
$360,000.00 |
$386,100.00 |
$416,022.75 |
$450,344.63 |
Gift Card |
|
$60,000.00 |
$64,350.00 |
$69,337.13 |
$75,057.44 |
Total Sales |
|
$4,000,000.00 |
$4,290,000.00 |
$4,622,475.00 |
$5,003,829.19 |
Cost of Goods Sold (CGS) |
|
|
|
|
|
Deli |
|
$2,000,000.00 |
$2,145,000.00 |
$2,311,237.50 |
$2,501,914.59 |
Dairy |
|
$2,000,000.00 |
$2,145,000.00 |
$2,311,237.50 |
$2,501,914.59 |
Canned Goods |
|
$3,000,000.00 |
$3,217,500.00 |
$3,466,856.25 |
$3,752,871.89 |
Frozen Foods |
|
$2,600,000.00 |
$2,788,500.00 |
$3,004,608.75 |
$3,252,488.97 |
Meats |
|
$2,000,000.00 |
$2,145,000.00 |
$2,311,237.50 |
$2,501,914.59 |
Produce |
|
$2,600,000.00 |
$2,788,500.00 |
$3,004,608.75 |
$3,252,488.97 |
Dry Goods |
|
$2,640,000.00 |
$2,831,400.00 |
$3,050,833.50 |
$3,302,527.26 |
Gift Card |
|
$1,200,000.00 |
$1,287,000.00 |
$1,386,742.50 |
$1,501,148.76 |
Total Cost of Goods Sold (CGS) |
|
$18,040,000.00 |
$19,347,900.00 |
$20,847,362.25 |
$22,567,269.64 |
Gross Profit |
|
$14,040,000.00 |
$15,057,900.00 |
$16,224,887.25 |
$17,563,440.45 |
Operating Expenses |
|
|
|
|
|
Sales & Marketing |
|
$220,000.00 |
$235,950.00 |
$254,236.13 |
$275,210.61 |
General & Administrative |
|
$350,000.00 |
$375,375.00 |
$404,466.56 |
$437,835.05 |
Depreciation |
|
$20,000.00 |
$20,000.00 |
$20,000.00 |
$20,000.00 |
Wages |
|
$503,000.00 |
$503,000.00 |
$503,000.00 |
$503,000.00 |
Common Costs |
|
$2,106,000.00 |
$2,258,685.00 |
$2,433,733.09 |
$2,634,516.07 |
Total Operating Expenses |
|
$3,199,000.00 |
$3,393,010.00 |
$3,615,435.78 |
$3,870,561.73 |
Gross Income (Income Before Taxes) |
|
$10,841,000.00 |
$11,664,890.00 |
$12,609,451.48 |
$13,692,878.72 |
Income Taxes |
|
$3,794,350.00 |
$4,082,711.50 |
$4,413,308.02 |
$4,792,507.55 |
Net Income |
|
$7,046,650.00 |
$7,582,178.50 |
$8,196,143.46 |
$8,900,371.17 |
Input Data: Assumptions and Additional Information |
|||||
Base Period Sales |
$4,000,000.00 |
$4,290,000.00 |
$4,622,475.00 |
$5,003,829.19 |
|
Store Items |
% of Sales |
CGS |
|
Rates |
|
Deli |
6.00% |
50.00% |
|
2018 Growth |
7.25% |
Dairy |
18.00% |
50.00% |
|
2019 Growth |
7.75% |
Canned Goods |
12.00% |
75.00% |
|
2020 Growth |
8.25% |
Frozen Foods |
20.00% |
65.00% |
|
Tax |
35.00% |
Meats |
21.00% |
50.00% |
|
|
|
Produce |
12.50% |
65.00% |
|
|
|
Dry Goods |
9.00% |
66.00% |
|
|
|
Gift Card |
1.50% |
30.00% |
|
|
|
|
100.00% |
|
|
Salaries |
|
Operating Expenses |
|
|
|
Luke |
15.00% |
Sales and Marketing |
5.50% |
|
|
Manager |
$55,000.00 |
General and Administrative |
8.75% |
|
|
Asst. Manager |
$40,000.00 |
Depreciation Expense |
$20,000.00 |
|
|
Total Working Hours in a year |
2000 |
Wage |
$12.00 |
|
|
No. of Employees |
17 |
Maxi’s Food Mart |
||||
Pro Forma Income Statement |
||||
|
Prepared on: |
February 1, 2018 |
|
|
|
2017 |
2018 |
2019 |
2020 |
Sales |
|
|
|
|
Deli |
$240,000.00 |
$261,600.00 |
$286,452.00 |
$315,097.20 |
Dairy |
$720,000.00 |
$784,800.00 |
$859,356.00 |
$945,291.60 |
Canned Goods |
$480,000.00 |
$523,200.00 |
$572,904.00 |
$630,194.40 |
Frozen Foods |
$800,000.00 |
$872,000.00 |
$954,840.00 |
$1,050,324.00 |
Meats |
$840,000.00 |
$915,600.00 |
$1,002,582.00 |
$1,102,840.20 |
Produce |
$500,000.00 |
$545,000.00 |
$596,775.00 |
$656,452.50 |
Dry Goods |
$360,000.00 |
$392,400.00 |
$429,678.00 |
$472,645.80 |
Gift Card |
$60,000.00 |
$65,400.00 |
$71,613.00 |
$78,774.30 |
Total Sales |
$4,000,000.00 |
$4,360,000.00 |
$4,774,200.00 |
$5,251,620.00 |
Cost of Goods Sold (CGS) |
|
|
|
|
Deli |
$2,000,000.00 |
$2,180,000.00 |
$2,387,100.00 |
$2,625,810.00 |
Dairy |
$2,000,000.00 |
$2,180,000.00 |
$2,387,100.00 |
$2,625,810.00 |
Canned Goods |
$3,000,000.00 |
$3,270,000.00 |
$3,580,650.00 |
$3,938,715.00 |
Frozen Foods |
$2,600,000.00 |
$2,834,000.00 |
$3,103,230.00 |
$3,413,553.00 |
Meats |
$2,000,000.00 |
$2,180,000.00 |
$2,387,100.00 |
$2,625,810.00 |
Produce |
$2,600,000.00 |
$2,834,000.00 |
$3,103,230.00 |
$3,413,553.00 |
Dry Goods |
$2,640,000.00 |
$2,877,600.00 |
$3,150,972.00 |
$3,466,069.20 |
Gift Card |
$1,200,000.00 |
$1,308,000.00 |
$1,432,260.00 |
$1,575,486.00 |
Total Cost of Goods Sold (CGS) |
$18,040,000.00 |
$19,663,600.00 |
$21,531,642.00 |
$23,684,806.20 |
Gross Profit |
$14,040,000.00 |
$15,303,600.00 |
$16,757,442.00 |
$18,433,186.20 |
Operating Expenses |
|
|
|
|
Sales & Marketing |
$220,000.00 |
$239,800.00 |
$262,581.00 |
$288,839.10 |
General & Administrative |
$350,000.00 |
$381,500.00 |
$417,742.50 |
$459,516.75 |
Depreciation |
$20,000.00 |
$20,000.00 |
$20,000.00 |
$20,000.00 |
Wages |
$503,000.00 |
$503,000.00 |
$503,000.00 |
$503,000.00 |
Common Costs |
$2,106,000.00 |
$2,295,540.00 |
$2,513,616.30 |
$2,764,977.93 |
Total Operating Expenses |
$3,199,000.00 |
$3,439,840.00 |
$3,716,939.80 |
$4,036,333.78 |
Gross Income (Income Before Taxes) |
$10,841,000.00 |
$11,863,760.00 |
$13,040,502.20 |
$14,396,852.42 |
Income Taxes |
$3,794,350.00 |
$4,152,316.00 |
$4,564,175.77 |
$5,038,898.35 |
Net Income |
$7,046,650.00 |
$7,711,444.00 |
$8,476,326.43 |
$9,357,954.07 |
Input Data: Assumptions and Additional Information |
||||
Base Period Sales |
$4,360,000.00 |
$4,774,200.00 |
$5,251,620.00 |
|
Store Items |
CGS |
|
Rates |
|
Deli |
50.00% |
|
2018 Growth |
9.00% |
Dairy |
50.00% |
|
2019 Growth |
9.50% |
Canned Goods |
75.00% |
|
2020 Growth |
10.00% |
Frozen Foods |
65.00% |
|
Tax |
35.00% |
Meats |
50.00% |
|
|
|
Produce |
65.00% |
|
|
|
Dry Goods |
66.00% |
|
|
|
Gift Card |
30.00% |
|
|
|
|
|
|
Salaries |
|
Operating Expenses |
|
|
Luke |
15.00% |
Sales and Marketing |
|
|
Manager |
$55,000.00 |
General and Administrative |
|
|
Asst. Manager |
$40,000.00 |
Depreciation Expense |
|
|
Total Working Hours in a year |
2000 |
Wage |
|
|
No. of Employees |
17 |
Maxi’s Food Mart |
|||||
Pro Forma Income Statement |
|||||
|
|
Prepared on: |
############## |
|
|
|
|
|
|
|
|
|
|
2017 |
2018 |
2019 |
2020 |
Sales |
|
|
|
|
|
Deli |
|
$240,000.00 |
$249,600.00 |
$262,080.00 |
$276,494.40 |
Dairy |
|
$720,000.00 |
$748,800.00 |
$786,240.00 |
$829,483.20 |
Canned Goods |
|
$480,000.00 |
$499,200.00 |
$524,160.00 |
$552,988.80 |
Frozen Foods |
|
$800,000.00 |
$832,000.00 |
$873,600.00 |
$921,648.00 |
Meats |
|
$840,000.00 |
$873,600.00 |
$917,280.00 |
$967,730.40 |
Produce |
|
$500,000.00 |
$520,000.00 |
$546,000.00 |
$576,030.00 |
Dry Goods |
|
$360,000.00 |
$374,400.00 |
$393,120.00 |
$414,741.60 |
Gift Card |
|
$60,000.00 |
$62,400.00 |
$65,520.00 |
$69,123.60 |
Total Sales |
|
$4,000,000.00 |
$4,160,000.00 |
$4,368,000.00 |
$4,608,240.00 |
Cost of Goods Sold (CGS) |
|
|
|
|
|
Deli |
|
$2,000,000.00 |
$2,080,000.00 |
$2,184,000.00 |
$2,304,120.00 |
Dairy |
|
$2,000,000.00 |
$2,080,000.00 |
$2,184,000.00 |
$2,304,120.00 |
Canned Goods |
|
$3,000,000.00 |
$3,120,000.00 |
$3,276,000.00 |
$3,456,180.00 |
Frozen Foods |
|
$2,600,000.00 |
$2,704,000.00 |
$2,839,200.00 |
$2,995,356.00 |
Meats |
|
$2,000,000.00 |
$2,080,000.00 |
$2,184,000.00 |
$2,304,120.00 |
Produce |
|
$2,600,000.00 |
$2,704,000.00 |
$2,839,200.00 |
$2,995,356.00 |
Dry Goods |
|
$2,640,000.00 |
$2,745,600.00 |
$2,882,880.00 |
$3,041,438.40 |
Gift Card |
|
$1,200,000.00 |
$1,248,000.00 |
$1,310,400.00 |
$1,382,472.00 |
Total Cost of Goods Sold (CGS) |
|
$18,040,000.00 |
$18,761,600.00 |
$19,699,680.00 |
############# |
Gross Profit |
|
$14,040,000.00 |
$14,601,600.00 |
$15,331,680.00 |
############# |
Operating Expenses |
|
|
|
|
|
Sales & Marketing |
|
$220,000.00 |
$228,800.00 |
$240,240.00 |
$253,453.20 |
General & Administrative |
|
$350,000.00 |
$364,000.00 |
$382,200.00 |
$403,221.00 |
Depreciation |
|
$20,000.00 |
$20,000.00 |
$20,000.00 |
$20,000.00 |
Wages |
|
$503,000.00 |
$503,000.00 |
$503,000.00 |
$503,000.00 |
Common Costs |
|
$2,106,000.00 |
$2,190,240.00 |
$2,299,752.00 |
$2,426,238.36 |
Total Operating Expenses |
|
$3,199,000.00 |
$3,306,040.00 |
$3,445,192.00 |
$3,605,912.56 |
Gross Income (Income Before Taxes) |
|
$10,841,000.00 |
$11,295,560.00 |
$11,886,488.00 |
############# |
Income Taxes |
|
$3,794,350.00 |
$3,953,446.00 |
$4,160,270.80 |
$4,399,153.44 |
Net Income |
|
$7,046,650.00 |
$7,342,114.00 |
$7,726,217.20 |
$8,169,856.40 |
Input Data: Assumptions and Additional Information |
|||||
Base Period Sales |
$4,000,000.00 |
$4,160,000.00 |
$4,368,000.00 |
$4,608,240.00 |
|
Store Items |
% of Sales |
CGS |
|
Rates |
|
Deli |
6.00% |
50.00% |
|
2018 Growth |
4.00% |
Dairy |
18.00% |
50.00% |
|
2019 Growth |
5.00% |
Canned Goods |
12.00% |
75.00% |
|
2020 Growth |
5.50% |
Frozen Foods |
20.00% |
65.00% |
|
Tax |
35.00% |
Meats |
21.00% |
50.00% |
|
|
|
Produce |
12.50% |
65.00% |
|
|
|
Dry Goods |
9.00% |
66.00% |
|
|
|
Gift Card |
1.50% |
30.00% |
|
|
|
|
100.00% |
|
|
Salaries |
|
Operating Expenses |
|
|
|
Luke |
15.00% |
Sales and Marketing |
5.50% |
|
|
Manager |
$55,000.00 |
General and Administrative |
8.75% |
|
|
Asst. Manager |
$40,000.00 |
Depreciation Expense |
$20,000.00 |
|
|
Total Working Hours in a year |
2000 |
Wage |
$12.00 |
|
|
No. of Employees |
17 |
Maxi’s Food Mart |
|||||
Pro Forma Income Statement |
|||||
|
|
Prepared on: |
February 1, 2018 |
|
|
|
|
2017 |
2018 |
2019 |
2020 |
Deli |
|
$240,000.00 |
$257,400.00 |
$277,348.50 |
$300,229.75 |
Dairy |
|
$720,000.00 |
$772,200.00 |
$832,045.50 |
$900,689.25 |
Canned Goods |
|
$480,000.00 |
$514,800.00 |
$554,697.00 |
$600,459.50 |
Frozen Foods |
|
$800,000.00 |
$858,000.00 |
$924,495.00 |
$1,000,765.84 |
Meats |
|
$840,000.00 |
$900,900.00 |
$970,719.75 |
$1,050,804.13 |
Produce |
|
$500,000.00 |
$536,250.00 |
$577,809.38 |
$625,478.65 |
Dry Goods |
|
$360,000.00 |
$386,100.00 |
$416,022.75 |
$450,344.63 |
Gift Card |
|
$60,000.00 |
$64,350.00 |
$69,337.13 |
$75,057.44 |
Total Sales |
|
$4,000,000.00 |
$4,290,000.00 |
$4,622,475.00 |
$5,003,829.19 |
Cost of Goods Sold (CGS) |
|
|
|
|
|
Deli |
|
$2,000,000.00 |
$2,145,000.00 |
$2,311,237.50 |
$2,501,914.59 |
Dairy |
|
$2,000,000.00 |
$2,145,000.00 |
$2,311,237.50 |
$2,501,914.59 |
Canned Goods |
|
$3,000,000.00 |
$3,217,500.00 |
$3,466,856.25 |
$3,752,871.89 |
Frozen Foods |
|
$2,600,000.00 |
$2,788,500.00 |
$3,004,608.75 |
$3,252,488.97 |
Meats |
|
$2,000,000.00 |
$2,145,000.00 |
$2,311,237.50 |
$2,501,914.59 |
Produce |
|
$2,600,000.00 |
$2,788,500.00 |
$3,004,608.75 |
$3,252,488.97 |
Dry Goods |
|
$2,640,000.00 |
$2,831,400.00 |
$3,050,833.50 |
$3,302,527.26 |
Gift Card |
|
$1,200,000.00 |
$1,287,000.00 |
$1,386,742.50 |
$1,501,148.76 |
Total Cost of Goods Sold (CGS) |
|
$18,040,000.00 |
$19,347,900.00 |
$20,847,362.25 |
$22,567,269.64 |
Gross Profit |
|
$14,040,000.00 |
$15,057,900.00 |
$16,224,887.25 |
$17,563,440.45 |
Operating Expenses |
|
|
|
|
|
Sales & Marketing |
|
$220,000.00 |
$235,950.00 |
$254,236.13 |
$275,210.61 |
General & Administrative |
|
$350,000.00 |
$375,375.00 |
$404,466.56 |
$437,835.05 |
Depreciation |
|
$20,000.00 |
$20,000.00 |
$20,000.00 |
$20,000.00 |
Wages |
|
$571,000.00 |
$571,000.00 |
$571,000.00 |
$571,000.00 |
Common Costs |
|
$1,123,200.00 |
$1,204,632.00 |
$1,297,990.98 |
$1,405,075.24 |
Total Operating Expenses |
|
$2,284,200.00 |
$2,406,957.00 |
$2,547,693.67 |
$2,709,120.90 |
Gross Income (Income Before Taxes) |
|
$11,755,800.00 |
$12,650,943.00 |
$13,677,193.58 |
$14,854,319.55 |
Income Taxes |
|
$4,114,530.00 |
$4,427,830.05 |
$4,787,017.75 |
$5,199,011.84 |
Net Income |
|
$7,641,270.00 |
$8,223,112.95 |
$8,890,175.83 |
$9,655,307.71 |
Input Data: Assumptions and Additional Information |
|||||
Base Period Sales |
$4,000,000.00 |
$4,290,000.00 |
$4,622,475.00 |
$5,003,829.19 |
|
Store Items |
% of Sales |
CGS |
|
Rates |
|
Deli |
6.00% |
50.00% |
|
2018 Growth |
7.25% |
Dairy |
18.00% |
50.00% |
|
2019 Growth |
7.75% |
Canned Goods |
12.00% |
75.00% |
|
2020 Growth |
8.25% |
Frozen Foods |
20.00% |
65.00% |
|
Tax |
35.00% |
Meats |
21.00% |
50.00% |
|
|
|
Produce |
12.50% |
65.00% |
|
|
|
Dry Goods |
9.00% |
66.00% |
|
|
|
Gift Card |
1.50% |
30.00% |
|
|
|
|
100.00% |
|
|
Salaries |
|
Operating Expenses |
|
|
|
Luke |
8.00% |
Sales and Marketing |
5.50% |
|
|
Manager |
$55,000.00 |
General and Administrative |
8.75% |
|
|
Asst. Manager |
$40,000.00 |
Depreciation Expense |
$20,000.00 |
|
|
Total Working Hours in a year |
2000 |
Wage |
$14.00 |
|
|
No. of Employees |
17 |
Input Data: Assumptions and Additional Information |
|||||
Base Period Sales |
$4,000,000.00 |
$4,290,000.00 |
$4,622,475.00 |
$5,003,829.19 |
|
|
|
|
|
|
|
Store Items |
% of Sales |
CGS |
|
Rates |
|
Deli |
6.00% |
50.00% |
|
2018 Growth |
7.25% |
Dairy |
18.00% |
50.00% |
|
2019 Growth |
7.75% |
Canned Goods |
12.00% |
75.00% |
|
2020 Growth |
8.25% |
Frozen Foods |
20.00% |
65.00% |
|
Tax |
35.00% |
Meats |
21.00% |
50.00% |
|
|
|
Produce |
12.50% |
65.00% |
|
|
|
Dry Goods |
9.00% |
66.00% |
|
|
|
Gift Card |
1.50% |
30.00% |
|
|
|
|
100.00% |
|
|
Salaries |
|
Operating Expenses |
|
|
|
Luke |
15.00% |
Sales and Marketing |
5.50% |
|
|
Manager |
$55,000.00 |
General and Administrative |
8.75% |
|
|
Asst. Manager |
$40,000.00 |
Depreciation Expense |
$20,000.00 |
|
|
Total Working Hours in a year |
2000 |
Wage |
$14.00 |
|
|
No. of Employees |
19 |
In the given case Luke Rendall may use the what if analysis to depict the changes in the salaries as per gross profit in different situation. The what if analysis may be used in case the total cost of goods sold is higher than the revenue generated from. If the total cost of goods sold is greater than total sales and the company incurs a loss then the what of analysis will be automatically trace this and generate the result as not applicable. The what if analysis may be beneficial to trace the different types of the results based on the changes in other variables as well. For instance, in the company does not need to any depreciation for a year then it can trace it from beforehand. The What-if analysis in excel may be used in excel for setting the values in one or more formulas to explore the different set of results. In case of building two analysis Luke Rendall ay use “several different sets of values in one or more formulas to explore all the various results” (Rodgers 2016).
This analysis when used in building two budgets may assume specific level of revenue and determine the values which will be produced in excel. “Excel provides several types of the different tools to help a company perform the analysis that fits appropriately to the business needs” (Poatsy et al.2016).
There are different situations which calls for the needs to implement absolute cell referencing. It has been seen that unlike relative references, absolute references do not get altered when copied or filled. The use of this method may be used to keep a row “and/or column constant”. Absolute reference is designed with the addition to a dollar sign ($). This may be used by preceding the column reference, roe reference or both. The use of $A$ is used when the row and column do not change on copying the data (Winston 2016). The use of A$2 is used when the row does not change when copied. The use of $A2 column does not change when copied. The use of absolute cell referencing is appropriate when a specific set of data used needs to be fixed as per the required solution. The absolute cell reference use helps in minimizing the overall time required for copying the data into other cells, hence it saves time by fixing a cell and making the changes in other relevant cells in excel (Liengme 2015).
The main depictions relating to the changes have suggested that there is a positive relation between sales and net income. This has been understood with the fact that when the sales have increased with higher percentage the net income has also increased accordingly. The hiring of additional employees has also demonstrated an increasing in the operating expenses.
Liengme, B., 2015. A guide to Microsoft Excel 2013 for scientists and engineers. Academic Press.
Poatsy, M.A., Mulbery, K., Davidson, J. and Grauer, R., 2016. Exploring Microsoft Office Excel 2016 Comprehensive. Pearso
Rodgers, K.A., 2016. Correlation Analysis with Excel Handout.
Winston, W., 2016. Microsoft Excel data analysis and business modeling. Microsoft press.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order formOnce we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignmentAs soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download